Concurrency Control Through Isolation and Locking

TimesTen transactions support ANSI Serializable and ANSI Read Committed levels of isolation.

ANSI Serializable isolation is the most stringent transaction isolation level. ANSI Read Committed allows greater concurrency. Read Committed is the default and is an appropriate isolation level for most applications.

The following sections describe transaction isolation and locking levels:

• Transaction Isolation Levels

• Locking Granularities

Transaction Isolation Levels

Transaction isolation enables each active transaction to operate as if there were no other transactions active in the system.

Isolation levels determine if row-level locks are acquired when performing read operations. When a statement is issued to update a table, locks are acquired to prevent other transactions from modifying the same data until the updating transaction commits or rolls back and then releases its locks.

The Isolation connection attribute sets the isolation level for a connection. The isolation level cannot be changed in the middle of a transaction.

TimesTen supports the following two transaction isolation levels:

• ANSI Read Committed isolation: The read committed isolation level is the recommended mode of operation for most applications, and is the default mode. It enables transactions that are reading data to run concurrently with a transaction that is updating the same data. TimesTen makes multiple versions of data items to allow non-serializable read and write operations to proceed in parallel.

  Read operations do not block write operations and write operations do not block read operations, even when they read and write the same data. Read operations do not acquire locks on scanned rows. Write operations acquire locks that are held until the transaction commits or rolls back. Readers share a committed copy of the data, whereas a writer has its own uncommitted version. Therefore, when a transaction reads an item that is being updated by another in-progress transaction, it sees the committed version of that item. It cannot see an uncommitted version of an in-progress transaction.

  Read committed isolation level provides for better concurrency at the expense of decreased isolation because of the possibility of non-repeatable reads or phantom rows within a transaction. If an application runs the same query multiple times within the same transaction, the commit of an update from another transaction may cause the results from the second read operation to retrieve different results. A phantom row can appear (in modified form) in two different read operations within the same transaction, as the result of an early release of read locks during the transaction.

  To set read committed isolation level, if previously modified since this is the default, do one of the following:

  • Connect with the Isolation connection attribute set to 1. You can also modify this value with the ALTER SESSION SQL statement.

    ALTER SESSION SET ISOLATION_LEVEL=READ COMMITTED;

    See Isolation in the Oracle TimesTen In-Memory Database Reference or ALTER SESSION in Oracle TimesTen In-Memory Database SQL Reference.

  • When using ttIsql, run isolation 1 or isolation READ_COMMITTED.

  • ODBC applications can run the SQLSetConnectOption ODBC function with the SQL_TXN_ISOLATION flag set to SQL_TXN_READ_COMMITTED. See Option Support for ODBC 2.5 SQLSetConnectOption and SQLGetConnectOption in the Oracle TimesTen In-Memory Database C Developer's Guide.

  • JDBC applications can run the setTransactionIsolation JDBC method of the Connection object to TRANSACTION_READ_COMMITTED.

• ANSI Serializable isolation: All locks acquired within a transaction by a read or write operation are held until the transaction commits or rolls back. Read operations block write operations, and write operations block read operations. But read operations do not block other read operations.

  • If an initial transaction reads a row, a second transaction can also read the row.

  • A row that has been read by an initial transaction cannot be updated or deleted by a second transaction until the initial transaction commits or rolls back. However, the second transaction can insert rows that are not in the same range that are locked by the first transaction. For example, if the first transaction is reading rows 1 through 10, then the second transaction cannot update rows 1 through 10. However, the second transaction can insert into rows greater than 10.

  • A row that has been inserted, updated or deleted by an initial transaction cannot be accessed in any way by a second transaction until the initial transaction either commits or rolls back.

  Serializable isolation level provides for repeatable reads and increased isolation at the expense of decreased concurrency. A transaction that runs the same query multiple times within the same transaction is guaranteed to see the same result set each time. Other transactions cannot update or delete any of the returned rows, nor can they insert a new row that satisfies the query predicate.

  To set the isolation level to serializable isolation, do one of the following:

  • Connect with the Isolation connection attribute set to 0. You can also modify this value with the ALTER SESSION SQL statement.

    ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE ;

    See Isolation in the Oracle TimesTen In-Memory Database Reference or ALTER SESSION in Oracle TimesTen In-Memory Database SQL Reference.

  • When using ttIsql, run isolation 0 or isolation SERIALIZABLE.

  • ODBC applications can run the SQLSetConnectOption ODBC function with the SQL_TXN_ISOLATION flag set to SQL_TXN_SERIALIZABLE.

  • JDBC applications can run the setTransactionIsolation JDBC method of the Connection object to TRANSACTION_SERIALIZABLE.

  To ensure that materialized views are always in a consistent state, all view maintenance operations are performed under serializable isolation, even when the transaction is in read committed isolation. This means that the transaction obtains read locks for any data items read during view maintenance. However, the transaction releases the read locks at the end of the INSERT, UPDATE or CREATE VIEW statement that triggered the view maintenance, instead of holding them until the end of the transaction.

Note:

The ttXactAdmin utility generates a report showing lock holds and lock waits for all outstanding transactions. It can be used to troubleshoot lock contention problems where operations are being blocked, or encountering lock timeout or deadlock errors. It can also be used to rollback a specified transaction.

Locking Granularities

TimesTen supports row-level locks, table-level locks and database-level locks.

Different connections can coexist with different levels of locking, but the presence of even one connection using database-level locking leads to reduced concurrency. For performance information, see Choose the Best Method of Locking.

Note:

When multiple locks have been obtained within the same transaction, the locks are released sequentially when the transaction ends.

• Row-level locking: Transactions usually obtain locks on the individual rows that they access. Row-level locking is the recommended mode of operation because it provides the finest granularity of concurrency control. It allows concurrent transactions to update different rows of the same table. However, row-level locking requires space in the database's temporary memory region to store lock information.

  Row-level locking is the default. However, if it has been modified to another type of locking and you want to re-enable row-level locking, do one of the following:

  • Set the LockLevel connection attribute to 0.

  • In TimesTen Classic, call the ttLockLevel built-in procedure with the lockLevel parameter set to Row. This procedure changes the lock level between row-level and database-level locking on the next transaction and for all subsequent transactions for this connection.

  • Run the ttOptSetFlag built-in procedure to set the RowLock parameter to 1, which enables the optimizer to consider using row locks.

  Note:

  See LockLevel, ttLockLevel, and ttOptSetFlag in the Oracle TimesTen In-Memory Database Reference.

• Table-level locking: Table-level locking is recommended when concurrent transactions access different tables or a transaction accesses most of the rows of a particular table. Table-level locking provides better concurrency than database-level locking. Row-level locking provides better concurrency than table-level locking. Table-level locking requires only a small amount of space in the temporary memory region to store lock information.

  Table-level locking provides the best performance for the following:

  • Queries that access a significant number of rows of a table

  • When there are very few concurrent transactions that access a table

  • When temporary space is inadequate to contain all row locks that an operation, such as a large insert or a large delete, might acquire

  To enable table-level locking, run the ttOptSetFlag procedure to set the TblLock parameter to 1, which enables the optimizer to consider using table locks. In addition, set RowLock to 0 so that the optimizer does not consider row-level locks.

  If both table-level and row-level locking are disabled, TimesTen defaults to row-level locking. If both table-level and row-level locking are enabled, TimesTen chooses the locking scheme that is more likely to have better performance. Even though table-level locking provides better performance than row-level locking because of reduced locking overhead, the optimizer often chooses row-level locking for better concurrency. For more information, see ttOptSetFlag in the Oracle TimesTen In-Memory Database Reference.

• Database-level locking: Database-level locking serializes all transactions, which effectively allows no concurrency on the database. When a transaction is started, it acquires an exclusive lock on the database, which ensures that there is no more than one active transaction in the database at any given time. It releases the lock when the transaction is completed.

  Database-level locking can provide better performance than row-level locking, due to reduced locking overhead. In addition, it provides higher throughput than row-level locking when running a single stream of transactions such as a bulk load operation. However, its applicability is limited to applications that never run multiple concurrent transactions. With database-level locking, every transaction effectively runs in ANSI Serializable isolation, since concurrent transactions are disallowed.

  To enable database-level locking, do one of the following:

  • Set the LockLevel connection attribute to 1.

  • In TimesTen Classic, call the ttLockLevel built-in procedure with the lockLevel parameter set to DS. This procedure changes the lock level between row-level and database-level locking on the next transaction and for all subsequent transactions for this connection.

Setting Wait Time for Acquiring a Lock

Set the LockWait connection attribute to the maximum amount of time that a statement waits to acquire a lock before it times out. The default is 10 seconds. If a statement within a transaction waits for a lock and the lock wait interval has elapsed, an error is returned. After receiving the error, the application can reissue the statement.

Lock wait intervals are imprecise due to the scheduling of the database's managing subdaemon process to detect lock timeouts. This imprecision does not apply to zero-second timeouts, which are always immediately reported. The lock wait interval does not apply to blocking checkpoints.

The database's managing subdaemon process checks every two seconds to see if there is a deadlock in the database among concurrent transactions. If a deadlock occurs, an error is returned to one of the transactions involved in the deadlock cycle. The transaction that receives the error must rollback in order to allow the other transactions involved in the deadlock to proceed.

When running a workload of high lock-contention potential, consider setting the LockWait connection attribute to a smaller value for faster return of control to the application, or setting LockWait to a larger value to increase the successful lock grant ratio (with a risk of decreased throughput).

See LockWait in Oracle TimesTen In-Memory Database Reference.