Database Transactions and Locks (iii)

Data | database


Considerations for using Transactions





when using a transaction, you should, in principle, make the transaction as short as possible and avoid transaction nesting. Transactions should be as short as possible, because longer transactions increase the amount of time the transaction consumes data, making other transactions that must wait for access to the transaction lock data, extending the time to wait for access to the data. In order to make transactions as short as possible, you should take some appropriate methods when using transactions. To minimize time, be very careful when using some Transact-SQL statements. For example, when using a loop statement while, be sure to confirm the length of the cycle and the time it takes to make sure that the loop is as short as possible before completing the appropriate functionality. Before starting a transaction, be sure to understand the information that requires user interaction. This allows you to avoid time-consuming interactive operations and shorten the transaction process while the transaction is in progress. In a user-defined transaction, you should use as many data manipulation languages as you can, such as INSERT, UPDATE, and DELETE statements, because these statements are primarily manipulating data in a database. And for some data definition languages, you should use as little or no as possible, because these data definition language operations take longer and more resources, and the operations of these data definition languages do not usually involve data, so you should use as little as possible in the transaction or do not need these operations. In addition, when using data manipulation language, be aware that you must use conditional judgment statements in these statements so that these data manipulation languages involve as few records as possible, thus shortening the processing time of transactions.





There are some issues to be aware of when nesting transactions. Although it is possible to nest transactions in the middle of a transaction, it does not affect the performance of SQL Server processing transactions. In practice, however, the use of nested transactions does not have the obvious benefit of making the transaction more complex. Therefore, it is not recommended that you use nested transactions.




Type of
transaction





The transaction can be divided into two types according to the system settings. One is a system-provided transaction, and the other is a user-defined transaction. A system-provided transaction is a statement that is a transaction when executing certain statements. To be clear, the object of a statement can be either a row of data in a table, or multiple rows of data in a table, or even all of the data in a table. Therefore, a transaction consisting of only one statement may also contain the processing of multiple rows of data. For example, execute the following data manipulation statement:





UPDATE authors





SET state = ' CA '





This is a statement, and the statement itself constitutes a transaction. This statement is a statement that modifies all the data in a table because it does not use a conditional restriction. So the object of this transaction is to modify all the data in the table. If there are 1000 rows of data in the authors table, the changes to the 1000-line data are either all successful or fail all.





another transaction, which is explicitly defined by the user. In practical applications, most transactions are handled by user-defined transactions. When developing an application, you can use the BEGIN TRANSACTION statement to define explicit user-defined transactions. 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 end with an explicit end statement, the system may treat all operations from the start of the transaction to the user's shutdown connection as a transaction. The explicit end of a transaction can be used in one of two statements: a COMMIT statement and a rollback statement. A COMMIT statement is a COMMIT statement that explicitly submits all the completed statements to the database. The rollback statement is a cancellation statement that cancels the operation of the transaction, which means that the transaction operation failed.





also has a special user-defined transaction, which is a distributed transaction. The transactions mentioned above are operations on a single server, and their guaranteed data integrity and consistency refers to the integrity and consistency of a server. However, if you have multiple servers in a more complex environment, you must define a distributed transaction to ensure the integrity and consistency of transactions in a multiple server environment. In this distributed transaction, all operations can involve operations on multiple servers, and when these operations are successful, all of these operations are committed to the database of the corresponding server, and if one of these operations fails, all operations in the distributed transaction are canceled.




The function of
lock and lock





A lock is a means of preventing other transactions from accessing the specified resource. Lock is the main method to realize concurrent control, and it is the important guarantee that multiple users can manipulate the data in the same database without data inconsistency. In general, locks can prevent dirty reads, non repeatable reads, and hallucinations. Dirty reading means that when a transaction is accessing the data and the data has been modified, and the modification has not yet been submitted to the database, then another transaction accesses the data and then uses the data. Because this data is not yet submitted data, then another transaction read the data is dirty data, the operation based on dirty data may be incorrect. Non-repeatable reading refers to reading the same data multiple times within a transaction. When this transaction is not finished, another transaction accesses the same data. Then, between the two read data in the first transaction and the modification of the second transaction, the data read by the first transaction two times may be different. This makes it happen that the data read two times within a transaction is not the same, so it is called a non repeatable read. Hallucination reading is a phenomenon that occurs when a transaction is not executed independently, for example, the first transaction modifies the data in a table that involves all the rows of data in the table. At the same time, the second transaction modifies the data in this table, which is inserting a row of new data into the table. So, it's going to happen later. The user of the first transaction discovers that there are no modified data rows in the table, as if there were hallucinations.