CODEWITHSUNDEEP
t-sqlazureazure-sql-database
Paul Sachs
Paul Sachs

Reputation: 1035

How to profile azure SQL deadlocks?

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?

Upvotes: 3

Views: 3910

Answers (4)

Brian Gianforcaro
Brian Gianforcaro

Reputation: 27190

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.

Upvotes: 5

hocho
hocho

Reputation: 1749

I ran into similar issues recently. Try using your updates with "with (UPDLOCK)".

Upvotes: 0

Klark
Klark

Reputation: 8280

Currently profiling tools for Sql Azure are practically non existent.

The locking problem shouldn't differ much between standard Sql Server and Sql Azure world thus I would suggest trying to repro the problem in the 'old' world using standard techniques such as good old Profiler: quite useful article & this.

If that approach doesn't prove to be fruitful a dirty solution could be to work on catch/retry logic.

Upvotes: 0

Shiraz Bhaiji
Shiraz Bhaiji

Reputation: 65461

To try and find the root cause:

  • Start by just running a single worker role.

Then check:

  • Are you locking at the right level table lock, page lock or row lock?
  • Are you releasing the locks?
  • is your system designed in such a way, that all edits to the same row will be done by the same machine?

There is a blog post on finding blocking queries here: http://blogs.msdn.com/b/sqlazure/archive/2010/08/13/10049896.aspx

Upvotes: -1

Related Questions