Locking In RDM SQL

RDM SQL provides two types of locks. A read (share) lock locks a table for read-only access. Any number of different connections can have a read lock on a table. During the time that a table is read locked, no modifications can occur on the table. A write (exclusive) lock locks a table for exclusive access by the connection which was granted the write lock. When one connection has been granted a write lock on a table, lock requests from other connections are queued and granted on a first-come, first-served basis.

Queued lock requests do not wait forever. When a lock request has waited for 10 seconds, it will be deleted from the queue and a timeout status code (eSQL_TIMEOUT) will be returned. The timeout value for a connection can be changed using the SET TIMEOUT statement as shown below.

set_timeout:
          SET TIMEOUT [TO | =] integer

A timeout value equal to -1 disables timeout checking so that lock calls will wait indefinitely. Timeouts should only be disabled when you are certain that there is no possibility of a deadlock situation arising (see deadlock discussion below). Any non-negative value specifies the number of seconds to wait for the requested table lock(s) to be granted. Setting the timeout to zero means that a lock request will return immediately if the lock cannot be granted.

Only table-level locking is provided in RDM SQL. Table locking is simple and is therefore very efficient but because an entire table is locked at a time, it works best in applications where there are a limited number of concurrent connections. If, however, you keep the duration of your transactions as short as possible good throughput is achievable for many applications.

Lock requests are automatically issued by RDM SQL when needed (implicit locking). For example, read locks are requested for each table that is accessed by a SELECT statement. When the locks on all of the needed tables have been granted then statement execution will proceed. If the SELECT statement was executed outside a transaction, the locks are held until the statement handle on which the SELECT is associated (i.e., the cursor) is closed which occurs automatically after the last row has been fetched. If the SELECT was executed after a transaction has started then the locks will be held until the transaction is either committed or rolled back.

A write-lock is requested by RDM SQL for the tables that are being modified by an INSERT, UPDATE, or DELETE statement. Write-locks are not freed until either a COMMIT or ROLLBACK operation is executed.

Table locks can be explicitly requested by either executing a START TRANSACTION statement that explicitly specifies the tables to be locked and the lock mode.