Hi,
The article was interesting, although not much more than you would get from the SQL Books Online. I would have found a more detailed discussion about situations where each lock type/hint should be used, very useful - maybe in a future article?
The paragraph about deadlocks I find insufficient - it doesn't explain how a deadlock actually happens - only halfway. It should be mentioned that both threads are waiting for shared resources which is held by the two threads. Thread A waits for resource 1 which is locked by thread B, and thread B waits for resource 2 which is held by thread A. Neither can go on before they haven't obtained the next resource, but that is impossible since they're waiting for each other. A deadlock occurs. This is very different from threads waiting for locks to be released.
Awaiting your comments.
Albert
Discussion on:
View:
Show:
Good day all.
I have a transaction set to an isolation level of REPEATABLE READ and I insert a set of values in a table given from a query.
I know that only the data used in the select to do the insert will be locked, but why does SQL server create a deadlock when another session of the transaction occurs, even although the data its inserting is different from the other concurrent transaction?
I have a transaction set to an isolation level of REPEATABLE READ and I insert a set of values in a table given from a query.
I know that only the data used in the select to do the insert will be locked, but why does SQL server create a deadlock when another session of the transaction occurs, even although the data its inserting is different from the other concurrent transaction?
"PAGLOCK-Locks the table"
While PagLock operates on tables; PAGLOCK hints cause a MS Sql Server DBMS to lock pages (rather than applying row or key locks or a table lock).
While PagLock operates on tables; PAGLOCK hints cause a MS Sql Server DBMS to lock pages (rather than applying row or key locks or a table lock).
Additional behaviour can be achieved with SET TRANSACTION ISOLATION LEVEL...
To stop deadlocking in the particular situation where you are reading data only and know that there is no updating, setting the connection transaction isolation level to READ UNCOMMITTED stops locks and lock escalation (SQL Server will take an intent lock on a table even if it is just reading from it).
Also useful if you want to probe a long-running update/insert on another connection for example; put SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the top of your script, then select from the relevant table with the (NOLOCK) hint and you can see the progress even in the middle of a transaction.
To stop deadlocking in the particular situation where you are reading data only and know that there is no updating, setting the connection transaction isolation level to READ UNCOMMITTED stops locks and lock escalation (SQL Server will take an intent lock on a table even if it is just reading from it).
Also useful if you want to probe a long-running update/insert on another connection for example; put SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the top of your script, then select from the relevant table with the (NOLOCK) hint and you can see the progress even in the middle of a transaction.
To get more information about Isolation levels in SQL Server including Locks and its impact on database in detail with examples, refer the below link:
http://www.sqllion.com/2009/07/transaction-isolation-levels-in-sql-server/
http://www.sqllion.com/2009/07/transaction-isolation-levels-in-sql-server/
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
