SQL Isolation levels or locks in large procedures

I have big stored procedures that handle user actions.

They consist of multiple select statements. These are filtered, most of the times only getting one row. The Selects are copied into temptables or otherwise evaluated. Finally, a merge-Statement does the needed changes in the DB. All is encapsulated in a transaction.

I have concurrent input from users, and the selected rows of the select statements should be locked to keep data integrity.

How can I lock the selected Rows of all select statements, so that they aren't updated through other transactions while the current transaction is in process?

Does a table hint combination of ROWLOCK and HOLDLOCK work in a way that only the selected rows are locked, or are the whole tables locked because of the HOLDLOCK?

SELECT *
FROM dbo.Test    
WITH (ROWLOCK HOLDLOCK ) 
WHERE id = @testId

Can I instead use

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

right after the start of the transaction? Or does this lock the whole tables? I am using SQL2008 R2, but would also be interested if things work differently in SQL2012.

PS: I just read about the table hints UPDLOCK and SERIALIZE. UPDLOCK seems to be a solution to lock only one row, and it seems as if UPDLOCK always locks instead of ROWLOCK, which does only specify that locks are row based IF locks are applied. I am still confused about the best way to solve this...

---

Changing the isolation level fixed the problem (and locked on row level):

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Here is how I tested it. I created a statement in a blank page of the SQL Management Studio:

begin tran
select 
    *
into #message
from dbo.MessageBody
where MessageBody.headerId = 28

WAITFOR DELAY '0:00:05'

update dbo.MessageBody set [message] = 'message1'
where headerId = (select headerId from #message)

select * from dbo.MessageBody where headerId = (select headerId from #message)

drop table #message
commit tran

While executing this statement (which takes at last 5 seconds due to the delay), I called the second query in another window:

begin tran

select 
    *
into #message
from dbo.MessageBody
where MessageBody.headerId = 28

update dbo.MessageBody set [message] = 'message2'
where headerId = (select headerId from #message)

select * from dbo.MessageBody where headerId = (select headerId from #message)

drop table #message
commit tran

and I was rather surprised that it executed instantaneously. This was due to the default SQL Server transaction level "Read Commited" http://technet.microsoft.com/en-us/library/ms173763.aspx . Since the update of the first script is done after the delay, during the second script there are no umcommited changes yet, so the row 28 is read and updated.

Changing the Isolation level to Serialization prevented this, but it also prevented concurrency - both scipts were executed consecutively.

That was OK, since both scripts read and changed the same row (via headerId=28). Changing headerId to another value in the second script, the statements were executed parallel. So the lock from SERIALIZATION seems to be on row level.

Adding the table hint

WITH ( SERIALIZABLE)

in the first select of the first statement does also prevent further reads oth the selected row.

链接地址: http://www.djcxy.com/p/32834.html

上一篇: 锁定共享模式锁定整个表格

下一篇: SQL隔离级别或大型过程中的锁定