With Firebird, locking is governed by the relative ages of transactions and the records managed by Firebird’s versioning engine. All locking applies at the row level, except when a transaction is operating in SNAPSHOT TABLE STABILITY isolation or with a table reservation restriction that blocks write access.
The timing of the lock on the row in normal read-write activity is optimistic—no locking is in force on any row until the moment it is actually required. Until an update of the row is posted to the server, the row is free to be “won” by any read-write transaction.
Pessimistic, or pre-emptive, locking can be applied to sets of rows or to entire tables. The table-locking options have already been introduced (see the sections “Table Reservation” and “SNAPSHOT TABLE STABILITY (Consistency)”).
Row-level and set-level pessimistic locking are options where there is an application requirement to reserve a row or a small set in advance of actually posting an update or deletion.
The capability to do explicit pessimistic row locking was added to Firebird’s SQL SELECT statement syntax at version 1.5. It is restricted to “outer-level” SELECT statements that return output sets or define cursors. In cannot be applied to subqueries.
The abbreviated syntax for acquiring explicit pessimistic row locks is
FOR UPDATE, which is not a locking instruction, requests that the output set be delivered to the client one row at a time, rather than as a batch. The optional phrase WITH LOCK is the element that forces the pre-emptive lock on a row as soon as the server outputs it from the server. Rows waiting to be output are not locked.
The traditional way of achieving a pessimistic row lock with Firebird is the dummyupdate. It is a hack that takes advantage of record versioning. Simply put, the client posts an update statement for the row that does not update anything —it just sets a column to its current value, causing the server to create a new record version, and thus blocks other transactions from acquiring the row for updating or deletion.
A lock conflict is triggered when concurrent transactions try to update or delete the same row during a time when their views of database state overlap. Lock conflicts are the planned outcome of Firebird’s transaction isolation and record versioning strategy, protecting volatile data from uncontrolled overwriting by parallel operations on the same data.
The strategy works so well that there are really only two conditions that can cause lock conflicts:
Suppose ThisTransaction posts a change to a row. Another transaction comes along and requests to change or delete the same row. In SNAPSHOT isolation with WAIT, the other transaction will keep waiting until ThisTransaction completes with either commit or rollback.
If ThisTransaction commits, then the other transaction will fail with an update conflict. The client that started the other transaction should have an exception handler that either rolls the transaction back and starts a new one to resubmit the request, or simply commits the transaction and exits.
Calling COMMIT in a lock conflict exception handler is not recommended, since it breaks the atomicity of the transaction —some work will complete, some will not, and it will be impossible to predict database state afterward. Rollback is almost always the right response to a lock conflict.
Unfortunately, Firebird tends to generalize all locking exceptions and report them as “deadlocks.” The normal case just described is not a deadlock.
What Is a Deadlock?
Deadlock is just a nickname, borrowed from the sport of wrestling, for the condition where two transactions are contending to update rows in overlapping sets and one transaction does not take any precedence over the other.
For example, ThisTransaction has an update pending on Row X and wants to update Row Y, while the other transaction has an update pending on Row Y and wants to update Row X, and both transactions are in WAIT mode. As in wrestling, the deadlock can be resolved only if one contender withdraws its hold. One transaction must roll back and let the other commit its changes.
Firebird provides for the application to resolve the deadlock, by scanning for deadlocks every few seconds. It will arbitrarily pick one transaction from the deadlock and deliver a deadlock exception.
Developers should not dread deadlock messages. On the contrary, they are the essence of isolating the work of multiple users in transaction contexts. You should anticipate them and handle them effectively in your client application.
When ThisTransaction is selected to resolve the deadlock, the application’s exception handler should roll it back to allow the other transaction to resume and complete its work. The alternative —committing ThisTransaction in the exception handler—is not recommended, since ThisTransaction becomes non-atomic and the other transaction will fail with a lock conflict.
In rare cases, more than two transactions could be deadlocked in contention for the same overlapping sets. It is sometimes called the deadly embrace. The deadlock scan will fail one transaction (ThisTransaction), handing it over to the client for exception resolution, as before. However, this time, even if the client rolls back This Transaction, those other transactions are still deadlocked out there.
The client might start a new transaction and retry, but the other contenders are still deadlocked, waiting for the next deadlock scan to extricate another of the contenders with a deadlock exception. As long as the retrying application keeps retrying with a WAIT transaction, it is just going to wait for some indefinite time for the other transactions to resolve the deadly embrace. For those futile retries, the transaction is said to be in a livelock.
In short, it is important in the first place to avoid transaction contexts that make it possible for a deadly embrace to occur. As added protection, exception handlers should be made capable of quickly dealing with deadlocks and ensuring that problem transactions finish cleanly and without delay.
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.