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 |
|
gautamr
Starting Member
1 Post |
 Posted - 2008-10-08 : 02:39:00
|
| Hi We have a J2EE application driven application which uses SQL Server as backend, the application performs searches on members within SQL database and also carries some insert and update transactions.The logic for Search, update and inserts are carried out through T-SQL procedures, there is a separate procedure to carry out search, a separate proc for updating the member information in the sql server, and another proc to do the member insert.We currently hosted the application live, lately we are facing problems. Within SQL Server enterprise manager under Management-->Process Info,it appears to be that some of the processes are being blocked by other processes, i.e The Insert procedure is being blocked by the search procedure at time, and this procedure appears to be in waiting mode.Sometimes the update procedure locks the insert procedure, in shortall the processes correpsonding to a T-SQL transaction get blocked byanother. Since many users are accessing the system, it appears to bethat processes corresponding to these transactions arent synchronised.I didnt find anything significant in the T-SQL procs which could cause the transaction to lock explicity, the T-SQL codesimply has some plain insert, update and select commands.Please help , i wish to know how do i do the necessary tuning within the database or reduce these deadlocks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-08 : 03:11:37
|
| Code the procedures such that it wont lock tables for too long. like using temporary tables to get data and then do processing rather than accesing main table all the time. You can use query execution plans to analyse how procedures perform and try rewriting ones that prove to be bottleneck. |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-10-08 : 10:55:13
|
| Some rules of thumb:a) Update the tables in the same order for all transactionsb) Do not hold transactions for longer than you have to (get your data, begin, update, commit - don't hold them open across pages or during user activity)c) Use multi-row versioningd) Make sure your queries and updates have decent plans to keep execution (and therefore contention time)downe) Always use identities rather than hand-rolled surrogate key generators |
|
|
|
|
|
|
|
|
T-SQL transactions