|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
Deadlocks
Hi,
I do have an enquiry. We are running on SQL 2000, but we faced a lot of deadlock issues. What are the features that is avaliable in SQL 2000 to reslove this problem or what will be a good suggestion to reslove this deadlock issues. Thank you in advance. Kelvin |
|
#2
|
|||
|
|||
|
RE: Deadlocks
The following text is from someone at microsoft that was posted in the newsgroup microsoft.public.sqlserver.programming. It is VERY good advice:
"Some ways to avoid Deadlocks are as follows: 1. Make sure the order of tables being accessed in all the transactions are the same.Say a programmer writes a transaction that modifies the Products table and then the Customers table.If another programmer writes a second transaction that accesses the same tables in the reverse order, the chance of a deadlock is greater. Make sure that the order is always remained same. 2. Avoid user interaction in transactions, and keep transactions short and in a single batch. 3. As far as possble, do not use lock hints to induce more locking than necessary. 4. Know that the transaction Isolation Level has a lot of impact on the way locking is done in SQL Server in that database and that obviously has effect in deadlocks. Make sure you are using the lowest Isolation level as much as possible. 5. Use update locks when applicable. This reduces Conversion Deadlocks. You can explicitly ask for an update lock by using the (UPDLOCK) hint in a SELECT statement. SELECT AccountBalance FROM Customers WITH (UPDLOCK) WHERE Customer = ? Bob? 6. Create clustered index to distribute inserts across the entire table. This ensures that the inserts are not happening on aht last page ONLY. 7. Avoid placing clustered index on columns that are frequently updated. Updates to clustered index key columns will require locks on the clustered index, data pages (to move the row), and all nonclustered indexes (since NC indexes point to rows via the clustered index key). 8. Capture error 1205 and make sure you resubmit the transaction as a general practice in your Application code. 9. Use bound connections where appropriate (see sp_getbindtoken in BOL). Any locks acquired by the bound connections are held as if they were acquired by the primary connection, and vice versa, and therefore do not block each other. This may be used to avoid distributed deadlocks, which occur when the circular blocking chain extends to resources outside of SQL Server. Special Considerations for Deadlocks when using MTS components: An MTS application runs its transactions with a default isolation level of Serializable when connected to SQL Server. MTS transactions have a timeout of 60 seconds. If a transaction cannot be completed ( a deadlock occured the MTS component would not know that it occured and if a deadlock did not occur and there was some kind of generic blocking going on ) within that time, the DTC assumes that a deadlock situation has occurred. In either case, your code must be ready to deal with deadlock errors when they arise. You can get the same effect ( as requesting an Update Lock ) with SQL Server by using a special type of ADO cursor. That is, if you open an ADO recordset on a table using a pessimistic server-side cursor, SQL Server will also acquire an update lock instead of a read lock. This shows how to eliminate deadlocks that are caused by lock conversion." |
![]() |
| Viewing: Codewalkers Forums > Other Technologies > Database Help > Deadlocks |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|
|
|
|