Well, not really without a transaction. But I was wondering what locking schemes are used when a query is run outside of an explicit (BEGIN / COMMIT) transaction. What impact does changes to the isolation level have?
The simple answer is its the same when compared to the locking examples I previously identified when running under different isolation levels. Without an explicit transaction in place the query will run under an Auto Commit Transaction, which sound a lot like what used to be called “system transactions”.
A quick execution of some modified code from the previous post shows the same locks taken under the SERIALIZABLE isolation level when an explicit transaction is not in place.
set transaction isolation level serializable select * from vwLocks select * from data cross apply vwLocks select * from vwLocks request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IS GRANT OBJECT vwLocks col1 request_mode request_status resource_type resource_name resource_description ---- ------------ -------------- --------------- -------------------- -------------------- a S GRANT DATABASE 0 a IS GRANT PAGE 72057594038321152 1:143 a RangeS-S GRANT KEY 72057594038321152 (6100bc414817) a IS GRANT OBJECT data a IS GRANT OBJECT vwLocks request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IS GRANT OBJECT vwLocks
And the same is seen for the READ COMMITTED isolation level.
request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IS GRANT OBJECT vwLocks col1 request_mode request_status resource_type resource_name resource_description ---- ------------ -------------- --------------- -------------------- -------------------- a S GRANT DATABASE 0 a IS GRANT PAGE 72057594038321152 1:143 a IS GRANT OBJECT data a IS GRANT OBJECT vwLocks request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IS GRANT OBJECT vwLocks
The query must still take locks in order to protect against reading data that is currently being modified. And the absence of a transaction means there is no delineated time span in which future modifications must be prevented. So locks are not held after the query has executed.
That got me thinking; if the guarantees about future changes (Repeatable Reads and Phantom Reads) don’t apply because there is no transaction it seems odd that SERIALIZABLE needs to take stronger locks than READ COMMITTED. After all they are both implementing the same guarantee about current modifications (no Dirty Reads).
But its not just a transaction that has duration, a query can as well. What if the query involved multiple reads from the same table; or spooled from a non clustered index and then performed lookups on a heap or clustered index. In those cases would the guarantees of the isolation level apply for the duration of the query?
I could not find a way to show the locks taken during the various phases of a query as I could with the simple examples in the previous post on locks. But I was able to create a test that demonstrated different isolation levels.
First I created a table of numbers so I could create a long running query. The code below uses the technique from chapter 4 of Inside SQL Server 2005: T-SQL Querying which can be found here. I’ve removed the primary key to ensure the queries take a long time to complete; you should adjust the number of rows (using the @max variable) in the table so there is enough time to run the second query.
IF OBJECT_ID('dbo.Nums') IS NOT NULL
DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL);
DECLARE @max AS INT, @rc AS INT;
SET @max = 10000;
SET @rc = 1;
INSERT INTO Nums VALUES(1);
WHILE @rc * 2 < = @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END
INSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO
Next I modified the vwLocks view created in the previous post to show locks from all connections as follows.
ALTER view
[dbo].[vwLocks]
as
select
cast(request_mode as varchar(10)) as request_mode ,
cast(request_status as varchar(10)) as request_status ,
cast(resource_type as varchar(15)) as resource_type,
case
when resource_type = 'OBJECT' then
cast(object_name(resource_associated_entity_id) as varchar(20))
else
cast(resource_associated_entity_id as varchar(20))
end as resource_name,
cast(resource_description as varchar(15)) as resource_description
from
sys.dm_tran_locks
I then created a query that took 7 seconds to complete; it finds numbers in the table that have 0 or 1 numbers higher than them by counting all of the numbers lower than the first.
set transaction isolation level read committed
select
parent.n
from
Nums as parent
where
1 >=
(
select
count(child.n)
from
Nums as child
where
child.n > parent.n
)
The query plan for this is nasty; on my machine it involved scanning the Parent alias and scanning then spooling the Child alias 10001 times. When run by it returns two numbers: 9999 and 10000. The next time I ran it I quickly switched to another connection and inserted row into the table as follows:
insert into Nums values(9999999)
Every time I did this the Insert completed immediately and the select query returned the new row as well as the previous values.
n ----------- 9999 10000 9999999 (3 row(s) affected)
If the Select query is run after the new row is inserted only two numbers are returned: 10000 and 9999999. Inserting the row while the Select query is running results in a Phantom Read; the new row was not present when the Parent number 9999 was considered otherwise it would not have been included. It is was present and included in the result set later in the execution of the query.
When the test is run under SERIALIZABLE isolation level the Insert statement blocks until the Select has completed. The select returns the numbers 9999 and 10000 as it should, when run again after the Insert has completed it returns 10000 and 9999999.
While I cannot demonstrate the locks during the query it seems clear that the SERIALIZABLE query took and held locks which prevented Phantom Reads occurring, and so prevented the Insert from completing. And that the READ COMMITTED query either did not take or did not hold its locks long enough to prevent the Phantom Reads as it does not guard against them; and so the Insert was able to add a phantom record that was sucked into the query.
Post a Comment