The concurrency control method and lock modes can be controlled with solidDB® SQL statements and configuration parameters.
Setting the concurrency (locking) mode to optimistic or pessimistic
The concurrency mode of disk-based tables can be set to optimistic or pessimistic for all tables or for specific tables. In-memory tables are always pessimistic.
By default D-tables use optimistic locking.
▪To set the concurrency mode for a specific table, use the ALTER TABLE <table_name> SET OPTIMISTIC|PESSIMISTIC command.
For example:
ALTER TABLE MyTable1 SET PESSIMISTIC; ALTER TABLE MyTable2 SET OPTIMISTIC;
▪To control the default concurrency mode for all tables, set the General.Pessimistic parameter to 'yes' or 'no' (default is 'no').
For example:
[General] Pessimistic=yes
The General.Pessimistic parameter takes effect only at the time that the server starts. If you edit the solid.ini file manually, the change will not be visible until the server restarts.
Since the value of the General.Pessimistic can change, the concurrency control for a table may change. It is possible for a table to use optimistic concurrency control during one instance of the server and pessimistic during another.
When you set the General.Pessimistic parameter to 'yes', the server defaults to pessimistic locking for
▪any new tables that are created, and
▪for any existing tables whose concurrency control method has never been set explicitly with the ALTER TABLE command.
If you set a table's locking mode by using the ALTER TABLE command, the ALTER TABLE command takes precedence.
Setting mixed concurrency control
With D-tables, you can use mixed concurrency control methods. Mixed concurrency control is available by setting individual tables to optimistic or pessimistic.
By default, solidDB® uses optimistic concurrency control for D-tables. M-tables are always pessimistic.
Mixed concurrency control is a combination of row-level pessimistic locking and optimistic concurrency control. By turning on row-level locking table-by-table, you can specify that a single transaction use both concurrency control methods simultaneously. This can be set for both read-only and read-write transactions.
To set individual tables for optimistic or pessimistic concurrency, use the followingcommand:
ALTER TABLE base_table_name SET {OPTIMISTIC | PESSIMISTIC}
Note When using solidDB® with advanced replication, pessimistic table-level locks in shared mode are possible with tables that are synchronized. This functionality provides users with the option to run some operations for synchronization in pessimistic mode even with optimistic tables. For example, when a REFRESH is executed in pessimistic mode in a replica, solidDB® locks all tables in shared mode; later, if necessary, the server can "promote" these locks to exclusive table locks. This is done in a few synchronization statements when optional keyword PESSIMISTIC is specified. Read operations do not use any locks.
Reading the concurrency mode
The method for reading the concurrency mode of a table depends on how the concurrency mode has been set.
▪If the concurrency mode of the table has been set explicitly with the ALTER TABLE command, the concurrency mode is recorded in a SYS_TABLEMODES system table.
Check the values in the SYS_TABLEMODES with the following command:
SELECT SYS_TABLEMODES.ID, SYS_TABLEMODES.MODE, SYS_TABLES.TABLE_NAME FROM SYS_TABLEMODES, SYS_TABLES WHERE SYS_TABLEMODES.ID = SYS_TABLES.ID AND SYS_TABLES.TABLE_NAME = ’<table_name>’;
For example:
SELECT SYS_TABLEMODES.ID, SYS_TABLEMODES.MODE, SYS_TABLES.TABLE_NAME FROM SYS_TABLEMODES, SYS_TABLES WHERE SYS_TABLEMODES.ID = SYS_TABLES.ID AND SYS_TABLES.TABLE_NAME = ’TESTTABLE2’;
ID MODE TABLE_NAME -- ---- ---------- 10002 PESSIMISTIC TESTTABLE2
1 rows fetched.
If the concurrency mode has not been set using the ALTER TABLE command, the SYS_TABLEMODES system table does not contain information about the concurrency mode of the table.
▪If the concurrency mode of the table has not been set with the ALTER TABLE command, check the setting of the General.Pessimistic parameter with the following command:
If the value in the solid.ini file has not been changed since the server started, and if the value has not been overridden by an ADMIN COMMAND, you can also check the parameter setting in the solid.ini file.
Setting lock timeout
The lock timeout setting can be modified with the SET LOCK TIMEOUT and LOCK TABLE WAIT commands. By default, lock timeout is set to 30 seconds.
▪Use LOCK TABLE WAIT <timeout_in_seconds> to set timeout for table-level locks.
Note The LOCK TABLE WAIT command is effective on disk-based tables only.
▪Use SET LOCK TIMEOUT <timeout_in_seconds> to set the lock timeout for both table-level and row-level locks in a session.
Note SET LOCK TIMEOUT does not change the timeout for those tables for which the table-level timeout has been set with LOCK TABLE WAIT.
By default, the granularity for the timeout is in seconds. The lock timeout can be set at millisecond granularity by adding "MS" after the value, for example:
LOCK TABLE emp,dept IN SHARED MODE WAIT 10MS;
or
SET LOCK TIMEOUT 10MS;
Without the "MS", the lock timeout is in seconds.
Note The maximum timeout is 1000 seconds (a little over 15 minutes). The server will not accept a longer value.
Setting lock timeout for optimistic tables
When you use SELECT FOR UPDATE, the selected rows become locked even if the locking mode of the table is optimistic. If the statement is successful, you can execute consecutive updates and deletes on those rows without a fear that they would fail on a conflict.
Statements that are competing in concurrent transactions for the write access to the rows (such as SELECT FOR UPDATE, UPDATE or DELETE ) will fail immediately if the lock cannot be granted. That corresponds to zero timeout. You can introduce lock waits by setting the optimistic lock timeout to a non-zero value: