Deadlocks and Locks

Deadlock

1. A deadlock occurs when two or more threads of control are blocked, each waiting on a resource held by the other thread.

2. A deadlock occurs when two or more session are waiting for data locked by each other, resulting in all the sessions being blocked. Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock. Typically, deadlocks are caused by poorly implemented locking in application code

e.g., If session 1 is locking row 1, session 2 locks row 2, then session 1 attempts to lock row 2 (which will block since session 2 has the lock on that row),and then session 2 attempts to lock row 1 (which will block since session 1 has the lock on that row), then session 1 is waiting for session 2, and session 2 is waiting on session 1, which of course will never be resolved.

Lock

1. When one thread of control wants to obtain access to an object, it requests a lock for that object.

2. When a transaction updates a row, it puts a lock so that no one can update the same row until it commits. When another transaction issues an update to the same row, it waits until the first one either commits or rolls back. After the first transaction performs a commit or rollback, the update by the second transaction is executed immediately, since the lock placed by the first transaction is now gone.tt