HOLDLOCK is not equivalent to REPEATABLE READ and HOLDLOCK does get range locks. And the semantics of HOLDLOCK and SERIALIZABLE are the same - that is, they both mean that if you rerun the SELECT later in the same transaction you will get back the same result.
The difference between REPEATABLE READ and SERIALIZABLE is REPEATABLE READ guarantees that if you rerun the same select command later in the same transaction, you will get back all the rows that were returned the first time the select ran and these rows will be unchanged. However, with REPEATABLE READ, you may get back additional, new rows that were not returned the first time. Therefore REPEATABLE READ does not need range locks, it just puts a shared lock on every returned row. But SERIALIZABLE not only guarantees that you will get back all the rows that were returned the first time the select ran and these rows will be unchanged, it also guarantees that no new rows will be returned. In order to do this, it sometimes needs to do a range lock.
So, for example, to see what REPEATABLE READ does, run
CREATE TABLE TestTable(
[name] [nvarchar](100) NOT NULL PRIMARY KEY,);
INSERT INTO TestTable (name)
VALUES ('H');
INSERT INTO TestTable (name)
VALUES ('K');
INSERT INTO TestTable (name)
VALUES ('P');
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN
SELECT* FROM TestTable
WHERE name Between 'J' And 'L';
Now, if you run sp_lock, you will see a KEY Shared lock on the index. That's because only the row that is returned (name = 'K') is locked. And if you open another connection and in the new connection run
INSERT INTO TestTable (name)
values('B');
SELECT * FROM TestTable;
INSERT INTO TestTable (name)
values('T');
SELECT * FROM TestTable;
INSERT INTO TestTable (name)
values('M');
SELECT * FROM TestTable;
Those inserts will work fine. And the table will now have six rows. And if you go back to the original connection, and redo the
SELECT * FROM TestTable WHERE name Between 'J' And 'N';
you will now get two rows, Name = 'K' and Name = 'M'. This is allowed under REPEATABLE READ because the only thing REPEATABLE READ guarantees is that the rows originally returned (Name = 'K' in this case) will be returned unchanged.
But SERIALIZABLE requires that no new rows are returned. So let's see what happens with SERIALIZABLE. First commit or rollback all open transactions, and drop the table. Then run
CREATE TABLE TestTable(
[name] [nvarchar](100) NOT NULL PRIMARY KEY,);
INSERT INTO TestTable (name)
VALUES ('H');
INSERT INTO TestTable (name)
VALUES ('K');
INSERT INTO TestTable (name)
VALUES ('P');
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN
SELECT* FROM TestTable
WHERE name Between 'J' And 'N';
Now if you run sp_lock, you will see that there are range locks. So, let's go to another connection and try to add new rows. If you do
INSERT INTO TestTable (name)
values('B');
SELECT * FROM TestTable;
INSERT INTO TestTable (name)
values('T');
SELECT * FROM TestTable;
Then will both work fine. That's because they will be outside the range locks. But if you do
INSERT INTO TestTable (name)
values('M');
SELECT * FROM TestTable;
The insert will be blocked. That's because inserting that row would change the result of the select query in the original connection which is not allowed with SERIALIZABLE.
OK, so now we've seen the difference between REPEATABLE READ and SERIALIZABLE, so the question is does HOLDLOCK do what REPEATABLE READ does or what SERIALIZABLE does. To find out, commit or rollback all the open transactions, drop the TestTable table, and run
CREATE TABLE TestTable(
[name] [nvarchar](100) NOT NULL PRIMARY KEY,);
INSERT INTO TestTable (name)
VALUES ('H');
INSERT INTO TestTable (name)
VALUES ('K');
INSERT INTO TestTable (name)
VALUES ('P');
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN
SELECT* FROM TestTable WITH (HOLDLOCK)
WHERE name Between 'J' And 'N'
Now run sp_lock. You will see that you once again have range locks. And when another connection tries to insert rows, it would be allowed to insert Name='B' and Name = 'T', but inserting Name='M' will be blocked.
So, HOLDLOCK is equivalent to SERIALIZABLE, not REPEATABLE READ.
本文转自UltraSQL51CTO博客，原文链接：http://blog.51cto.com/ultrasql/1839580 ，如需转载请自行联系原作者
