Just to clarify, indexing (clustered or nonclustered) makes the query faster, (instead of a scan, it does index seek), therefore a shorter time of locking or less locks obtained. Didn't have to make the index a clustered one. SQL Server choose index seek for all 3 commands.
#Page lock deadlock sql server code#
I've run the code provied and after creating the 2 NON-CLUSTERED indexes, the queries completed successfully without a deadlock. I will try this weekend and see if it makes a difference What version of sql server are you using (include edition)? This could effect your results although I think I've tried it on both 20 with the same result. I've tested on 2005 developer and 2008 R2 enterprise. Just to confirm you are using the second set of queries as they changed slightly from the first example? Not sure what else would cause you to get a different result. I confirmed both were doing index scans as well. Interesting.I only have enterprise edition but I tested on both (32-bit) and (64-bit) and both deadlocked with only the non-clustered indexes. When using the queries from the 2nd one, it works the way you described (deadlock with non-clustered index). No, I've used the queries from the first example.
#Page lock deadlock sql server update#
Profiler can be used to see the entire sequence of acquiring and releasing UPDATE locks during a scan while searching for the rows to update.
I used sys.dm_tran_locks to look at the locks. There are other better solutions in the vast majority of cases. I don't recommend using them to solve problems. Please note that I only used an index hint in this test example. Then the nonclustered index seek is used without an index hint and there is no need to change which columns are used for the clustered index.Īlso, if col3 is updated in the 2nd example instead of col2, then the nonclustered index seek is automatically used with the 1000 row table because a table spool isn't needed. With a larger table this will not be the case. The table spool made the estimated cost of the plan using the nonclustered index seek more expensive than the clustered index scan plan. Then I saw that a table spool was needed when the nonclustered index seek was used because the column being updated is the same column used to select the row. I was wondering why the 2nd example wasn't using the nonclustered index. Thanks dear, you have save my job, i tried since 10 days, in my table there is insert and update from different 5 sql jobs, so index every time disturb, i remove cluster index with primary key and add primary key with non cluster index.
Identify columns in your tables that are a good candidate for clustered index.Check out the SQL Server Tables without a Clustered Indextip for useful queries to diagnose the issue in your SQL Server databases.
Ensure all your tables have a clustered index.Use query plans and profilerto provide more insight as to why you may be encountering deadlocks.Testing with your application/database should be done to ensure that you are getting the best performance possible. In my experience I have found columns that are heavily used in WHERE clauses are usually a good candidate, but there are many factors (table size, heavily skewed data, other indexes, etc.) that can affect the query plan and locks that the SQL Server optimizer decides to use during execution. That is why we saw a deadlock in the previous scenario.Ĭareful consideration needs to be taken when laying out the indexes on your SQL Server tables and even more attention placed when deciding on which column(s) the clustered index should be put on as this can have a dramatic impact on locking in your database. This means that no other processes can update any of these records until these shared locks are released. If we were to look at the query plan for the previous scenario we can see that in that case the optimizer chose to do an index scan hence it acquired shared locks on all the records it scanned. For the scenario with the clustered unique index we see the optimizer chose to do an index seek hence it only locked the records that it was updating allowing other processes to update other records in the table concurrently. In addition to using sp_lock and sp_who2 we can use the query plan for one of the statements from our test scenario, UPDATE dbo.TABLE1 SET col2=1 where col2=1, to highlight why we see the locking that we are seeing.
0 kommentar(er)
