What is a scope of a table hint lock?
Let's say I have a stored procedure with no transaction blocks specified and a default transaction isolation level, which does following:
SELECT [something] FROM myTable WITH (UPDLOCK)
SELECT [something else] FROM myTable
IF (condition) INSERT INTO myTable VALUES [stuff]
How would that UPDLOCK actually work? Would the update lock extend to the second select too? Is there a possiblity of deadlocks here if the SP is called multiple times at the same time and the hint is not told to cover the whole table?
I've read the online documentation but I admit I'm lost trying to put resource scopes and lock modes together. A clear explanation would be very appreciated.
Once lock is put on an object, it is held until the end of transaction (explicit or implicit). If you put your two statements in one transaction, the lock used in the first one will be effective in the second one.
The same applies to calling a stored proc multiple times inside one transaction . The locks from each call will be held until the end of the transaction. (Transaction isolation level is reverted at the end of stored proc but not the locks themselves).
Lock lifetime is not completely documented. Locks are acquired, released, and held as to provide the promises of the requested isolation level. Strict 2 phase locking is sometimes obeyed, but you cannot rely on it. Lock lifetime is influenced by the plan choices and by other factors (eg. resource utilization).
The general wisdom goes that S and U locks are held 'as long as necessary' (what that means depends on the query plan) and X locks are held until the end of transaction.
Rather than present us with an abstract storage engine implementation detail question, is much better to present the actual problem you're trying to solve. Is impossible to tell from your question what are you trying to solve. Perhaps app locks will work better. Perhaps serializable transaction is needed.
In the spirit of teaching a man to fish, you can take a look at sys.dm_tran_locks and sys.dm_tran_session_transactions to see what locks are held by your session at various stages. Alternatively, you could set up an extended event session that captures lock_acquired, lock_released, and something like sql_statement_completed (limited to your session id of course so the output is not overwhelming).
链接地址: http://www.djcxy.com/p/32844.html下一篇: 什么是表提示锁的范围？