I have two procedures running in different SQL Server Agent jobs. Both update the same table. Now I am getting deadlock error.
The table also has a trigger to update a history table.
When I checked the XML deadlock report, it shows the following information:
<owner-list>
<owner mode="U" />
</owner-list>
<waiter-list>
<waiter mode="U" requestType="wait" />
</waiter-list>
Trying to figure out what the issue is. Issue is intermittent.
You may have to share your stored procedure code for us to be able to help. The XML code alone is not enough.
With that said, are you wrapping the code in a transaction? And then locking the table?
Like this:
BEGIN TRANSACTION
SELECT *
FROM tblYourTable
WITH (TABLOCK, HOLDLOCK) -- lock the table until the end of the transaction
-- Continue other queries with the table (Updating, Inserting, etc.)
COMMIT TRANSACTION -- release the lock (and transaction) after you are done with all of your processing.
You can also use a rollback if things go wrong.
However, to answer your question about U locks, here is a good summary of how locks work in SQL Server: Lock Types