• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

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?
0
deepaknet
Asked:
deepaknet
1 Solution
 
deepaknetAuthor Commented:
Adding to it ...

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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...
0
 
deepaknetAuthor Commented:
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?
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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...

0
 
deepaknetAuthor Commented:
Enterprise Edition (Service Pack 4)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Can you post the SP here, let us have a look
0
 
deepaknetAuthor Commented:
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?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, that looks fine.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no objection

<offtopic>
  welcome back, ee_ai_construct !
</offtopic>
0
 
Vee_ModCommented:
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now