Concurrency Controls

The transactional database engine and its automatic recovery functions handle the physical integrity of your database. Pervasive PSQL provides logical data integrity using the transaction and record-locking capabilities of the transactional database engine. Pervasive PSQL, in conjunction with the transactional database engine, provides the following types of concurrency controls:

•

Isolation levels for transactions

•

Explicit locks

•

Passive control

Transaction Processing

Transaction processing lets you identify a set of logically related database modifications, either within a single table or across multiple tables, and require them to be completed as a unit. Transaction processing involves two important concepts:

•

A logical unit of work, or transaction, is a set of discrete operations that must be treated as a single operation to ensure database integrity. If you make a mistake or encounter a problem during a transaction, you can issue a ROLLBACK WORK statement to undo the changes you have already made.

For example, the Registrar might credit a student account with an amount paid in one operation, then update the amount owed in a second operation. By grouping these operations together you ensure the student’s finances are accurate.

•

A locking unit is the amount of data from which other tasks are blocked until your transaction is complete. (A task is a Pervasive PSQL session.) Locking prevents other tasks from changing the data you are trying to change. If other tasks can also change the data, Pervasive PSQL cannot roll back work to a previously consistent state. Thus, within a transaction, only one task may access a given locking unit at a time. However, multiple cursors that belong to the same task can access the locking unit at the same time.

The START TRANSACTION statement begins a transaction. When you have issued all the statements you want to complete during the transaction, issue a COMMIT WORK statement to end the transaction. The COMMIT WORK statement saves all your changes, making them permanent.

Note: START TRANSACTION and COMMIT WORK are only supported in stored procedures. For more information on these two SQL statements, see SQL Engine Reference.

If an error occurs in one of the operations, you can roll back the transaction and then retry it again after correcting the error. For example, if you need to make related updates to several tables, but one of the updates is unsuccessful, you can roll back the updates you have already made so the data is not inconsistent.

Pervasive PSQL automatically performs the rollback operation if two tasks are sharing a login session and the task that originated the session logs out before the second task completes its transition.

Starting and Ending Transactions

To begin a transaction, issue a START TRANSACTION statement in a stored procedure. After issuing all the statements you want to complete during the transaction, issue a COMMIT WORK statement to save all your changes and end the transaction.

START TRANSACTION;

UPDATE Billing B

SET Amount_Owed = Amount_Owed - Amount_Paid

WHERE Student_ID IN

(SELECT DISTINCT E.Student_ID

FROM Enrolls E, Billing B

WHERE E.Student_ID = B.Student_ID);

COMMIT WORK;

For more information about the START TRANSACTION statement, refer to SQL Engine Reference.

Using Savepoints to Nest Transactions

In a SQL transaction, you can define additional markers called savepoints. Using savepoints, you can undo changes after a savepoint in a transaction and continue with additional changes before requesting the final commit or abort of the entire transaction.

To begin a transaction, use the START TRANSACTION statement. The transaction remains active until you issue a ROLLBACK or COMMIT WORK statement.

To establish a savepoint, use the SAVEPOINT statement.

SAVEPOINT SP1;

To rollback to a savepoint, use the ROLLBACK TO SAVEPOINT statement.

ROLLBACK TO SAVEPOINT SP1;

The savepoint name must specify a currently active savepoint in the current SQL transaction. Any changes made after establishing this savepoint are cancelled.

To delete a savepoint, use the RELEASE SAVEPOINT statement.

RELEASE SAVEPOINT SP1;

You can only use this statement if a SQL transaction is active.

If you issue a COMMIT WORK statement, all savepoints defined by the current SQL transaction are destroyed, and your transaction is committed.

Note: Do not confuse ROLLBACK TO SAVEPOINT with ROLLBACK WORK. The former cancels work only to the indicated savepoint, while the latter cancels the entire outermost transaction and all savepoints established within it.

Savepoints provide a way to nest your transactions, thereby allowing the application to preserve the previous work in the transaction while it waits for a sequence of statements to complete successfully. As an example, you can use a WHILE loop for this purpose. You can set a savepoint before beginning a sequence of statements that may fail on the first attempt. Before your transaction can proceed, this sub-transaction must complete successfully. If it fails, the sub-transaction rolls back to the savepoint, where it can start again. When the sub-transaction succeeds, the rest of the transaction can continue.

A SQL transaction must be active when you issue a SAVEPOINT statement.

Note: The MicroKernel allows each transaction a total of 255 internal nesting levels. However, Pervasive PSQL uses some of these levels internally to enforce atomicity on INSERT, UPDATE, and DELETE statements. Therefore, a session can effectively define no more than 253 savepoints to be active at one time. This limit may be further reduced by triggers that contain additional INSERT, UPDATE, or DELETE statements. If your operation reaches this limit, you must reduce the number of savepoints or the number of atomic statements contained within it.

Work that is rolled back within a savepoint cannot be committed even if the outer transaction(s) completes successfully. However, work that is completed within a savepoint must be committed by the outermost transaction before it is physically committed to the database.

For example, in the sample database you might start a transaction to register a student for several classes. You may successfully enroll the student in the first two classes, but this may fail on the third class because it is full or it conflicts with another class for which the student has enrolled. Even though you failed to enroll the student in this class, you don’t want to undo the student’s enrollment for the previous two classes.

The following stored procedure enrolls a student into a class by first establishing a savepoint, SP1, then inserting a record into the Enrolls table. It then determines the current enrollment for the class and compares this to the maximum size for the class. If the comparison fails, it rolls back to SP1; if it succeeds, it releases savepoint SP1.

CREATE PROCEDURE Enroll_student( IN :student ubigint, IN :classnum integer);

BEGIN

DECLARE :CurrentEnrollment INTEGER;

DECLARE :MaxEnrollment INTEGER;

SAVEPOINT SP1;

INSERT INTO Enrolls VALUES (:student, :classnum, 0.0);

SELECT COUNT(*) INTO :CurrentEnrollment FROM Enrolls WHERE class_id = :classnum;

SELECT Max_size INTO :MaxEnrollment FROM Class WHERE ID = :classnum;

IF :CurrentEnrollment >= :MaxEnrollment

THEN

ROLLBACK to SAVEPOINT SP1;

ELSE

RELEASE SAVEPOINT SP1;

END IF;

END;

Note: When working at the SQL level, transactions are controlled in different ways depending on the interface. For ODBC, transactions are controlled through the use of SQL_AUTOCOMMIT option of the SQLSetConnectOption API, in conjunction with the SQLTransact API.

For more information about the syntax of any of these statements, refer to the entries for these statements in the SQL Engine Reference.

Special Considerations

Transactions do not affect the following operations:

•

Operations that create or change dictionary definitions. Therefore, you cannot roll back the results of the following statements: ALTER TABLE, CREATE GROUP, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, CREATE TRIGGER, and CREATE VIEW.

•

Operations that remove dictionary definitions. Therefore, you cannot roll back the results of the following statements: DROP DICTIONARY, DROP GROUP, DROP INDEX, DROP PROCEDURE, DROP TABLE, DROP TRIGGER, and DROP VIEW.

•

Operations that grant or revoke security rights. Therefore, you cannot roll back the results of the following statements: CREATE GROUP, DROP GROUP, GRANT (access rights), GRANT CREATETAB, GRANT LOGIN, REVOKE (access rights), REVOKE CREATETAB, and REVOKE LOGIN.

If you attempt any of these operations within a transaction and Pervasive PSQL completes the statement, then you cannot roll back the results.

You cannot alter or drop a table (in other words, change its dictionary definition) during a transaction if you have previously referred to that table during the transaction. For example, if you start a transaction, insert a record into the Student table, and then try to alter the Student table, the ALTER statement fails. You must commit the work from this transaction, and then alter the table.

Isolation Levels

An isolation level determines the scope of a transaction locking unit by allowing you to define the extent to which a transaction is isolated from other users, who may also be in a transaction. When you use isolation levels, Pervasive PSQL automatically locks pages or tables according to the isolation level you specify. These automatic locks, which Pervasive PSQL controls internally, are called implicit locks, or transaction locks. Locks that an application specifies explicitly are called explicit locks, formerly record locks. For more information, refer to Explicit Locks.

Pervasive PSQL offers two isolation levels for your transactions :

•

Exclusive (locks the entire data file you are accessing). Corresponds to the ODBC isolation level SQL_TXN_SERIALIZABLE

•

Cursor stability (locks either the row or page you are accessing). Corresponds to the ODBC isolation level SQL_TXN_READ_COMMITTED

You set the isolation level using the ODBC API SQLSetConnectOption.

Exclusive Isolation Level (SQL_TXN_SERIALIZABLE)

When you use the exclusive isolation level, the locking unit is an entire data file. Once you access a file or files within an exclusive transaction, those files are locked from any similar access by any other user in a transaction. This type of locking is most effective when few applications attempt to access the same tables at the same time, or when large parts of the file must be locked in the course of a transaction.

Pervasive PSQL releases the lock on the file or files when you end the transaction. When you access a table during an exclusive transaction, the following conditions take effect:

•

Other tasks that are in a transaction cannot read, update, delete, or insert rows in that table until you end the transaction.

•

Other tasks that are not in a transaction can read rows in the table, but they cannot update, delete, or insert rows.

•

Multiple cursors within the same task can read any row in the table. However, when you perform an update, delete, or insert operation with a particular cursor, Pervasive PSQL locks the entire data file for that cursor.

When you access tables through a joined view using the exclusive isolation level, Pervasive PSQL locks all the accessed data files in the view.

Cursor Stability Isolation Level (SQL_TXN_READ_COMMITTED)

The transactional database engine maintains data files as a set of data pages and index pages. When you use the cursor stability isolation level, the locking unit is a data page or index page instead of a data file. When you read records within a cursor stability transaction, Pervasive PSQL locks the data pages that contain those records for possible update, but allows concurrent access to a table by multiple tasks within transactions. These read locks are released only when you read another set of records. Pervasive PSQL supports set level cursor stability since it allows an application to fetch multiple records at a time.

In addition, any data modifications you make to the data or index pages cause those records to remain locked for the duration of the transaction, even if you issue subsequent reads. Other users in a transaction cannot access these locked records until you commit or roll back your work. However, other applications can lock other pages from the same files within their own transactions.

When you access a file during a cursor stability transaction, Pervasive PSQL locks data and index pages as follows:

•

You read a row, but you do not update it or delete it. Pervasive PSQL locks the data page for that row until your next read operation or until you end the transaction.

•

You update a non-index column in a row, delete a row from a table that does not contain indexes, or insert a new row into a table that does not contain indexes. Pervasive PSQL locks the data page for that row for the remainder of the transaction, regardless of subsequent read operations.

•

You update an indexed column in a row, delete a row from a table that contains indexes, or insert a new row into a table that contains indexes. Pervasive PSQL locks the affected index page(s), as well as the data page, for the remainder of the transaction, regardless of subsequent read operations.

Cursor stability ensures that the data you read remains stable, while still allowing other users access to other data pages within the same data files. Within the cursor stability isolation level, you can generally achieve greater concurrency for all tasks by limiting the number of rows you read at one time, thereby locking fewer data pages at a time. This allows other network users access to more pages of the data file, since you do not have them locked.

However, if your application is scanning or updating large numbers of rows, you increase the possibility of completely locking other users out of the affected tables. Therefore, it is best to use cursor stability for reading, writing, and committing small transactions.

Cursor stability does not lock records within a subquery. Cursor stability does not guarantee that the conditions under which a row is returned do not change, only that the actual row returned does not change.

Transactions and Isolation Levels

Whenever you access data within a transaction, Pervasive PSQL locks the accessed pages or files for that application. No other application can write to the locked data pages or files until the locks are released.

Using the cursor stability isolation level, when you access tables through a joined view, Pervasive PSQL locks all the accessed pages for all the tables in the view. Using the exclusive isolation level, when you access tables through a joined view, Pervasive PSQL locks all the accessed tables in the view.

Pervasive PSQL performs no-wait transactions. If you try to access a record that another task has locked within a transaction, Pervasive PSQL informs you that the page or table is locked or that a deadlock has been detected. In either case, roll back your transaction and begin again. Pervasive PSQL allows multiple cursors in the same application to access the same data file.

The following steps illustrate how two applications interact while accessing the same tables within a transaction. The steps are numbered to indicate the order in which they occur.


Task 1	Task 2
1. Activate the view.
	2. Activate the view.
3. Begin a transaction.
	4. Begin a transaction.
5. Fetch records.
	6. Attempt to fetch records from the same data files.
	7. Receive Status Code 84 (Record or Page Locked) if both tasks are using cursor stability and Task 2 attempts to fetch the same records that Task 1 has already locked, or receive 85 (File Locked) if one of the tasks is using an exclusive transaction.
	8. Retry the fetch if needed.
9. Update the records.
10. End the transaction.
	11. The fetch is successful.
	12. Update the records.
	13. End the transaction.

Since a transaction temporarily locks records, pages, or tables against other applications’ updates, an application should not pause for operator input during a transaction. This is because no other application can update the records, pages, or tables accessed in the transaction until the operator responds and the transaction is terminated.

Note: Reading records within a cursor stability transaction does not guarantee that a subsequent update succeeds without conflict. This is because another application may have already locked the index page that Pervasive PSQL needs to complete the update.

Avoiding Deadlock Conditions

A deadlock condition occurs when two applications are retrying operations on tables, data pages, index pages, or records that the other one has already locked. To minimize the occurrence of deadlock situations, have your application commit its transactions frequently. Do not attempt to retry the operation from your application; Pervasive PSQL attempts a reasonable number of retries before returning an error.

Deadlock Conditions under Exclusive Isolation Level

When you use the exclusive isolation level, Pervasive PSQL locks the entire data file against updates by other applications; thus, it is possible for a deadlock to occur if your applications do not access data files in the same order, as shown in the following table.


Task 1	Task 2
1. Begin a transaction.
	2. Begin a transaction.
3. Fetch from File 1.
	4. Fetch from File 2.
5. Fetch from File 2.
6. Receive lock status.
7. Retry Step 5.
	8. Fetch from File 1.
	9. Receive lock status.
	10. Retry Step 8.

Deadlock Conditions under Cursor Stability Isolation Level

When you use the cursor stability isolation level, other applications can read and update records or pages in the file you are accessing (records or pages that your application has not locked).

Explicit Locks

When you wish to have concurrency control outside of transactions, you can use explicit locks with the use of a Pervasive PSQL ODBC driver extension to the SQLSetStmtOption API.

These locks are called explicit locks because the task is responsible for setting the locks. Explicit locks do not allow you to roll back the operations as do transactions.

The following table contains information on the ODBC driver extension that allows you to perform an exclusive lock:


fOption	vParam	Description
1153	0 (default) turns off table locking; 1 turns on table locking.	A Pervasive ODBC Engine Interface extension: When vParam is set to 1, all tables used by the hStmt are exclusively locked when a select, update, insert, delete, or create index statement is executed on the hStmt. The tables remain locked until the hStmt is dropped (by calling SQLFreeStmt with the SQL_DROP option) or vParam is set to 0 and the hStmt is re-executed. Locked tables can only be used by the locking hStmt; they cannot be used by any other hStmts.

For more information regarding this ODBC driver extension, see SQL Engine Reference.

Passive Control

If your application performs single record fetch and update sequences that are not logically connected, you can use Pervasive PSQL’s passive method of concurrency. Using this method, you can fetch and update (or delete) records without performing transactions or record locks. These operations are referred to as optimistic updates and deletes.

By default, if your task does not use transactions or explicit record locks to complete update and delete operations, your task cannot overwrite another task’s changes. The feature that ensures this data integrity is passive control, sometimes referred to as optimistic concurrency control. With passive control, your task does not perform any type of locking. If another task modifies a record after you originally fetched it, you must fetch the record again before you can perform an update or delete operation.

Under passive control, if another application updates or deletes a record between the time you fetch it and the time you issue an update or remove operation, your application receives a conflict status. This indicates that another application has modified the data since you originally fetched it. When you receive a conflict status, you must fetch the record again before you can perform the update or remove operation.

Passive control allows an application that was designed for a single-user system to run on a network without implementing lock calls. However, passive control is effective only when an application is operating in a lightly used network environment or on files in which the data is fairly static. In a heavily used environment or on files that contain volatile data, passive control may be ineffective.