A new understanding of the read committed isolation level: the select statement can read data that has been modified by other transactions in the future ???

There are many locks that can be learned, such as the lock granularity, lock mode, lock compatibility, and isolation level.

However, how does one obtain the corresponding lock when running a statement? For example, if a select statement requires an S lock, it may be a pair of records, it may also be a page, or a table. You may find a record through an index. After a record is found, can you obtain all the locks at a time or some part of the records?

For example, in the following experiment, a record occupies 1 data page and a total of 32768 data records are queried. Is S lock applied to 32768 pages or some pages first, query a part of the data, lock the remaining pages, and then query a part of the data.

For example, there are two sessions. Session 1 runs the select statement to query data. Assume that it takes about 18 seconds to query data, and Session 2 is an update statement to update the same data, it takes about 15 seconds. If Session 1 is run and Session 2 is run after one second, which statement will be run first?

The general idea is that Session 1 should be returned first, because the select statement of Session 1 will add a share lock to the data page. Since the share lock is added, Session 2 will run later, to apply an exclusive X lock to a page, you must wait, but this is not the case. If there are still 10 select statements in the session, which of the following statements will be run first? According to the actual execution, the update statement is executed first.

Back to the truth, experiment code

1. Create a table, insert data, and create an index. In this Code, batch processing is performed 18 times, that is, go 18. You can modify the batch data according to the actual situation, as long as the data volume is sufficient, it takes about 20 seconds to run the select statement.

if OBJECT_ID('test') is not null   drop table testgocreate table test(id int,v varchar(8000))insert into testselect 1,replicate('a',8000) union allselect 2,replicate('o',8000) union allselect 3,replicate('c',8000) union allselect 4,replicate('d',8000) union allselect 5,replicate('e',8000) union allselect 6,replicate('f',8000) union allselect 7,replicate('g',8000) union allselect 8,replicate('h',8000) union allselect 9,replicate('i',8000) union allselect 10,replicate('j',8000) union allselect 11,replicate('k',8000) union allselect 12,replicate('l',8000)goinsert into testselect *from testgo 18create index idx_test_id on test(id)

2. Create a temp_lock table to store sys. dm_tran_locks information. This table is mainly used to monitor the detailed lock information during statement execution.

if OBJECT_ID('temp_lock') is not null   drop table temp_lockgoselect 0 id,* into temp_lockfrom sys.dm_tran_lockswhere 1 = 2

Next, you need to create three new queries, execute the following three queries in sequence, that is, first execute Session 1 code, then execute Session 2 code, and then execute Session 3 code, there is a normal interval in the middle (you do not need to execute it at the same time ).

3. Session 1 is the monitoring code. Note that this code is an endless loop. Therefore, you need to cancel the query after executing the code in session 2 and Session 3. Otherwise, the query will continue to run.

declare @i intset @i = 1while 1=1begin insert into temp_lock select @i,* from sys.dm_tran_locks  set @i = @i +1 end 

4. query 2 is a select statement.

Select GETDATE () begin transelect id, % lockres %, -- File id: Page id: Record id vfrom testwhere id = 2 commit transelect GETDATE ()

5. query 3 is an update statement.

select GETDATE()update testset v =replicate('x',8000)where id = 2 select GETDATE()

6. Running result

The run time of the select statement is 14:45:10. 510-14:45:27. 107

The run time of the update statement is 14:45:08. 810-14:45:24. 247

Because too much information is monitored, only a small amount of data is selected here. Note that on the right side of the graph, the request_session_id field is the request session id, and 55 is the session where the update statement is located, 57 is the session where the select statement is located. Obviously, there is an X lock on the record in this line, and the session of the select statement is blocked by the update session, so request_status is shown as wait, that is, waiting to get the S lock

7. What can be explained in the above figure?

First, although the update statement is run 2 seconds later, it is finished earlier.

Second, the results returned by the select statement include both the original data that has not been modified and the updated data of the update statement.

Third, the update statement blocks the select statement.

Previously, I only knew that at the default read committed isolation level, in a transaction, the S lock will be released after the select statement stops running, but through the above experiment, it should be that as long as the select statement sends a part of the results to the client during execution, that is, when we see the results in the result set window of ssms, the S lock will be released, instead of releasing the select statement after the entire operation, otherwise the update statement will be blocked by the select statement and the setting will be deadlocked (that is, the select statement reads part of the data, output, but still hold the S lock, while the update statement also updates some data, and holds the X lock for the data, and the select statement needs to wait for the data that has been locked by the X lock, the update statement must wait for the data that has been locked by the select statement ).

In this case, the select statement must first obtain the S lock of a part of data at runtime, then output the data, and then obtain the S lock of the next part of data, and then output the data, in this way, the update statement may be blocked, because the update statement has added the X lock to the same batch of data, which leads to the third blocking.

Since the select statement is blocked, it will be slower than the update statement, and the X lock of the update statement will be kept until the transaction is committed or rolled back. Likewise, why does the select statement read some of the data after update modification xxxxxxxx..., not all of which are ooooooooo... data?

Since the select statement is blocked by the update statement, it can be read only after the update statement is submitted. Therefore, it reads the data after the update statement is submitted, that is, xxxxxxxx...

8. Has it been said so much? Is this a SQL Server bug?

I don't think so, because at the read committed isolation level, only the select statement can read committed data, however, a select statement in a transaction cannot be guaranteed (note that it is not the two select statements before and after a transaction). It can only read data at a certain time point, that is, it cannot be guaranteed that the select statement, the read result is 14:45:10 at the start of running. 510 time point data, because after this time point, the data is modified and submitted, so he will also read the modified data, so this is not a bug.