Use a transaction
In principle, the transaction should be shorter and should be avoided in principle. The transaction should be as short as possible because the relatively long transaction has increased the time of the transaction, so that other transactions that must wait for the transaction lock data, extend the time waiting for access data. When using a transaction, in order to make the transaction as short as possible, some corresponding methods should be taken. In order to minimize the time, you must be very careful when using some Transact-SQL statements. For example, when using a loop statement while, you must confirm the length of the loop in advance, so that this cycle must ensure that the loop is as short as possible before completing the corresponding function. Before starting the transaction, you must understand the information that needs user interactive operations. In this way, in the process of transaction, some time-consuming interactive operations can be avoided, and the time of the transaction process can be shortened. In a user-defined transaction, some data manipulation languages should be used as much as possible, such as INSERT, UPDATE, and DELETE statements, because these statements are mainly manipulated in the database. For some data definition languages, it should be as small as possible, because the operations of these data definitions use both longer times and more resources, and these data definition languages often do not involve data, So it should be as small as possible or without these operations as possible in the transaction. In addition, when using the data manipulation language, be careful, be sure to use the conditional judgment statement in these statements, so that these data manipulation languages relate to as little records as possible, thereby shortening the processing time of transactions.
Pay attention to some problems when nested in nested transactions. Although it is possible, nesting in the transaction is possible, and does not affect the performance of SQL Server handling transactions. However, in fact, use nested transactions, in addition to making matters more complicated, there is no significant benefit. Therefore, it is not recommended to use nested transactions.
Type of transaction
Depending on the settings of the system, transactions can be divided into two types. One is a transaction provided by the system and the other is a user-defined transaction. The transaction provided by the system means that a statement is a transaction when performing some statements. At this time, it is clear that the object of a statement may be both a line of data in the table, or the multi-line data in the table, or even all the data in the table. Therefore, only one statement is also possible to process multiplex data. For example, perform the following data manipulation schemism:
Update Authors
Set State = 'CA'
This is a statement, this statement itself constitutes a transaction. This statement is due to the use of conditional restrictions, this statement is to modify all the data in the table. So the object of this transaction is to modify all the data in the table. If there is 1000 lines of data in the Authors table, the modification of this 1000 line data is either successful, or all failed.
Another transaction is a transaction that the user is clearly defined. In practical applications, most of the transaction is handled by user-defined transactions. When developing an application, you can use the Begin Transaction statement to define a clear user-defined transaction. When using user-defined transactions, be sure to pay attention to two points: First, the transaction must have a clear end statement to end. If you do not use a clear end statement, the system may treat all the operations between the transaction to the user to close the connection as a transaction. The clear end of the transaction can use one of the two statements: commit statements and rollback statements. The COMMIT statement is submitting a statement, and all completed statements are explicitly submitted to the database. The ROLLBACK statement is a cancellation statement, which cancels all the operations of the transaction, that is, the transaction fails.
There is also a special user-defined transaction, which is a distributed transaction. The previously mentioned transactions are operations on a server, and their guarantees of data integrity and consistency refer to the integrity and consistency on a server. However, if a complicated environment may have multiple servers, you must define a distributed transaction to ensure the integrity and consistency of transactions in multiple server environments. In this distributed transaction, all operations can involve the operation of multiple servers. When these operations are successful, all of these operations are submitted to the database of the corresponding server, if one of these operations failed, then All operations in this distributed transaction are canceled. Lock and lock role
The lock is the means to prevent other transactions from accessing the specified resource. The lock is the main method of implementing concurrent control. Multiple users can simultaneously manipulate data in the same database without data inconsistency. In general, the lock can prevent dirty reading, not repeatable reading and illusion reading. Dirty reading is to refer to a transaction is accessing data and modify the data, and this modification has not been submitted to the database, at this time, another transaction also accesss this data and then uses this data. Because this data is not submitted data, then another transaction reads this data is dirty data, and the operation made by dirty data may be incorrect. It is not repeatable to read the same data multiple times in one transaction. Another transaction also accesses the same data at this business yet. Then, between the two read data in the first transaction, the data read in the first transaction twice can be different due to the modification of the second transaction. This happens that the data read twice in a transaction is different, so it is called whether it is not repeatable. Illustrative reading is a phenomenon that occurs when the transaction is not independent execution, such as the first transaction modifies the data in a table, which involves all the data lines in the table. At the same time, the second transaction also modifies the data in this table, which is inserted into the table new data. Then, in the future, the user finds the first transaction, there is still a modified data, just like an illusion.
