Deadlock solving question

[DClyde]

Hi there, I am a bit new to all of this and have had my first run in with a deadlock situation.

We know what is causing the deadlock but are unsure what the best solution is to fix the problem.

In our simplest case we have the following tables with three common transactions on them.

AssetMetaData: metadata_id (pk), name, asset_id, version_id
AssetDependencies: metadata_id, dependency_asset_id (many entries per metadata id)

Our database is structured in such a way that we have unique "assets" of which all can have multiple versions of the asset. Each time someone edits an asset a new set of table data is provided for the user to work with (so a new metadata entry with the same asset id but a new version id) These two combined are represented in other tables in the form of our auto incrementing metadata id.

Our common transactions that have deadlock issues are:

Save -
Update Metadata row with our metadata_id
Clear current associated dependencies with our metadata_id
Insert new dependencies with our metadata_id

Revert -
Delete all dependency rows with our metadata_id
Delete metadata row with our metadata_id

Checkout -
Find the current version of the asset in metadata table, insert new row as a copy but with a new auto increment metadata_id
Find all dependencies with the old id and insert them into the table with the new id (insert into from select)

Our common problem is that for foreign key/creation problems and also the fact multiple people are writing the code that our table are referenced in different orders. So one user will lock metadata and try to lock dependencies while the other user has a lock on dependencies and wants metadata.

Re-ordering is possible for the cases that do not create the metadata row, but in the creation cases it needs to go first where every deletion case it needs to go last due to foreign keys. I do not know how locks work in this regard though so I dont know if it is an issue. Re-ordering however seems fragile, someone else could come along and write his own save in a different order and things would appear ok until there is a high traffic day and then we would have problems again.

We know in these cases that no two users are editing the same rows so we were hoping only row locks would be used and no collisions would occur. Of course the database could always decide to promote to a table lock on us especially in cases like the dependencies table if there are enough dependencies being changed. Also in a case where we "UPDATE metadata WHERE metadata_id = @metadata_id" the sproc seems to get a U lock while looking for the row and ends up with a X lock on the specific row. But the U lock gets stuck if someone else has a specific row lock before we could lock our specific but different row.

Our current thinking is that we just need to forward declare our locks in the same order which would be easier to enforce, but my attempts at that seem to either be wrong or incorrect in our approach.

What do people suggest we do?
Is there any other information I can provide?

Thanks,
DClyde

 

[danvlas]

I'm not sure you need to do the foreign key update at all...
You can insert some 'tracking' fields in your AssetDependancies table, like CreationDate and CreatedBy, with default values GetDate() and suser_sname() respectively (I found out the two fields are absolutely brilliant in any table).
You can now easily identify the last (or any) dependancy by Asset, by user, by date or by any combination:

Select dependency_asset_id As AssetID from AssetDependencies Main Inner Join
(Select dependency_asset_id as ID, Max(CreationDate) as CreateDate From AssetDependencies Where CreatedBy = suser_sname() Group By dependency_asset_id) LastDep
On Main.dependency_asset_id=LastDep.ID and Main.CreationDate=LastDep.CreateDate

The red part can be changed to select only the combinations you want...

There are ways to retrieve historical data without deleting or updating any data, just by writing appropriate queries.

Just an opinion


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[DClyde]

I am not sure I explained things well the first time.

So, I am not sure what you mean by thinking I dont have to update my foreign key.

As far as the Dependencies retrieval information:
I think you are reffering to our
Delete all Dependencies associated with a metadata_id
Insert all Dependencies associated with a metadata_id

What this is doing (and I am deffinately open to optimizations here as this seems to be slow) is, lets say I have an asset Guy who refers to assets L.Arm, L.Leg but then I edit him and now he refers to assets R.Arm, R.Leg, L.Leg. We find it way easier to just clear anything he currently references and reinsert them rather than trying to find the differences and inserting/deleting the differences.

As far as the rest of it, our MetaData table is kind of the center of our version based universe. It keeps track of the different versions for each asset. Every other table storing asset data refers to it, so if I have a table storing dependencies or storing an xml file defining this particular asset it is always associated with the metadata_id which maps to a asset/version (it is actually more complex because we allow multiple people to edit the same version so it can branch out to asset/version/client unique rows but.. yeah) so all associated data reffering to a particular version of that asset uses the metadata_id which every other table enforces as a foreign key against the metadata table. If a user changes his mind, and wants to revert his changes to an asset we delete all associated rows. If a user saves we update all associated rows, and when a user choses to edit an asset we make new rows for him.

 

[genomon]

Have you looked at locking hints in BOL? With(rowlock) is a good thing to use with updates; With(nolock) is also useful in selects....

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."