Select, Update , Insert on single table SQLServer2005

Question

Select, Update , Insert on single table SQLServer2005

C.Gershom

Newbie

Points: 2
More actions
November 7, 2006 at 11:16 pm

#69142

I have a client application which runs three threads "Select", "Update","Insert". Also there are three stored procedures "Select" , "Update" , "Insert" which are used by corresponding threads. Default isolation level "READCOMMITTED" is mentioned in "Select" stored procedure.
We are using SQL Server 2005
The way selection and updation occur is,
Suppose there are 10 records in Table1. "Select" thread starts from say SK 1, "Update" thread starts updating from SK 7. Similarily runs the "Insert" thread.
When we run multiple client applications around 10. We get transaction deadlocks. Please Helllllllp!!!!!!!!!!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you post the code of the stored procedures and the table structure please

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

cje Mr or Mrs. 500 Points: 513 More actions · Answer 2

since your "select", "update" and "insert" run in separate threads, it is almost certain to get blocks and conflicts - you will not be able to do "updates" and "inserts" at the same time on the same table. This does not seem like the proper use of threads.

C.Gershom Newbie Points: 2 More actions · Answer 3

worked out using snapshot_isolation_level. Thanks for your efforts to solve my problem.