What are deadlocks in SQL?

[Caila]

Hello friends, I have got project on deadlocks in SQL. I am not aware of this topic. But I want to know about as I am having a project on it which is very much important for me as I can score a marks in it which will boost my percentage level. So, anybody having any information or having any clue or idea about this topic then please tell me as soon as possible.

[Vandam]

Unfortunately, the use of any locking scheme to support concurrent SQL transactions lead to a problem called a deadlock. A deadlock is a situation in which two users (or transactions) have locks on separate objects, and each user is waiting for a lock on the other object. It usually occurs in a multiuser environment.

[Shreevats]

For example, Transaction A has locked the Distributor table and wants to lock the Products table. Transaction B has locked the Products table and wants to lock the Distributor table. This is resulting in a deadlock, as both the transactions are waiting for the other transaction to release the table. This will not happen and both the transaction will be left waiting.

[Steyn]

Over the last several years, most of the major commercial DBMS systems have moved beyond page-level locking to row-level locking. Row-level locking allows two concurrent transactions that access two different rows of a table to proceed in parallel, even if the two rows fall in the same disk block. Other transactions are prevented from accessing the locked pages but may access other pages of data.