question

jennifer 2 avatar image
jennifer 2 asked

Deadlock in SQL

I have a parent table and few child tables that has foreign key with update and delete cascades referencing the Parent table's primary key. Now I have separate stored procedures to save the 3 tables separately. In my Save SP, I will convert the Primary Key column ID from newid() to Integer. So, I will have to update the keys everywhere using the cascade. Since all these Stored Procedures are called in a single transaction, a lot of deadlock occurs. I need some solution. I cannot remove the foreign key constraint or the update cascade.
deadlock
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
@Jennifer - please provide us with the sproc code so that we can see what is going on. Even better, provide the table create scripts and a little test data too.
1 Like 1 ·
Grant Fritchey avatar image
Grant Fritchey answered
If you have not done so, please enable traceflag 1222 on your system. This will capture what is called a deadlock graph into your error log. With that information you can determine exactly where the deadlock is occuring, which statement, which columns, all that stuff. Updating primary keys is a major design flaw. The whole concept of a primary key is that it should not be data that changes. If you have to change key values as part of the regular run, you need to reassess your primary key in the design.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
If you are on 2008+, then you can get the deadlock information out of the default extended event trace - best way to get at that is to use Jon Kehayias' [Extended Event Manager][1]. But, just to second Grant, don't update primary keys... [1]: http://extendedeventmanager.codeplex.com/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Yeah, have to get used to using extended events for more stuff. It's the future.
1 Like 1 ·
Oleg avatar image
Oleg answered
As Grant has already pointed out, updates on the primary key column should never happen. As a bare minimum, PK enforced by the unique clustered index should follow 3 guidelines. It should be: - narrow (if possible) - ever-increasing (which implies that uniqueidentifier column must never have a newid() default, but should opt for a newsequentialid() instead) - never, ever changing In fact, the chosen approach to **convert the Primary Key column ID from newid() to Integer** cannot work. If the ID column enforced by the clustered index then any attempt to alter the column from uniqueidentifier data type (16 bytes) to integer(4 bytes) would force the engine to completely rebuild and move the table. This would never happen due to the existence of the foreign key constraint on child tables. Additionally, it is not possible to alter the column from GUID to int anyway due to **Operand type clash: uniqueidentifier is incompatible with int** error. This means that the parent table will have to be rebuilt from scratch (create new table with int column plus all other columns of the original, copy data to the new table etc). Child tables might have to be rebuilt from scratch as well depending on whether you can tolerate the new foreign key column of the child tables to be the last column or not. After all is set and done, please do not forget to ensure that every child table has a non-clustered index on the foreign key column referencing the parent's column. Without it, you are guaranteed to have obscure deadlocks as thoroughly described in the excellent article by Alok Dwivedi titled [An Un-indexed Foreign Key Gotcha][1]. Please provide more information about your tables, so someone can come up with a more or less complete modifications script. Oleg [1]: http://www.sqlservercentral.com/articles/T-SQL/68337/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.