Deadlock resolution using lock hints

[DClyde]

So.. I think I went about my question wrong last time so I am revisiting this with something way more focused and see if anyone can enlighten me.

I have the following two transactions going on at the same time by different clients.

Revert

--Ex: Asset 1234, version 2 which has a metadata id of 4321
-- @asset_id = 1234
-- @metadata_id = 4321
-- @edit_lock = 1

DELETE FROM Dependencies
WHERE metadata_id = @metadata_id

DELETE FROM FileDependencies
WHERE metadata_id = @metadata_id

--Delete this last since all foreign references refer to him
DELETE FROM AssetMetaData WHERE metadata_id = @metadata_id

Save
-- Ex: Asset: 6789, version 3 which is metadata_id: 9876
-- @metadata_id = 9876
UPDATE AssetMetaData
SET name = 'name', more_data = 'data'
WHERE metadata_id = @metadata_id

--Clean us out so we dont need to figure out what is new, what is changed, what is deleting, just start fresh
DELETE FROM Dependencies
WHERE metadata_id = @metadata_id

--Called once per dependency
INSERT INTO Dependencies (metadata_id, dependency_id)
@metadata_id, @dependency_id

--Clean us out so we dont need to figure out what is new, what is changed, what is deleting, just start fresh
DELETE FROM FileDependencies
WHERE metadata_id = @metadata_id

--Called once per dependency
INSERT INTO FileDependencies (metadata_id, file_id)
@metadata_id, @file_id


Now, I happen to know these will never be accessing the same rows, we have other means to prevent that, these actually have some checks around them to ensure that.

My issue is that they are in a different order (and I will be fixing the order but I am looking for a more robust solution) and we end up with the save with a row lock on the metadata table, and the revert with a row lock (or two) on the dependencies table. The revert then goes to delete the row from the metadata table, but within the "Delete From AssetMetaData WHERE metadata_id = @metadata_id" it attempts to get a Update lock, presumably on the same page that the Save has a row lock and, vice versa with the save accessing the dependencies table and I deadlock.

My question is.. without reordering things is there a way to make it so that this works? Like I said I know with certainty that are editing/deleting/whatever different rows.

 

[SQLSister]

Is there a cursor in this somewhere? "Called once per dependency" leads me to belive there might be. Depending on what is in the loop that could be the issue.

First, I am not a fan of deleting everything and then inserting it all again. That is way more work than needs to be done and is taking much more of your processing time than insert, update and delete statements that were suitably qualified would take. It also means a fairly vast adjustment of the pages where the data is stored for particularly good reason. I don't know how many dependencies we are talking about but if only one is new and two are changed and two deleted that is far less work than deleting 10000 records and inserting 9999 records. Plus if this is in a cursor, you are doing it one record at a time which is always a poor choice. If the transaction is wrapped around the whole loop, then it can't release the records until all are done even when many many more than need be are being affected. Just fixing that may remove the deadlock because the work is so much faster the procs might not collide.

Also depending on the indexing, this need to adjust the index more than need be due to creating so much extra unneeded work could be what is causing the the lock which creates the deadlock. A clustered index actually has to move rows around when rows are deleted, updated or inserted. If you have one, it could be why SQL Server feels the need to lock the table.

Just theorizing here. Don't know for a fact that is the problem.

Also I see a variable called @edit_lock. What does that do? Is it somehow creating the table lock or page lock that causes the deadlock? Can it be that it is your process to try to prevent people from editing the same record that is causing the deadlock?

"NOTHING is more important in a database than integrity." ESquared

 

[DClyde]

There are no cursors, we originally had some cursors for something else and when we hit the deadlocking we worked around them "just in case" (was easy to convince us since no one seems to like them). Numbers of dependencies could vary from 0 to 5000 (estimated), I understand this one deffinately risks promotion to a table lock. File Dependencies tend to have 0-1 entries only.

The loop I refer to is done in C++ code. The save is done in a tool that has local data it is uploading to the server. This is also why the delete/add is easier for us. Otherwise we would have to loop through each one, do a query to see if it is there, then do another query to add it or update it, then we would have to do another query after looping to determine if there are any on the server that dont exist any more that would need to be removed and a last query to remove them possibly so we would go from N + 1 queries to 2N+1 if there was nothing to delete or 2N+2 to delete if there was. There is a bit of overhead to this so we avoid it using what I mentioned. We are looking at doing this differently where we upload a bunch of data to temp tables then have a SProc to "publish" the data to a real table which would address some other issues we have with bulk saves.

The Revert however happens in a SProc all in the database, I probably should have specified the difference there for clarification. Both actions however run in transactions to run as a whole.

There is only a clustered index on The AssetMetaData table, no other tables have one currently. Indices do worry me because we always have to remove the AssetMetaData row last since everyone refers to him, and when we add a new one we always need to add him first since everyone refers to him and I have read that inserting only does row locks but the Indices add a complexity I wasnt sure if that would cause an issue. I am already trying to make sure the updates/deletes all happen in order to minimize problems.

In all of our deadlock logging however our current problem does not involve indices and only with an "update x where y" takes an update lock on more than a row while it is looking for y at which point it converts to a row lock. It is very possible after resolving the row stuff we will find problems with the indices but one problem at a time

@edit_lock is an entirely different concept, I just forgot to trim him before I posted. In our world an "edit_lock" is just something specifying what type of data the metadata row and all other associated rows in other tables is. We have two options, Edit and Debug. Debug data is never "published" for others to see but still lives in and interacts with the database the exact same way. This is part of the system that allows me to be certain I know that no two operations are operating on the same rows.