Concurrent Database Access
Concurrent database access refers to the situation where the database is being accessed from more than one connection (user) at a time. Without the database system exerting some control over what gets updated by who and when, all kinds of data integrity and consistency problems can arise. This can be illustrated with the simple example given below in Table 12 which shows what can happen when the database system does not provide some kind of concurrent access protection.
| Time | Connection 1 | Connection 2 |
|---|---|---|
| T1 | select price from book where bookid = "cbronte03"; PRICE 12500.00 |
|
| T2 | select price from book where bookid = "cbronte03"; PRICE 12500.00 |
|
| T3 | update book set price=14500.00 where bookid "cbronte03"; | |
| T4 | update book set price=10500.00 where bookid "cbronte03"; | |
| T5 | select price from book where bookid = "cbronte03"; PRICE 10500.00 |
select price from book where bookid = "cbronte03"; PRICE 10500.00 |
At time T1 connection 1 executes a SELECT that returns the price of the books as 12,500. At time T2 connection 2 executes the same SELECT and gets the same result. Then at time T3 connection 2 issues an UPDATE changing the price to 14,500 while at time T4 connection one changes the price to 10,500 overwriting the change just made by connection 2. At time T5 both connections issue the same SELECT with connection 1 getting the expected result while the user on connection 2 wonders if there is something wrong with her keyboard!
One of the most common ways for a DBMS to prevent these kinds of problems is to use locking in order to prevent other connections from accessing the data being updated. So, in the above example, if at time T1 connection 1 places a lock on the book table then the lock request issued by connection 2 at T2 will wait until connection 1 releases the lock which will occur when the update completes and the lock is freed. Then connection 2's lock request will be granted and the SELECT statement will now return the value of price as 10,500 and connection 2's update can proceed with no anomalies.
| Time | Connection 1 | Connection 2 |
|---|---|---|
| T1 | Request book table lock | |
| T2 | Lock granted | Request book table lock |
| T3 | select price from book where bookid = "cbronte03"; PRICE 12500.00 |
|
| T4 | update book set price=10500.00 where bookid "cbronte03"; | |
| T5 | Free book table lock | Lock granted |
| T6 | select price from book where bookid = "cbronte03"; PRICE 10500.00 |
|
| T7 | update book set price=14500.00 where bookid "cbronte03"; | |
| T8 | Free book table lock; |
Locking In RaimaDB SQL
RaimaDB 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 (errTIMEOUT) will be returned. The timeout value for a connection can be changed using the SET TIMEOUT statement as shown below or through a call to function rdm_sqlSetTimeout.
set_timeout_stmt: 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 RaimaDB 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 most embedded systems applications.
Lock requests are automatically issued by RaimaDB 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 RaimaDB 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 LOCK TABLE statement or through a call to the RaimaDB SQL API function rdm_sqlLockTables. The syntax for the LOCK TABLE statement is shown below.
If neither READ nor WRITE is specified, then READ is the default outside of a transaction and WRITE is the default inside a transaction. If a read only transaction (see below) is active then the lock request will return an error. Either all lock requests will succeed or none will. I.e, this is an either all or none request which can be used to prevent a deadlock situation in which one process holds a lock on table A while requesting a lock on table B while a second process is holding a lock on table B while requesting a lock on table A.
The system will switch into explicit locking mode on execution of the first LOCK TABLE statement (rdm_sqlLockTables call). In this mode, all tables that are accessed by any subsequent SQL statements must be explicitly locked. If not, SQL will return an errNOTLOCKED status. Note that the values of foreign key columns are retrieved from the referenced row in the primary key table (RaimaDB SQL does not actually store them in the foreign key table). Hence, both the foreign and primary key tables must be explicitly locked when accessing foreign key column values.
This statement will free the READ lock on table tabname or will free all read locks. This can only be executed outside of a transaction. The locks held within a transaction can only be freed through a transaction COMMIT or ROLLBACK.
The SQL system automatically reverts to implicit locking mode when all table locks have been freed.
Read Only Transactions
A read only transaction allows a transaction consistent snapshot of the database to be queried without the need to place locks on the accessed tables. A read only transaction can be explicitly started by executing the following statement.
start_stmt:
START TRANSACTION [transid_name] [transact_item [, transact_item]...]transact_item:
{READ {ONLY | WRITE} | UPDATE} [ON table_list]
| isolation level iso_leveltable_list:
database_name.]table_name [, [database_name.]table_name]...iso_level:
READ UNCOMMITTED
| READ COMMITED
| REPEATABLE READ
| SERIALIZABLE
Once a read only transaction has started, database modifications that have been committed by other connections will not be visible. Read only transactions are terminated by executing either a COMMIT or a ROLLBACK statement. If a read only transaction is active when a SELECT statement executes, no lock requests will be issued.
By default, RaimaDB SQL automatically requests read locks on the tables that are accessed by a SELECT statement. However, an option is available that will cause SQL to automatically initiate a read only transaction instead of requesting locks. The read only transaction will be terminated when the SELECT statement completes (i.e., cursor is closed). The mode is controlled using the statement given in the following syntax.
read_only_trmode_stmt:
SET READ ONLY {TRANS | TRANSACTION} MODE [TO | =] {AUTO | MANUAL}
When this mode is set to MANUAL (default), SQL will issue lock requests on the tables to be accessed by a SELECT statement. When this mode is set to AUTO, SQL will executed each SELECT statement within its own read only transaction.
You can also explicitly indicate that a SELECT is to use a read only transaction instead of locks by adding the FOR READ ONLY clause to the end of your SELECT statement.
Read only transactions are very useful in concurrent database access applications because they do not block access to the database from other connections. However, these do not come free. Long running read only transactions will eventually seriously degrade system performance. Therefore, it is best that read only transactions be kept as short as possible.
Modification Stored Procedures
RaimaDB SQL automatically places write locks on the tables that are being modified in an INSERT, UPDATE, or DELETE statement. If you encapsulate all of your database modifications in stored procedures that includes an opening START TRANSACTION and a closing COMMIT statement—a transactional stored procedure—then the system will issue a grouped lock request at the start of execution of the stored procedure to acquire all of the locks on all of the tables involved in the modification. The EXECUTE statement (or call to rdm_stmtExecuteProcedure) will return status errTIMEOUT when one or more of the requested locks could not be acquired within the timeout window.
Transactional stored procedures can modify only one database at a time. If you use more than one database at a time, then the modifications for each must be made in separate transactions.
Avoiding Deadlock
A deadlock (also known as deadly embrace) is an egregious situation that can arise in any system that involves concurrent access to shared data from multiple processes. In its simplest form, process 1 holds an exclusive lock on data item A and is requesting a lock on data item B while at the same time process 2 holds an exclusive lock on data item B while requesting a lock on data item A. As you can easily see, both processes will wait forever unless one or the other releases the lock it holds. Of course, much more complex deadlock scenarios exist that involve multiple processes.
The primary application programming technique available in RaimaDB that can be used to avoid deadlock is the timeout. A lock request will fail if the lock is not granted within the time duration specified by the connection's timeout value. The default timeout is set to 10 seconds. As noted above, this value can be changed using either the SET TIMEOUT statement or through a call to the rdm_sqlSetTimeout function.
While timeouts can be used to avoid deadlock, a related condition known as a livelock can still occur in which, in the example above both of process 1's and process 2's lock requests timeout at the same time, causing each to free the other lock as well and then restart their respective transactions with the timing of the operations such that the same situation continues to repeat itself.
Both livelock and deadlock can be avoided by including in a single request locks on all of the tables (i.e., a grouped lock request) that will potentially be modified by a transaction. As noted in the last section, a transactional stored procedure performs a grouped lock request for all needed locks at the beginning of the transaction, before any modification statements have executed. The table locks included in grouped lock requests made by RaimaDB SQL are always specified in the same order. While a timeout can still certainly occur, neither a deadlock nor livelock situation will occur.
However, if you are issuing dynamic SQL transactions that include multiple database modification statements, you need to explicitly lock all tables that can be modified in the transaction immediately following the START TRANSACTION statement. While not strictly necessary, it is also best to specify the tables in the LOCK TABLE statement in the order in which they are declared in your DDL specification (this is the order in which SQL automatically issues the grouped lock request when a transactional stored procedure is executed). If you do not explicitly lock the tables in a dynamic SQL transaction, SQL will automatically make the lock requests for each statement. If a timeout occurs during execution of a database modification statement, the correct response is to roll back the transaction and then restart it.
It is highly recommended that you encapsulate all of your transactions in transactional stored procedures in order to ensure that deadlock and livelock situations are avoided. It is also recommended that you use read only transactions as much as possible as these will not block other updating processes. Both regular and read only transactions should execute in as short a time frame as possible.
Concurrent Database Access Use in Static SQL Applications
These statements are only available through dynamic SQL—they cannot be included in stored procedures. Explicit locking within a static SQL application that uses only pre-compiled stored procedures must be done through calls to the RaimaDB SQL API locking functions as shown in the table below. The Using SQL in an Application Program section will describe in detail the use of these functions in an RaimaDB SQL C application program.