To reduce the chance of a deadlock:
* Minimize the size of transaction and transaction times.
* Always access server objects in the same order each time in application.
* Avoid cursors, while loops, or process which requires user input while it is running.
* Reduce lock time in application.
* Use query hints to prevent locking if possible (NoLock, RowLock)
* Select deadlock victim by using SET DEADLOCK_PRIORITY.
SQL SERVER 2005 has new priority HIGH as well as numeric-priority.
SQL SERVER 2005 Syntax
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar } < numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 } Example: The following example sets the deadlock priority to NORMAL. SET DEADLOCK_PRIORITY NORMAL; GO
To help minimize deadlocks:
* Access objects in the same order.
* Avoid user interaction in transactions.
* Keep transactions short and in one batch.
* Use a lower isolation level.
* Use a row versioning-based isolation level.
* Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.
* Use snapshot isolation.
* Use bound connections.
* • Ensure the database design is properly normalized.
* • Have the application access database objects in the same order every time.
* • Keep transactions as short as possible.
* • During transactions, don't allow any user input.
* • Avoid cursors.
* • Consider reducing lock escalation by using the ROWLOCK or PAGLOCK hint.
* • Consider using the NOLOCK hint to prevent locking.
* • Use as low a level of isolation as possible for user connections.
Dead lock—events in profiler
* Deadlock graph
* · Lock: Deadlock
* · Lock: Deadlock Chain
* · RPC:Completed
* · SP:StmtCompleted
* · SQL:BatchCompleted
* · SQL:BatchStarting
* Minimize the size of transaction and transaction times.
* Always access server objects in the same order each time in application.
* Avoid cursors, while loops, or process which requires user input while it is running.
* Reduce lock time in application.
* Use query hints to prevent locking if possible (NoLock, RowLock)
* Select deadlock victim by using SET DEADLOCK_PRIORITY.
SQL SERVER 2005 has new priority HIGH as well as numeric-priority.
SQL SERVER 2005 Syntax
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar } < numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 } Example: The following example sets the deadlock priority to NORMAL. SET DEADLOCK_PRIORITY NORMAL; GO
To help minimize deadlocks:
* Access objects in the same order.
* Avoid user interaction in transactions.
* Keep transactions short and in one batch.
* Use a lower isolation level.
* Use a row versioning-based isolation level.
* Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.
* Use snapshot isolation.
* Use bound connections.
* • Ensure the database design is properly normalized.
* • Have the application access database objects in the same order every time.
* • Keep transactions as short as possible.
* • During transactions, don't allow any user input.
* • Avoid cursors.
* • Consider reducing lock escalation by using the ROWLOCK or PAGLOCK hint.
* • Consider using the NOLOCK hint to prevent locking.
* • Use as low a level of isolation as possible for user connections.
Dead lock—events in profiler
* Deadlock graph
* · Lock: Deadlock
* · Lock: Deadlock Chain
* · RPC:Completed
* · SP:StmtCompleted
* · SQL:BatchCompleted
* · SQL:BatchStarting
No comments:
Post a Comment