SQL Server Performance Forum – Threads Archive

DeadLoack

hello,
I am working on an online reservation system.DataBase i am working on consist of aroung 375 tables and using around 150 stored procedures.Size of DB is aroung 1.9 GB.
Problem i am facing is system is getting too many deadlock issues… i know that why deadlock issue occurs…and i also understand that we can change the structure of the DB to make them go.but i am at very later stage of development and chaging DB now would not be feasible we already have implemented the system into the office.. We are using SQL Server 2000 .. I want to know how can i make DeadLock issue come less.. is there anything i can do to increase the performce os sql server ? All your help is highly appreciated Sati
========================
WebMaster TempoHolidays
MCP Masters in IT
========================

I won’t ask you to change the DB as that possibility you have ruled out… But I would ask you to look at the order in which you are accessing your tables. I should be same across various procedues. Also tyr to reducce the duration of transaction in the database. Try to reduce any locking hints that may have been used in the sp’s. HTH Gaurav

Also in your stored procedures, wherever possible, do all your preliminary selects outside of a transaction, so you have all info you need to continue. Then you can begin the transaction and do whatever updates you need, meaning locks are held for shorter time like gaurav says.

In queries where you are not worried about dirty reads (if there are any), you can try to use NOLOCK hint. This will prevent a shared lock from being placed, and will allow another transaction to modify data.
You can also use ROWLOCK or PAGELOCK hint when updating data to lock only the row/page that is being updated. Take into consideration that if SQL decided to escalates lock level, ROWLOCK will become a TABLOCK and not a PAGELOCK.
You can view the process that are causing deadlocks in EM (or sp_lock) to try to understand the source of them. Bambola.

]]>