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 (via UPDATE, DELETE, INSERT, ALTER TABLE, and so on). 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. If you need to set table-level locks explicitly, you may do so using the LOCK TABLE command.

solidDB^® allows both table-level locks and row-level locks.

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’s 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 were 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 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 setups using advanced replication, table-level locks are typically used with Maintenance Mode operations. For more details, see Introduction to Maintenance Mode in the solidDB^® Advanced Replication User Guide.

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/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 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 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.

▪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.

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 will fail 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. By default, solidDB^® lock timeout is set to 30 seconds. 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 User1’s query is completed (or times out). If the query of User1 is completed and the User2 query has not timed out yet, a lock is issued for the update transaction of User2. If User1 does not finish before the query of User2 times out, the server terminates User2’s statement.

The default lock timeout is controlled with the General.LockWaitTimeOut parameter. In advanced replication setups, you might also want to set the default lock time for table-level locks with the General.TableLockWaitTimeout parameter.

The default timeout can be overridden with the following transaction or section specific commands:

Note The LOCK TABLE WAIT mechanism does not apply to M-tables. For example, if in Session1 you lock table DEPARTMENT (LOCK TABLE DEPARTMENT IN EXCLUSIVE MODE, an attempt to insert values into the table in Session2 (INSERT INTO DEPARTMENT VALUES ...) will return error 10014 Resource is locked. immediately.

The wait mechanism in lock timeout applies only to pessimistic locking. There is no such thing as “waiting for an optimistic lock”. If someone else changed the data since the time that you read it, no amount of waiting will prevent a conflict that has already occurred. In fact, since optimistic concurrency methods do not place locks, there is no “optimistic lock” to wait on.

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 command.

If the locking transaction aborts or is rolled back, all locks are released, including LONG locks. You must unlock long locks explicitly using the UNLOCK command. 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, SERIALIZABLE isolation level does additional checks. It checks also 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 command like 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 solidDB^®’s implementation of holding shared locks until the end of transaction differs from some other servers. Some servers will release shared locks before the end of a transaction, if the transaction isolation level is low enough. Other database servers might also allow you to extend the duration of read/shared locks to ensure that within a single transaction, data looks the same every time you view it.

Also, in other servers, 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, your isolation level affects what you see, not just what other users can or cannot see because you have locked certain records.