Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ziva55
Starting Member
4 Posts |
 Posted - 2009-12-14 : 11:31:52
|
| Hello,I have a critical Financial system which recently fails with error message : Transaction DeadlockedTechnical Information:• SQL Server 2005• Visual Basic 6 using ADO Connection to the Database• The connection to the DB made by user SA Application Information:A part of our application imports a huge amount of Bank transactions to the DB from an external source and builds Balances on a daily based for an account, when user inserts a retro transaction or deletes a transaction, the application must rebuild the balances of the account according to the transactions info.In this process our application deletes the balances from this point up to today and rebuilds them.whole the process made within an ADO BEGINTRANS and COMMITTRANS commands.Our application uses a default Isolation level, and Optimistic Locktype when open recordsets.Recently the application failed frequently with message : Transaction (Process ID xxx) was deadlocked on (xxx) resources with another process and has been chosen as the deadlock victim. Rerun the transaction.Do someone have any advice?Thanks,Ziva |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-14 : 12:40:47
|
| You'll need to enable the 1222 trace flag in order to capture the deadlock output. What you have so far is only the deadlock victim, you'll need to capture the deadlock owner.You should cnosider using READ_COMMITTED_SNAPSHOT to avoid reads/writes from deadlocking with each other. See BOL for details.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
ziva55
Starting Member
4 Posts |
Posted - 2009-12-15 : 07:56:24
|
| Hi Tara,thanks for your reply.i checked how to enable trace flag 1222, and found a command like DBCC TRACEON (TF-1222 )i'm not familiar with SQL Trace, could you please provide more information how and where can i check and analize the results of this tracethanks for your advice. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-15 : 10:56:35
|
| It's DBCC TRACEON(1222,-1). Once you run that, the deadlock information will go into the SQL Server Error Log once a deadlock is encountered. DBCC TRACEON is just for the current session of SQL Server, so if you restart the service you'll lose this. That's why you should also put it as a startup parameter (-T1222), which you can do in SQL Server Configuration Manager.You could also use SQL Profiler to show the deadlocks, but you'd have to leave it running. SQL Profiler can show the deadlock graph, although I find the error log to be more useful.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
|
|
|
|
|
Urgent - Transaction Deadlock !!!