SQLServer DeadLock on Multiple Table Updates through Stored Procedures
When an user tries to do an electronic financial transaction, we have a stored procedure that needs to update five tables. We also have a couple of intermediate selects and updates in the stored procedure. The problem is that when the web server encounters to run this stored procedure even with ten concurrent users to this page, SQLServer succumbs to deadlock errors.
Can somebody throw some light on optimizing this route?
Can somebody throw some light on optimizing this route?
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
what exact version/sp is your sql server?
apart from that, you will need to ensure that you lock the tables carefully in the beginning, and exclude as much intermediate work that could be done before the actual transaction.
looking at the code might help...
apart from that, you will need to ensure that you lock the tables carefully in the beginning, and exclude as much intermediate work that could be done before the actual transaction.
looking at the code might help...
ASKER
Hello,
The SQLServer is 2000. But what do you mean by version of Stored Procedure (sp)?
Currently I am ensuring that the table operations strictly follow one by one (A, B,C,D in that order) and logically I expect all concurrent users (scaling up) follow this is.
Am I missing something?
The SQLServer is 2000. But what do you mean by version of Stored Procedure (sp)?
Currently I am ensuring that the table operations strictly follow one by one (A, B,C,D in that order) and logically I expect all concurrent users (scaling up) follow this is.
Am I missing something?
>The SQLServer is 2000. But what do you mean by version of Stored Procedure (sp)?
select @@version will indicate the service pack of sql server, I do not speak of a version of the procedure, but I would like to look at the code of the procedure...
select @@version will indicate the service pack of sql server, I do not speak of a version of the procedure, but I would like to look at the code of the procedure...
ASKER
Enterprise Edition (Service Pack 4)
Can you post the SP here, let us have a look
ASKER
I have resolved it in a differnt way though. From the UI, I have divided the screen into Wizard so that each step has a distinct difference instead of accumulating (piling) everything and burdening the webserver/db server.
The page TaskManager spiking is also less now.
I think, this should ease the server and enhance its performance. I think we can claim this a a not-bad though a rework solution. What do you say?
The page TaskManager spiking is also less now.
I think, this should ease the server and enhance its performance. I think we can claim this a a not-bad though a rework solution. What do you say?
yes, that looks fine.
no objection
<offtopic>
welcome back, ee_ai_construct !
</offtopic>
<offtopic>
welcome back, ee_ai_construct !
</offtopic>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
An Internet research gave us these points: Deadlock can be avoided if table operations are followed sequentially like Table A first, Table B second etc and this order is religiously followed by all users. Anyway, the design of the SP adheres to this and but we still deadlock is haunting us.