How to profile azure SQL deadlocks?

Question

I know this question was asked here but 1) it's relatively old and 2) It didn't help me much.

I am running into a relatively large number of deadlocks with a few operations on my database. The setup is as follows:

Tables:

Table A with foreign key into Table B.

Operations:

Insert into table A

Insert into table B

Update row in table B

Delete row in table B

Delete row in table A

Problem:

These operations can happen essentially in any order because I have multiple worker roles so these operations must be idempotent, however, each worker role will be working with a different primary key from table A. I am still trying to wrap my head around the concept of locks on tables and from what i understand, any delete on A will first lock table B, delete relevant rows there, and then delete the row from A. I currently assume that is an atomic operation and there is no time to execute additional locks between locking table B and locking table A because I can't imagine a way to get around that.

I am currently able to catch an exception in microsoft visual studio of the following format:

Transaction (Process ID xxx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

This exception seems like it can happen on any of the above operations.

My question is: How do i know which locks/transactions are the ones causing the deadlock? Does anyone know any queries that would be useful AFTER we get the exception?

Answer 1

sys.event_log is the answer here.

It lives in your server's masterdb and should contain an entry with all of the deadlock graphs your database has hit in the last month.

Armed with the deadlock graph there are many tutorials on sql server deadlock graph debugging.