Repeatable read and serialization isolation sectors in SQL Server transactions

Table t_lock:

ID is the primary key, and the table has 5 rows of data, 1 ~ 2, 4 ~ 6

 

Repeatable read:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Begin tran
Select * from t_lock where id between 1 and 6

After the query is executed, the shared lock (s) is added to the rows with the table ID 1, 2, 4, 5, and 6)

Execute insert statement

Insert into t_lock values (3, '3 ')

The execution is successful because the row with the ID 3 is inserted with the exclusive lock (x). The insert statement will not apply for the exclusive lock in rows 1, 2, 4, 5, or 6; otherwise, the operation will be blocked.

 

 

Serialization:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Begin tran
Select * from t_lock where id between 1 and 6

 

After the query is executed according to the definition at the serialization level, the ID is 1 ~ Range 6: RangeS-S: Shared locks. Although the row with ID 3 does not exist in the database ~ 6. The range lock is applied, so the RangeS-S lock is applied to the row with ID 3. Remember that the range lock is a continuous range, it is not locked because the data in the database does not exist. For example, you can execute the SQL statement with the ID of 3:

Insert into t_lock values (3, '3 ')

 

The result is blocked because the ID is 1 ~ Rows in the range of 6 (no matter whether they exist) are added with a shared lock. in this range, nothing can be done except query, the above insert statement must apply for an exclusive lock on the Data row with ID 3, which will certainly be blocked.

 

 

Small serialization traps:

In principle, the following SQL statement is executed:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Begin tran
Select * from t_lock where id between 1 and 6

 

Then, the inserted data rows with IDs greater than 6 and less than 1 will not be blocked, because the inserted data ID is located in 1 ~ 6 is out of scope, but you can try to execute the following insert statement:

Insert into t_lock values (9, '9 ')
Insert into t_lock values (-1, '-1 ')

No matter whether the row with ID-1 is inserted or the row with ID 9 is blocked, why? Is the ID of the inserted data out of the scope clearly?

 

Let's change the data in the database. Let's take a look at the two situations.

First, if serialized query is used in a transaction, the data in the database is as follows:

Executing insert into t_lock values (9, '9') will not block
However, executing insert into t_lock values (-1, '-1') will be blocked.

 

If serialized query is used in a transaction, the data in the database is as follows:

Executing insert into t_lock values (-1, '-1') will not block
However, executing insert into t_lock values (9, '9') will be blocked.

 

If serialized query is used in a transaction, the data in the database is as follows:

Executing insert into t_lock values (-1, '-1') or insert into t_lock values (9, '9') will not be blocked.

 

Are you looking at it?
When SqlServer applied a range lock to a row, it first checks whether there are other data rows outside the lock range in the database. If not, it also adds a range lock to other rows outside the lock range, for example, in this article, the range lock will set the ID to 1 ~ Apply a range lock to the data row in Step 6. It first checks whether there are data rows whose ID is less than 1 in the database. If not, it sets the ID to be less than 1 and the ID to 1 ~ Rows of 6 are all added with a range lock. Similarly, it will also check whether there are data rows with IDs greater than 6 in the database. If not, it will set IDs greater than 6 and IDs to 1 ~ All rows in Step 6 are added with a range lock.

• So in the last figure, because SqlServer finds that there are both data rows with IDs less than 1 (ID = 0) and data rows with IDs greater than 6 (ID = 7) in the database ), so only when the ID is 1 ~ Apply a range lock to the range of 6, so executing insert into t_lock values (-1, '-1') or insert into t_lock values (9, '9') will not be blocked.
• The second-to-last figure shows that SqlServer only finds rows with less than 1 in the database (ID = 0). Therefore, a Range lock is applied to all rows with IDs greater than 1, execution of insert into t_lock values (9, '9') is blocked
• The last and third figure shows that SQL Server only finds rows with more than 6 rows in the database (ID = 7). Therefore, a Range lock is applied to all rows with IDs less than 6, the execution of insert into t_lock values (-1, '-1') is blocked.