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 ?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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).