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

Question

Hi all

I've just run into a bizarre problem when trying to deploy to new database. I have also ran the database.Schema.sql script directy in SSMS and get the same error repeatedly:

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

The line it relates to in the schema DDL script is inside a SPROC that has a DECLARE @TempTable AS TABLE (...)
I also tried commenting out the offending SPROC just to see if that resolves my issue and it happened again further down the script with another SPROC that also has a similar DECLARE @AnotherTempTable TABLE (...) statement.

Any ideas? I'm at a loss and cannot deploy the database.

---

Answer 1

Hi Jaans

When the script runs in the SSMS, the error also happens to you, right?

It looks like the problem is at SQL Server.

I've found Pinal Dave has a blog giving some fixes and suggestions, hope it help: http://blog.sqlauthority.com/2007/05/16/sql-server-fix-error-1205-transaction-process-id-was-deadlocked-on-resources-with-another-process-and-has-been-chosen-as-the-deadlock-victim-rerun-the-transaction/

In his words: Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other’s object. When this happens, SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.

Fix/Workaround/Solution:
Deadlock priority can be set by user. In other words, user can choose which process should stop to allow other process to continue. SQL Server automatically chooses the process to terminate which is running completes the circular chain of locks. Sometime, it chooses the process which is running the for shorter period then other process.

To reduce the chance of a deadlock:

• Minimize the size of transaction and transaction times.
• Always access server objects in the same order each time in application.
• Avoid cursors, while loops, or process which requires user input while it is running.
• Reduce lock time in application.
• Use query hints to prevent locking if possible (NoLock, RowLock)
• Select deadlock victim by using SET DEADLOCK_PRIORITY.

Thanks.

I moved it to SQL Server forum for further and better response.

Figo Fei

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

In case this is SQL Server 2005 and above, you will have to enable -T1222 to get the deadlock graph in the SQL Errorlogs and -T1204 for SQL Server 2000.

The following blog post explains how to decipher the deadlock graphs:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx 

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL

Answer 3

You need to capture a deadlock graph as explained by Amit.  With that you can best determine why the deadlock is occuring.  I'd expect that you will find a schema lock that is being held and your resulting in a cross lock, possibly at the database level.  Can you deploy this if you break the script down into individual object scripts or deploy the objects manually by highlighting portions of the script and running them manually?  So other references on troubleshooting deadlocks are:

http://sqlblog.com/blogs/jonathan_kehayias/archive/tags/Deadlock/default.aspx

I also have a series of videos on troubleshooting deadlocking on JumpstartTv.com as well:

http://jumpstarttv.com/profiles/1379/Jonathan-Kehayias.aspx

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 4

Thanks for you replies Figo, Amit and Jonathan

Ps: Yes I do get this when running from SSRS.

Figo: I'm not sure your moving the post away from the Visual Studio Database Edition is the right thing, because this is specific to the database deployment tool for the "datadude" edition of visual studio. I believe that mostly users of Visual Studio Database Edition will encounter the issue.

All:
I have since discovered it happens when you generate a database deployement script from Visual Studio Database Edition's deployment tool, AND YOU HAVE IT SET TO GENERATE THE DEPLOYMENT SCRIPT WITH TRANSACTION PROTECTION. The Deployment setting under advanced option is "IncludeTransactionalScripts" for the deployment configuration.

If I regenerate with out the transactional scripts, the script succeeds to create the database.

Where the SQL Server aspect comes into it, is that it seems that you cannot have a DECLARE @MyTable TABLE (...) statement (inside a SPROC) that is part of a greater script that has transactional support is implemented by the Visual Studio Database Editions deployment tool.

I'm happy to provide in confidence the offending script to MSFT for correction.
Alternatively, repro:
* Create a database schema in VS "DataDude"
* Include a SPROC that - creates a temporary table variable (DECLARE @Temp TABLE (...)
* Try to deploy that database using "IncludeTransactionScripts" and see it fail.

Hope that helps

---

Answer 5

What you are seeing sounds like a bug that should be filed on the connect site for Visual Studio:

http://connect.microsoft.com/VisualStudio/feedback/

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 6

Hi Jaans

If you submit a ticket at http://connect.microsoft.com/VisualStudio/feedback/ , it is better that you can paste its link here, so that community members could see it.

Thanks!

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 7

Created feedback on connect site (as suggested):

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=509393

Regards
Jaans

---