PESSIMISTIC vs. OPTIMISTIC concurrency control
* Pessimistic concurrency control (or pessimistic locking) is called "pessimistic" because the system assumes the worst — it assumes that two or more users will want to update the same record at the same time, and then prevents that possibility by locking the record, no matter how unlikely conflicts actually are.
The locks are placed as soon as any piece of the row is accessed, making it impossible for two or more users to update the row at the same time. Depending on the lock mode (shared, exclusive, or update), other users might be able to read the data even though a lock has been placed. For more details on the lock modes, see Lock modes: shared, exclusive, and update.
* Optimistic concurrency control (or optimistic locking) assumes that although conflicts are possible, they will be very rare. Instead of locking every record every time that it is used, the system merely looks for indications that two users actually did try to update the same record at the same time. If that evidence is found, then one user's updates are discarded and the user is informed.
For example, if User1 updates a record and User2 only wants to read it, then User2 simply reads whatever data is on the disk and then proceeds, without checking whether the data is locked. User2 might see slightly out-of-date information if User1 has read the data and updated it, but has not yet committed the transaction. Optimistic locking is available on disk-based tables (D-tables) only.
* The solidDB implementation of optimistic concurrency control uses multiversioning.
* Each time that the server reads a record to try to update it, the server makes a copy of the version number of the record and stores that copy for later reference.
* When it is time to commit the transaction, the server compares the original version number that it read against the version number of the currently committed data.
* If the version numbers are the same, then no one else changed the record and the system can write the updated value.
* If the originally read value and the current value on the disk are not the same, then someone has changed the data since it was read, and the current operation is probably out-of-date. Thus the system discards the version of the data, aborts the transaction, and returns an error message.
* The step of checking the version numbers is called validation. The validation can be performed at the commit time (normal validation) or at the time of writing each statement (early validation). In solidDB, early validation is the default method (General.TransactionEarlyValidate=yes).
Each time a record is updated, the version number is updated as well.
Depending on the lock mode, when one user has a lock on a record, the lock prevents other users from changing or even reading that record. There are three lock modes:
* SHARED
* Row-level shared locks allow multiple users to read data, but do not allow any users to change that data.
* Table-level shared locks allow multiple users to perform read and write operations on the table, but do not allow any users to perform DDL operations.
* Multiple users can hold shared locks simultaneously.
* EXCLUSIVE
* An exclusive lock allows only one user/connection to update a particular piece of data (insert, update, and delete). When one user has an exclusive lock on a row or table, no other lock of any type may be placed on it.
* UPDATE
* Update locks are always row-level locks. When a user accesses a row with the SELECT... FOR UPDATE statement, the row is locked with an update mode lock. This means that no other user can read or update the row and ensures the current user can later update the row.
* Update locks are similar to exclusive locks. The main difference between the two is that you can acquire an update lock when another user already has a shared lock on the same record. This lets the holder of the update lock read data without excluding other users. However, once the holder of the update lock changes the data, the update lock is converted into an exclusive lock.
* Also, update locks are asymmetric with respect to shared locks. You can acquire an update lock on a record that already has a shared lock, but you cannot acquire a shared lock on a record that already has an update lock. Because an update lock prevents subsequent read locks, it is easier to convert the update lock to an exclusive lock.
Shared and exclusive locks cannot be mixed. If User1 has an exclusive lock on a record, User2 cannot get a shared lock or an exclusive lock on that same record. All locks within a particular category (such as shared locks) are equal.
* All users regardless the user privileges are equal: locks placed by a DBA are no more and no less strong than locks placed by any other user.
* All ways of executing statements that place locks are equal: the lock can be executed as part of ,
* It does not matter whether the lock was executed as part of an interactively typed statement, called from a compiled remote application, or called from within the local application when using solidDB® with shared memory access or linked library access, or if the lock was placed as a result of a statement inside a stored procedure or trigger.
Some locks can be escalated. For example, if you are using a scroll cursor and you acquire a shared lock on a record, and then later within that same transaction you update that record, your shared lock may be upgraded to an exclusive lock. Getting an exclusive lock is only possible if there are no other locks (shared or exclusive) on the table; if you and another user both have shared locks on the same record, then the server cannot upgrade your shared lock to an exclusive lock until the other user drops her shared lock.
The EXCLUSIVE and SHARED lock modes are used for both pessimistic and optimistic tables. By default, optimistic and pessimistic tables are locked in shared mode; unless you are altering the table, the locks on tables are usually shared locks.
When you execute an ALTER TABLE operation, you get a shared lock on that table. That allows other users to continue to read data from the table, but prevents them from making changes to the table. If other users want to do DDL operations (such as ALTER TABLE) on the same table at the same time, they will either have to wait or will get an error message.
Also, in advanced replication setups, some solidDB statements (such as REFRESH or MESSAGE EXECUTE) that can be run with the optional PESSIMISTIC keyword, use EXCLUSIVE table-level locks even when the tables are optimistic.