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 |
Suppose you want to implement the many-to-many relationships while designing tables. How would you do it?
How can you list all the table constraints in a database?
what are the steps you will take, if you are tasked with securing an sql server? : Sql server database administration
What are various aggregate functions that are available?
What is the difference between a "where" clause and a "having" clause?
select top 5 * from emp order by newid() my question is , how this query get executed?
Difference between connected and disconnected database in .net with sql server?
How many types of functions are there in sql server?
Can two different columns be merged into single column? Show practically?
here id col have primary key and identity id name 1 a 2 b 3 c 4 d delete 2nd row then o/p will be id name 1 a 3 c 4 d next inssert 2nd row and i want o/p will be id name 1 a 2 e 3 c 4 d
What is database replication?
Can we shrink data file in sql server?