In-depth SQL server transactions

I. Overview... 1

Ii. Adverse Effects of concurrent access... 1

1. dirty read (dirty read)... 1

2. nonrepeatable read... 1

3. phantom read... 1

Iii. concurrent access control mechanism... 2

1. Lock... 2

2. Row version control... 2

Iv. isolation level... 2

5. Transactions... 3

1. transaction mode... 3

1.1. Explicit transaction (Explicit Transactions)... 3

1.2. Autocommit Transactions... 4

1.3. Implicit Transactions (Implicit Transactions)... 4

2. Transaction programming... 5

2.1. Transact-SQL script... 5

2.2. ADO. NET application interface... 5

 

I. Overview

Concurrent access is called when multiple users access the same resource of the database at the same time. If a user modifies the data during concurrent access, it is likely to adversely affect other users accessing the same resource. Possible adverse effects of concurrency include: dirty reads, non-repeated reads, and Phantom reads.

To avoid adverse effects of concurrent access, SQL server designs two control mechanisms for concurrent access: Lock and row version control.

Ii. Adverse Effects of concurrent access

Concurrent access. If there is no concurrent access control mechanism, the following adverse effects may occur:

1. dirty read (dirty read)

If a user updates a record, the second user reads the updated record, but the first user does not modify the record after updating it, rollback the update. In this way, the second user actually reads a modified record that has never existed. If the first user locks the modified record during the modification, other users cannot read the record before the modification is complete, this situation can be avoided.

2. nonrepeatable read)

The first user reads the same record twice in a transaction. After reading a record for the first time, the second user accesses the record and modifies the record, when the first user reads this record for the second time, the data obtained is different from that obtained for the first time. If the first user locks the record to be read between two reads, other users cannot modify the corresponding record to avoid this situation.

3. phantom read)

The first user reads a batch of records that meet the same conditions twice in a transaction. After reading a batch of records for the first time, the second user accesses the table, some records are inserted or deleted in this table. When the first user reads this batch of records with the same conditions for the second time, some records may be obtained for the first read, the second read result does not exist, or some records in the second read result do not exist in the first read result. If the first user locks the record to be read between two reads, other users cannot modify the corresponding record or add or delete the record, this situation can be avoided.

Iii. concurrent access control mechanism

SQL server provides two concurrent control mechanisms to avoid adverse effects on concurrent access. The two mechanisms are:

1. Lock

Each transaction requests different types of locks for the dependent resources (such as rows, pages, or tables. The lock can prevent other transactions from modifying resources in a way that may cause transaction request lock errors. When the transaction no longer depends on the locked resource, it releases the lock.

There are many types of locks, including:

Table type: Lock the entire table

Row Type: Lock a row

File Type: Lock a database file

Database Type: Lock the entire database

Page type: Lock the database page in 8 K

 

The smaller the lock granularity, the smaller the lock range, and the smaller the blocking effect on other accesses, but the more locks used, the larger the lock consumption. The larger the lock granularity, the larger the possibility of blocking other accesses, but the number of locks used will be relatively small, and the lock consumption will be relatively small.

For programmers, you do not need to manually set the control lock. SQL server automatically manages the lock settings and control by setting the isolation level of transactions.

The lock manager is used by SQL server to manage locks. The lock manager analyzes the SQL statements to be executed by the query analyzer to determine which resources and operations the statements will access, then, the locks required for management are automatically allocated at the specified isolation level.

2. Row Version Control

When the row version control-based isolation level is enabled, the database engine maintains the version of each row modified. The application can specify that the transaction uses the row version to view the data that exists at the beginning of the transaction or query, rather than using the lock to protect all reads. By using row version control, the possibility of reading operations to block other transactions is greatly reduced.

Iv. isolation level

As mentioned above, SQL server controls the use of locks by setting the isolation level to implement concurrent access control.

The Microsoft SQL Server database engine supports all these isolation levels:

L uncommitted read (the lowest level of the isolation transaction, only ensure that the physical damage data is not read)

L committed read (default database engine level)

L Repeatable read

L serializable (the highest level of isolation transactions, full isolation between transactions)

 

These isolation levels correspond to the adverse effects of the preceding three concurrent accesses, which have different effects, as shown in the following table:

Isolation level	Dirty read	Non-repeated read	Phantom read
Uncommitted read	Yes	Yes	Yes
Committed read	No	Yes	Yes
Repeatable read	No	No	Yes
Snapshots	No	No	No
Serializable	No	No	No

 

5. Transactions

A transaction is a single logical unit of work, which can include many operations, but they are logically a whole, either complete or fail, it seems that no operation is performed.

A transaction is a very reliable and robust mechanism that ensures that the transaction is completed or rolled back.

L lock: Use the lock mechanism to ensure the isolation of concurrent transactions as much as possible to avoid the adverse impact of concurrency.

L transaction log: The transaction log records all the operation steps of the entire transaction. If necessary, start the transaction or roll back the transaction with the log. No matter what happens, even if the network is interrupted, the machine is powered off, or even the Database Engine itself has a problem, transaction logs can ensure the integrity of the transaction.

L Transaction Management: ensures the atomicity and Data Consistency of a transaction. After a transaction starts, it is either completed successfully or failed, and rolled back to the state before the transaction starts. All modifications made to the transaction will be restored.

1. transaction mode

There are several transaction modes to control the start and end time of the transaction and the scope of the transaction:

1. 1. Explicit transaction (Explicit Transactions)

An explicit TRANSACTION starts a TRANSACTION using the BEGIN in TRANSACTION of the SQL script or the start TRANSACTION statement of the programming interface (API). The TRANSACTION is committed or rolled back using the COMMIT or ROLLBACK statement of the SQL script, the commit transaction or rollback transaction statement of the programming interface (API) ends the transaction. Both use explicit commands to control the start and end of a transaction.

From transaction start to transaction commit or rollback is a complete transaction cycle. Once a transaction starts, the result is either commit or rollback.

If an error occurs within the transaction range, there are several types of errors. Different types of errors have different behaviors.

L serious errors

For example, if the network from the client to the server is interrupted or the customer's machine is shut down, the data engine will be notified that the data connection has been interrupted, the Data Engine will automatically roll back the entire transaction on the server.

L runtime error

The "GO" command between statements is formed in the Command batch. Data Engine compilation and execution statements are in batches. Compile a batch of commands at a time, and then execute this batch of commands after compilation. A stored procedure is compiled once. Therefore, a stored procedure is a batch regardless of batches.

In most cases, if an error occurs when a statement in a batch occurs, the statement will be aborted and all subsequent statements in the same batch will not be executed, however, the commands executed in the same batch are still valid. However, try… can be used... Catch errors and perform corresponding processing, such as executing the transaction rollback command.

There are some running errors, such as inserting a record with a duplicate primary key to stop the statement with the current error. The subsequent statements will continue to be executed. Such errors can also be accessed by try... Catch.

To ensure that the entire transaction is rolled back when any statement error occurs, the simplest way is to SET XACT_ABORT to ON before the transaction starts. This setting indicates the Data Engine, when an error occurs in a transaction, the subsequent transaction is no longer executed and the entire transaction is rolled back.

L compilation Error

In case of a compilation error, the batch of the error statement is not executed and is not affected by the SET XACT_ABORT setting.

1. 2. Autocommit Transactions)

This mode is the default mode of the Data Engine and the transaction default mode of various programming interfaces. Each separate statement is submitted after completion and rolled back after failure. programmers do not need to specify any commands.

Each separate statement is the unit of a transaction. If the statement is successfully submitted, the statement will be rolled back if the execution error occurs, without affecting the execution of other statements. Note that the execution error here is a runtime error. If the statement has a compilation error, for example, the keyword spelling of the SQL statement is incorrect, the statement in the batch where the compilation error statement is located will not be executed. For example:

USE AdventureWorks; GO Create table TestBatch (Cola int primary key, Colb CHAR (3 )); GO Insert into TestBatch VALUES (1, 'aaa '); Insert into TestBatch VALUES (2, 'bbb '); Insert into TestBatch VALUSE (3, 'ccc '); -- Syntax error. GO SELECT * FROM TestBatch; -- Returns no rows. GO

The spelling of the values keyword in the third insert statement in the preceding Section will lead to compilation errors. The result is that all three insert statements in the same batch as this statement will not be executed.

If the third insert statement is as follows:

Insert into TestBatch VALUES (1, 'ccc '); -- Duplicate key error.

This will generate a running error "Duplicate primary key". This statement will be rolled back, but will not affect the first two insert statements. From this point, we can see that the automatic submission Mode means that each separate statement is either completed or rolled back, without affecting the execution of other statements.

 

1. 3. Implicit Transactions (Implicit Transactions)

The first statement after the SET IMPLICIT_TRANSACTIONS ON command starts a new transaction until the transaction ends when a COMMIT or ROLLBACK statement is run, and the next statement is a new transaction, similarly, the transaction ends when a COMMIT or ROLLBACK statement is encountered. This forms a transaction chain until SET IMPLICIT_TRANSACTIONS OFF ends the implicit transaction and returns to the default automatic commit transaction mode.

The action in the transaction is consistent with the explicit transaction mode.

 

The TRANSACTION is reflected in the connection level. A connection has the TRANSACTION mode, and the automatic commit mode is the default TRANSACTION mode of the connection until the in TRANSACTION statement starts the explicit TRANSACTION mode, or the implicit transaction is SET by SET IMPLICIT_TRANSACTIONS ON, and the connected transaction mode is SET to explicit or implicit transaction mode. when the transaction is committed or rolled back, the implicit transaction is SET to closed, the transaction mode of this connection is set to the automatic commit mode again.

2. Transaction Programming

There are two ways to program a database: one is an application interface (API), which includes programming interfaces such as ODBC, ADO, and ado.net, and the other is a Transact-SQL script, typical is stored procedures.

2.1.Transact-SQL script

BEGIN TRANSACTION

Mark the starting point of the explicit connection transaction.

COMMIT TRANSACTIONOrCOMMIT WORK

If no error is encountered, you can use this statement to successfully end the transaction. All data modifications in this transaction will be permanently valid in the database. The resources occupied by the transaction will be released.

ROLLBACK TRANSACTIONOrROLLBACK WORK

Roll back and forth the transaction that encountered an error. All data modified by the transaction is returned to the State at the beginning of the transaction. The resources occupied by the transaction will be released.

2.2.ADO.NET application interface

You can use the BeginTransaction method on the SqlConnection object to start an explicit transaction. To end the transaction, you can call the Commit () or Rollback () method on the SqlTransaction object.

 

The following describes how to program transactions in the stored procedure.

 

The purpose of using a transaction is to maintain the integrity of the execution of a SQL statement, or all the execution is successful, as long as one statement fails, it can be completely rolled back, back to the status before the start of the transaction.

The TRANSACTION has a starting point, that is, starting a TRANSACTION through begin transaction. Then, each statement in the TRANSACTION is executed. Finally, you must determine that all statements have been successfully executed, and then COMMIT the TRANSACTION using commit transaction, fix the results of the statements executed in the TRANSACTION. If there are any errors in the TRANSACTION, capture the errors and execute rollback transaction to roll back the entire TRANSACTION.

The following is an example code:

USE AdventureWorks; Begin transaction; BEGIN TRY -- A violation error is generated. Delete from Production. Product WHERE ProductID = 980; END TRY BEGIN CATCH SELECT ERROR_NUMBER () AS ErrorNumber, ERROR_SEVERITY () AS ErrorSeverity, ERROR_STATE () as ErrorState, ERROR_PROCEDURE () as ErrorProcedure, ERROR_LINE () as ErrorLine, ERROR_MESSAGE () as ErrorMessage; IF @ TRANCOUNT> 0 Rollback transaction; End catch; IF @ TRANCOUNT> 0 Commit transaction;

Place the statements to be executed in the transaction in the TRY statement block to ensure that all statements generate errors can be captured. If an error occurs in the statement in the transaction, the subsequent statement in the transaction is no longer executed, and the CATCH statement block is directly executed for subsequent processing after the error occurs.

The main task in the CATCH statement block is to execute the transaction rollback statement to roll back the entire transaction. You can also perform other auxiliary tasks, such as displaying errors and recording errors.

If no errors occur in all statements in the TRANSACTION, the program will skip the CATCH Block and execute the final commit transaction to submit the TRANSACTION.

 

It is often seen that some people use @ error to capture errors and determine whether to roll back the transaction. The code is roughly as follows:

Begin transaction; Select xxx from yyyy; -- SQL statement in the transaction ......   If @ error> 0 Rollback transaction; Else Commit transaction;

Here, @ error is used to determine whether all the statements in the transaction are incorrect and whether to roll back the transaction or commit the transaction. In fact, this is very wrong.

First, @ error refers to the execution result of each SQL statement, reflecting the error status of the currently executed statement. When executed to the next sentence, @ error is reset again to reflect the execution result of the next statement. Therefore, it is impossible to use @ error to determine whether all statements have errors.

Second, there are two types of SQL statement running errors: one is a statement error, and the other is aborted, but subsequent statements can still be executed. The other is that after a statement error occurs, all subsequent statements in a command batch are no longer executed. When the statement in the transaction encounters this error, the final If @ error> 0 judgment statement will not have the opportunity to be executed.

This may cause serious consequences: if a statement in the transaction produces the first type of error, subsequent statements will not be executed, the original rollback transaction or commit transaction has no chance to be executed. That is to say, the resources locked by this TRANSACTION will not be released, and the consequence is, if this transaction sets a shared lock for some records, these records can no longer be modified. Even worse, if this transaction sets an exclusive lock for some records, then the statements that read these records will remain congested and cannot be executed. The program will die there.

Therefore, you still need to use try... Catch Block.