Locks and lock modes

A lock is a mechanism for preventing two or more users from doing conflicting operations at the same time. Operations conflict if at least one of the operations involves updating the data (for example, by using UPDATE, DELETE, INSERT, or ALTER TABLE statements). If all the operations are read-only operations (such as SELECT), then there is no conflict.

solidDB does not allow users to specify row-level locks explicitly. There is no LOCK RECORD command; the server does all row-level locking for you. The server also does table-level locking for you. You can set table-level locks explicitly by using the LOCK TABLE command.

Row-level locks are placed on single records (rows) that the statements in a transaction define. The locks are placed as soon as any piece of the row is accessed.

Row-level locks are always implicit, solidDB sets the locks when necessary. You cannot lock or unlock row-level locks manually.

Table-level locks can be thought of as metadata locks; they prevent concurrent users from making schema changes (DDL operations) simultaneously or while records within the table are being changed.

For example, if you are updating a customer home phone number, you do not want another user to drop the telephone number column at the same time. If the other user was allowed to drop the telephone number column before you finished, your transaction would try to write an updated telephone number to a column that no longer exists, thus resulting in data corruption.

Most table-level locks are implicit; the server itself sets those locks when necessary. For example, when the server recognizes that a particular operation (such as an UPDATE statement without a WHERE clause) will affect every record in the table, the server itself can lock the entire table if it thinks that would be most efficient, and if no conflicting locks on the table already exist. Also, when you acquire a lock on a record in a table, you also implicitly acquire a lock (usually a shared lock) on the entire table. This prevents prevent one user from dropping the table or modifying the structure of the table while another user is updating data in the table.

You can also lock and unlock table-level locks manually by using the LOCK TABLE and UNLOCK TABLE commands.

Table-level locks are always pessimistic; the server puts a real lock on the table rather than just looking at versioning information. This is true even if the table is set to optimistic locking.

In configurations that use Advanced Replication, table-level locks are typically used with Maintenance Mode operations. For more details, see Upgrading schemas in a distributed system by using Maintenance Mode features.

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:

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.

An exclusive lock allows only one user or 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 can be placed on the data.

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 that the current user can 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, when 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 of their privileges are equal; locks placed by a DBA do not override locks that are placed by any other user.

All ways of executing statements that place locks are equal. 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 (SMA) or Linked Library Access (LLA), 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 can 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 their 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 they 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.

By default, a lock is held from the time it is acquired until the end of the transaction (completed with commit or rollback). If you try to get an exclusive lock on a record that another user has already locked (shared or exclusive), you cannot get a lock; instead, your transaction fails with an error. You can define whether solidDB should fail your transaction immediately or, before failing, wait and try again for a specified number of seconds. This is controlled with a lock timeout setting.

The lock timeout setting is the time in seconds that the engine waits for a lock to be released. If transactions tend to be very short, a brief wait allows you to continue activities that otherwise would have been blocked by locks.

When the lock timeout interval is reached, solidDB terminates the timed-out statement. For example, if User1 is querying a specific row in a table and User2 tries to update data in the same row, the update will not go through until the query for User1 is completed (or times out). If the query of User1 is completed and the User2 query has not timed out yet, a lock is implemented for the update transaction of User2. If User1 does not finish before the query of User2 times out, the server terminates the User2 statement.

solidDB allows you to prevent exclusive locks from being released when the locking transaction commits. These type of long exclusive locks are set with the LONG option in the LOCK TABLE statement, see LOCK TABLE.

If the locking transaction aborts or is rolled back, all locks are released, including LONG locks. You must unlock long locks explicitly by using the UNLOCK TABLE statement, see UNLOCK TABLE.

LONG duration locks are allowed only in EXCLUSIVE mode. LONG shared locks are not supported.

Update locks and exclusive locks are always held until the time that the transaction completes. Shared locks ("read locks") are also held until the end of the transaction but the transaction isolation level can affect how shared locks behave. For example, with the SERIALIZABLE isolation level, additional checks are made including that no new rows are added to the result set that the transaction should have seen. In other words, it prevents other users from inserting rows that would have qualified for the result set that is in the transaction.

If a SERIALIZABLE transaction has an update operation as follows:

UPDATE customers SET x = y WHERE area_code = 415;

solidDB does not allow other users to enter records with area_code=415 until the SERIALIZABLE transaction is committed.

Note that the solidDB implementation of holding shared locks until the end of transaction differs from some other database programs. Some programs release shared locks before the end of a transaction, if the transaction isolation level is low enough. Other programs might allow you to extend the duration of read or shared locks to ensure that within a single transaction, data looks the same every time you view it.

Also, in other programs, transaction isolation level might affect not only how long you lock a record, but also what you see. For example, on systems that allow both READ UNCOMMITTED (sometimes called "dirty read") and READ COMMITTED isolation levels, your isolation level affects what you see, not just what other users can or cannot see because you have locked certain records.