The concurrency control method 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 (D-tables) can be set to optimistic or pessimistic for all tables or for specific tables. In-memory tables (M-tables) are always pessimistic.
By default D-tables use optimistic locking.
▪ To set the concurrency mode for a specific table, use the ALTER TABLE statement, see ALTER TABLE.
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 in solid.ini, see General section.
For example:
[General] Pessimistic=yes
Since the value of the General.Pessimistic parameter can change, the concurrency control for a table can change. It is possible for a table to use optimistic concurrency control during one instance of the server and pessimistic during another.
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.
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.
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 database, solidDB locks all tables in shared mode. If necessary, the server can "promote" these locks to exclusive table locks at a later time. This is done with 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.
The following query shows how to check the values in the SYS_TABLEMODES:
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 of the table has not been set with the ALTER TABLE command, check the setting of the General.Pessimistic parameter by using 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.