November 22, 2003 at 3:21 am
Please help me out here.
One of the stored procedures we have in a invoice processing system is causing a deadlock situation. The error that appears is :
Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID ) was deadlocked on lock resources with another process and has been chosen as the deadlock
balaji ramanar
November 23, 2003 at 11:44 am
You have two transactions that lock each other. You can find out the processes by setting flag 1204. Check the books online on tips how to prevent deals with dead locks.
November 24, 2003 at 1:05 am
they may be other one who query from this table (so it locks it) and you are waiting for him to release the lock
my advise to you is to see are there anyone query this table .. if yes, then the best thing is to run the query on the server (by making Views in SQL Server 2000) .. and with each table name in the query, add this statement "with (nolock)" .. so you read data as it is (not recently updated = not commited transaction), and you don't wait for locks to be free and you don't lock these tables, and any other transaction will complete successfully (for you and others) 🙂 .
you will write something like this in your View:
select Invoices.*, Situation.*
from Situation with (nolock) INNER JOIN Invoices with (nolock)
ON Situation.SituationID = Invoices.Situation
where Situation.SituationID =1
Disadv:
- when using "with (nolock)", you will not seen comitted transaction (changed one) at this moment ... but you can see it at any other time.
Adv:
- no hang at all
- very fast Response
- Little summary for Locks in SQL Log file.
also : you may need to add more Memory (RAM) when server still hangs a little after transfering your queries to path-through queries.... becuase every transaction or query is done on server first, then processed data is passed to your PC.
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
November 24, 2003 at 5:17 am
choose the appropriate transaction isolation level for each of the processes that were dead loacked. it should solve your problem.
November 24, 2003 at 7:21 am
November 24, 2003 at 7:49 am
To add to the already useful info on this thread...........
On EM if you click on Management ...
Current activity locks Process id that will
give you who is sitting on what lock and who is looking for a way to get to it .There is also a free Microsoft storedpro.exe zip file which creates a procedure to give you the same information and you can grant access on this procedure to those you want.
Mike
November 24, 2003 at 9:24 am
You might want to check out KB169960. I had a similar problem and found that specifying a fill factor of 50 eliminated the deadlocking. This will spread out your rows over different pages. SQLServer in my opinion does a terrible job of handling deadlocks and housekeeping.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply