A deadlock occurs when there are two or more database transactions that are waiting on each other in order to release the locks that have been placed on resources, thereby creating a circular dependency, where none of the transactions are able to proceed.

For example, let’s say we have two transactions: Transaction A and Transaction B. Transaction A locks Row 1 and needs Row 2 in order to complete the transaction, while Transaction B locks Row 2 and needs Row 1 in order to complete its transaction. Neither transaction can move forward because each is waiting for the other to release their lock. Hence, they are stuck in a deadlock.

Databases handle deadlocks automatically by detecting them and picking one of the transactions to roll back, which breaks the cycle and allows the other transaction to proceed. The rolled back transaction returns an error and can be retried.

Some of the common reasons for deadlocks occurring include accessing resources that are in different order, transactions that run for a long time thereby having hold locks for extended periods, or indexes that are improper that end up causing more locks than what is necessary.

In order to reduce deadlocks, one should keep their transactions short; tables should be accessed in an order that is consistent across the application accessing the database, and using isolation levels that are appropriate.