0

What is the play between lock_timeout and deadlock_timeout ?

What is the reason for a lock_timeout to occur not leading to a deadlock timeout. I am not able to think of scenarios from database perspective ?

3

The difference between a live lock (handled by lock_timeout) and a deadlock is that the latter is guaranteed never to end, because two (or more) sessions are waiting for each other.

A live lock can always end – it will end as soon as the session that locks the resource ends the database transaction. With a deadlock, that is not possible, because all involved transactions are blocked and cannot continue.

By default, the database resolves deadlocks by killing one of the involved transactions, but it does not interrupt live locks.

1

If one database session needs to wait for another session because it needs to have the same lock for example, this is only a lock waiting issue: you use lock_timeout.

Deadlock is a very special case of waiting lock issue where several sessions wait on each other and it cannot work: the database decides to kill one of the involved transaction to fix this so that at least one database session is not blocked any more. the parameter deadlock_timeout is

the amount of time, in milliseconds, to wait on a lock before checking to see if there is a deadlock condition.

Deadlock is a special case of locking situation that occurs less frequently (generaly caused by application code taking locks on same database objects but not in the same sequence).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.