First, what is transaction?
Transaction is a device for grouping together multiple SQL statements such that either all or none of the statements succeed (a property known as atomicity).
Database servers use locks for simultaneous access to data resources. When some portion of the database is locked, any other users wishing to modify (or possibly read) that data must wait until the lock has been released. There are several locking strategies, but this post doesn't cover it. There are also a number of different strategies that you may employ when deciding how to lock a resource (table, page or row locks).
In the next example, I'll assume that database server employs table lock. So, what is deadlock?
Database servers use locks for simultaneous access to data resources. When some portion of the database is locked, any other users wishing to modify (or possibly read) that data must wait until the lock has been released. There are several locking strategies, but this post doesn't cover it. There are also a number of different strategies that you may employ when deciding how to lock a resource (table, page or row locks).
In the next example, I'll assume that database server employs table lock. So, what is deadlock?
A deadlock occurs when two different transactions are waiting for resources that other transaction currently holds. Take a look at image.
Transaction A and transaction B consists of two SQL statements.
For example, transaction A has updated the Table-1 (green arrow) and is waiting for a write lock on the Table-2(second SQL-statement in transaction A, red arrow), while transaction B has updated th Table-2 (green arrow) and is waiting for write lock on the Table-1 (second SQL-statement in transaction B, red arrow).
So, transactions will wait forever for the other transaction to finish and free up the needed resource.
Database server must always be on the lookout for these situations. When deadlock is detected, one of the transactions is chosen (either arbitrarily or by some criteria) to be rolled back so that the other transaction may proceed. Most of the time, the terminated transaction ca be restarted and will succeed without encountering antoher deadlock situation.
Hopefully, you got the answer for the post's question.
Have a nice day
For example, transaction A has updated the Table-1 (green arrow) and is waiting for a write lock on the Table-2(second SQL-statement in transaction A, red arrow), while transaction B has updated th Table-2 (green arrow) and is waiting for write lock on the Table-1 (second SQL-statement in transaction B, red arrow).
So, transactions will wait forever for the other transaction to finish and free up the needed resource.
Database server must always be on the lookout for these situations. When deadlock is detected, one of the transactions is chosen (either arbitrarily or by some criteria) to be rolled back so that the other transaction may proceed. Most of the time, the terminated transaction ca be restarted and will succeed without encountering antoher deadlock situation.
Hopefully, you got the answer for the post's question.
Have a nice day
No comments:
Post a Comment