How can your resolve deadlocks?
Answers were Sorted based on User's Feedback
Answer / ilgian
Deadlocks occour when two or more processes place a lock on
the same resources and each process waits for the others to
release the lock.
The options to avoid deadlocks are basically the following:
1) always update data in the same order: if process A
updates table t1 and then table t2 and process B updates
table t2 and then table t1 deadlocks can occour. Choose an
order and use it everywhere in your code.
2) issue the commands again when you catch the deadlock
3) always update data before data selection: no other
process will place a lock on the modified records
4) identify the processes that can run with lower deadlock
priority and issue SET DEADLOCK_PRIORITY LOW before these
statements: this will tell SQLServer that the process is a
good candidate for killing in case of deadlock, having
the "important" processes free to run without kills
Is This Answer Correct ? | 12 Yes | 0 No |
Answer / xavier rajan, photon infotech
A deadlock occurs when two or more
processes try to access a resource that another process has
a lock on. Microsoft SQL Server automatically
detects these deadlocks, terminates one of the processes,
and rolls back any changes made by that process.
The process chosen for termination will be the one with the
least amount of active time on the server. In
most cases, you can simply reissue the statements that were
rolled back and should have no problems.
Is This Answer Correct ? | 6 Yes | 1 No |
Answer / darshan
Hi
1)
First find out which process block the other process from
activity monitor or sp_who2. Then kill that process.
2)
If update locks are more then it will make deadlock. So
reduce that.
Is This Answer Correct ? | 7 Yes | 2 No |
Answer / sasikala
one way to kill the process which is responsible for dead lock. after this the process can proceed without any problem. after a process completed , the killed process should be created and executed as if nothing has happened. but the killed process might have changed in an data base or some records. it must have changed as before to maintain the consistency.
Is This Answer Correct ? | 0 Yes | 0 No |
Is null vs coalesce?
How do I find the sql server database version?
Ek lifafa 10 noto se bhara hai usme 2 or 5 ke note nahi hai aur usme total 50Rs hai, to batao lifafe me kon se not kitne hia it’s a challenge thank’s
Do you know what are the ways available in sql server to execute sql statements?
What is pivot and unpivot?
Show Practically Sql Server Views are updatable?
Difference Between ORDER BY Clause and GROUP BY Clause in SQL?
Can I save my report as html, excel or word? : sql server management studio
what is bit datatype and what's the information that can be stored inside a bit column? : Sql server database administration
What is index?
What is a NOLOCK?
what is the different types of backups available in sql server? : Sql server database administration