SQL Server Concurrency
Lock compatibility |
|
Been having all kinds of issue lately with Sch-S lock ( Schema Stability Lock). It is incompatible with Sch-M (Schema Modification lock). Had a stored procedure that had DBCC CheckIdent inside the code block with the reseed option. This process acquires a Sch-M lock. If you have any other process taking a Sch-S lock, they will block each other. Microsoft pointed out to us that even Select statements take Sch-S locks and any DDL statements which require Sch-M can block each other. That is crazy!!
Here is a link to technet article on Lock Compatibility
http://technet.microsoft.com/en-us/library/ms186396(v=sql.105).aspx
Here is a link to technet article on Lock Compatibility
http://technet.microsoft.com/en-us/library/ms186396(v=sql.105).aspx
Turning on Read Committed Snap Shot Isolation |
|
Had to turn on RCSI for a database. Kinda freaked when I didn't see it when right clicking on the DB name and then selecting it from options. I could of sworn I saw it under properties. Anyway!!!
Figured it out!!! If you are using SSMS 2012 you can find it by right-clicking on the DB and selecting properties. Boom!!!
Used this code to accomplish it. Don't forget WITH ROLLBACK IMMEDIATE or it will just sit there.
ALTER DATABASE <DBNAME> SET READ_COMMITTED_SNAPSHOT ON with rollback immediate
Figured it out!!! If you are using SSMS 2012 you can find it by right-clicking on the DB and selecting properties. Boom!!!
Used this code to accomplish it. Don't forget WITH ROLLBACK IMMEDIATE or it will just sit there.
ALTER DATABASE <DBNAME> SET READ_COMMITTED_SNAPSHOT ON with rollback immediate
Query Hints
If you are using RCSI and SQL decides to take a page-level lock for a DML statement, then blocking and deadlocking can occur. We solved this issue with using a query hint (ROWLOCK, UPDLOCK) forcing SQL to take a row-level lock. Use query hints sparingly unless you think you are smarter than the optimizer.
Check out this article.
http://msdn.microsoft.com/en-us/library/ms187373.aspx
Check out this article.
http://msdn.microsoft.com/en-us/library/ms187373.aspx