Controlling Cloudscape Application Behavior
Page 4 of 7

Locking, Concurrency, and Isolation

This section discusses topics pertinent to multi-user systems, in which concurrency is important. Cloudscape is configured by default to work well for multi-user systems. For single-user systems, you may want to tune your system so that it uses fewer resources; see Lock Granularity.

Isolation Levels and Concurrency

Cloudscape provides three transaction isolation levels. Setting the transaction isolation level for a connection allows a user to specify how severely the user's transaction should be isolated from other transactions. For example, it allows you to specify whether transaction A is allowed to make changes to data that have been viewed by transaction B before transaction B has committed.

A connection determines its own isolation level, so JDBC provides an application with a way to specify a level of transaction isolation. It specifies five levels of transaction isolation. The higher the transaction isolation, the more care is taken to avoid conflicts; avoiding conflicts sometimes means locking out transactions. Lower isolation levels thus allow greater concurrency.

Inserts, updates, and deletes always behave the same no matter what the isolation level is. Only the behavior of select statements varies.

JDBC defines the following isolation levels:

  • TRANSACTION_READ_UNCOMMITTED (ANSI level 0)

    Not supported in Cloudscape Version 3.6

  • TRANSACTION_READ_COMMITTED (ANSI level 1)
  • TRANSACTION_REPEATABLE_READ (ANSI level 2)
  • TRANSACTION_SERIALIZABLE (ANSI level 3)

NEW: Support for TRANSACTION_REPEATABLE_READ is new in Version 3.6.

These levels allow you to avoid particular kinds of transaction anomalies, which are described in Table 6-2

Table 6-2 Transaction Anomalies


Example

Dirty Reads

A dirty read happens when a transaction reads data that is being modified by another transaction that has not yet committed.

Transaction A begins.

UPDATE Flights SET miles = 7 WHERE flight_id = 'AA1111'

Transaction B begins.

SELECT * FROM Flights

(Transaction B sees data updated by transaction A. Those updates have not yet been committed.)

Non-Repeatable Reads

Non-repeatable reads happen when a query returns data that would be different if the query were repeated within the same transaction. Non-repeatable reads can occur when other transactions are modifying data that a transaction is reading.

Transaction A begins.

SELECT * FROM Flights WHERE flight_id = 'AA1111'

Transaction B begins.

UPDATE Flights SET miles = 7 WHERE flight_id = 'AA1111'

(Transaction B updates rows viewed by transaction A before transaction A commits.)

If Transaction A issues the same SELECT statement, the results will be different.

Phantom Reads

Records that appear in or disappear from a set being read by another transaction. Phantom reads can occur when other transactions insert or delete rows that would satisfy the WHERE clause of another transaction's statement.

Transaction A begins.

SELECT * FROM Flights WHERE flight_id BETWEEN 'AA1112' AND 'AA1115'

Transaction B begins.

INSERT INTO Flights VALUES 'AA1114'

Transaction B inserts a row that would satisfy Transaction A's query if it were issued again.

.

The transaction isolation level is a way of specifying whether these transaction anomalies are allowed. The transaction isolation level thus affects the quantity of data locked by a particular transaction. In addition, a DBMS's locking schema may also affect whether these anomalies are allowed. A DBMS may lock either the entire table or only specific rows in order to prevent transaction anomalies.

Table 6-3 shows which anomalies are possible under the various locking schemas and isolation levels.

Table 6-3 When Transaction Anomalies Are Possible

Isolation Level

Table-Level Locking

Row-Level Locking

TRANSACTION_READ_UNCOMMITTED

Dirty reads, nonrepeatable reads, and phantom reads possible

Dirty reads, nonrepeatable reads, and phantom reads possible

TRANSACTION_READ_COMMITTED

Nonrepeatable reads and phantom reads possible

Nonrepeatable reads and phantom reads possible

TRANSACTION_REPEATABLE_READ

Phantom reads not possible because entire table is locked

Phantom reads possible

TRANSACTION_SERIALIZABLE

None

None

In Cloudscape Version 3.6, the following java.sql.Connection isolation levels are available:

  • TRANSACTION_SERIALIZABLE

    Within the Cloudscape system (not JDBC), called SERIALIZABLE

    SERIALIZABLE means that Cloudscape treats the transactions as if they occurred serially (one after the other) instead of concurrently. Cloudscape issues locks to prevent all the transaction anomalies listed in Table 6-2 from occurring. The type of lock it issues is sometimes called a range lock. (You will learn more about this in range locks.)

  • TRANSACTION_REPEATABLE_READ

    Within the Cloudscape system (not JDBC), called REPEATABLE_READ.

    REPEATABLE_READ means that Cloudscape issues locks to prevent only dirty reads and non-repeatable reads, but not phantoms. It does not issue range locks for selects.

  • TRANSACTION_READ_COMMITTED

    Within the Cloudscape system (not JDBC), called READ_COMMITTED or READ COMMITTED

    READ_COMMITTED means that Cloudscape issues locks to prevent only dirty reads, not all the transaction anomalies listed in Table 6-2.

    READ_COMMITTED is the default isolation level for transactions.

NEW: Support for TRANSACTION_REPEATABLE_READ is new in Version 3.6.

Attempting to set isolation to another level results in a "Feature not implemented" (SQLState XJZZZ) SQLException. Cloudscape will offer more isolation level options in future releases.

Configuring Isolation Levels

If a connection does not specify its isolation level, it inherits the default isolation level for the Cloudscape system, which you set with the cloudscape.language.defaultIsolationLevel property. The default value of that property is READ_COMMITTED. When set to READ_COMMITTED, the connection inherits the TRANSACTION_READ_COMMITTED isolation level. When set to SERIALIZABLE, the connection inherits the TRANSACTION_SERIALIZABLE isolation level.

To override the inherited default, use the methods of java.sql.Connection.

In addition, a connection can change the isolation level of the transaction within an SQL-J statement. For more information, see SET TRANSACTION ISOLATION LEVEL statement of the Cloudscape Reference Manual. The AT ISOLATION clause changes the isolation level for the current statement only. See AT ISOLATION clause of the Cloudscape Reference Manual.

With the exception of using the AT ISOLATION clause, changing the isolation level commits the current transaction.

NOTE: For information about how to choose a particular isolation level, see Tuning Cloudscape.

Lock Granularity

Cloudscape can be configured for table-level locking. With table-level locking, when a transaction locks data in order to prevent any transaction anomalies, it always locks the entire table, not just those rows being accessed.

By default, Cloudscape is configured for row-level locking. Row-level locking uses more memory but allows greater concurrency, which works better in multi-user systems. Table-level locking works best with single-user applications or read-only applications.

You typically set lock granularity for the entire Cloudscape system, not for a particular application. However, at compilation time or runtime, Cloudscape may escalate the lock granularity for a particular transaction from row-level locking to table-level locking for performance reasons. You have some control over the threshold at which this occurs. For information on turning off row-level locking, see cloudscape.storage.rowLocking in Tuning Cloudscape. For more information about automatic lock escalation, see About the Optimizer's Selection of Lock Granularity and Transaction-Based Lock Escalation in Tuning Cloudscape. For more information on tuning your Cloudscape system, see Chapter 3, "Tuning Databases and Applications".

You can override row-level locking for individual tables. Because row-level locking uses more resources, it is useful to be able to use table-level locking where possible. For example, you may have a table that you know will be read-only after you populate it, or one that will always be accessed by only one user at a time, or you want to ensure that only one user accesses it at a time. You override row-level locking with the SET LOCKING clause when you create or alter a table. For more information, see CREATE TABLE statement in the Cloudscape Reference Manual.

Types and Scope of Locks in Cloudscape Systems

Exclusive Locks

When a statement modifies data, its transaction holds an exclusive lock on data that prevents other transactions from accessing the data. This lock remains in place until the transaction holding the lock issues a commit or rollback. Table-level locking lowers concurrency in a multi-user system.

Shared Locks

When a statement reads data without making any modifications, its transaction obtains a shared lock on the data. Another transaction that tries to read the same data is permitted to read, but a transaction that tries to update the data will be prevented from doing so until the shared lock is released. How long this shared lock is held depends on the isolation level of the transaction holding the lock. Transactions using the READ COMMITTED isolation level release the lock when the transaction steps through to the next row. Transactions using the SERIALIZABLE or REPEATABLE_READ isolation level hold the lock until the transaction is committed, so even a SELECT can prevent updates if a commit is never issued.

Update Locks

When a user-defined update cursor (created with the FOR UPDATE clause) reads data, its transaction obtains an update lock on the data. If the user-defined update cursor updates the data, the update lock is converted to an exclusive lock. If the cursor does not update the row, when the transaction steps through to the next row, transactions using the READ COMMITTED isolation level release the lock, and transactions using the SERIALIZABLE or REPEATABLE_READ isolation level downgrade it to a shared lock until the transaction is committed.

Update locks help minimize deadlocks.

Lock Compatibility

The following table lists compatibility between lock types. + means compatible,
- means incompatible.

Table 6-4 Lock Compatibility Matrix


Shared

Update

Exclusive

Shared

+

-

-

Update

+

-

-

Exclusive

-

-

-

Scope of Locks

The amount of data locked by a statement can vary.

  • table locks

    A statement may lock the entire table.

    Table-level locking systems always lock entire tables.

    Row-level locking systems may lock entire tables if the statement's WHERE clause can't use an index. For example, UPDATES that cannot use an index lock the entire table.

    Row-level locking systems may lock entire tables if a high number of single-row locks would be less efficient than a single table-level lock. Choosing table-level locking instead of row-level locking for performance reasons is called lock escalation. (For more information about this topic, see About the Optimizer's Selection of Lock Granularity and Transaction-Based Lock Escalation in Tuning Cloudscape.)

  • single-row locks

    A statement may lock only a single row at a time.

    This section applies only to row-level locking systems.

    For READ COMMITTED or REPEATABLE_READ isolation, Cloudscape treats rows as cursors for SELECT statements. It locks rows only as the application steps through the rows in the result. The current row is locked. The row lock is released when the application goes to the next row. (For SERIALIZABLE isolation, however, Cloudscape locks the whole set before the application begins stepping through. See range locks.)

    Cloudscape locks single rows for INSERT statements, holding each row until the transaction is committed. (If there is an index associated with the table, the previous key is also locked.)

  • range locks

    A statement may lock a range of rows (range lock).

    This section applies only to row-level locking systems.

    For any isolation level, Cloudscape locks all the rows in the result plus an entire range of rows for updates or deletes.

    For the SERIALIZABLE isolation level, Cloudscape locks all the rows in the result plus an entire range of rows in the table for SELECTs to prevent nonrepeatable reads and phantoms.

    For example, if a SELECT statement specifies rows in the HotelAvailability table where the booking_date is BETWEEN two dates, the system may lock more than just the actual rows it returns in the result. It also must lock the entire range of rows between those two dates to prevent another transaction from inserting, deleting, or updating a row within that range.

    An index must be available for a range lock. If one is not available, Cloudscape locks the entire table.

Table 6-5 Possible Types and Scopes of Locking

Transaction Isolation Level

Table-Level Locking

Row-Level Locking

Read Committed

SELECT statements get a shared lock on the entire table. The locks are released when the user closes the ResultSet.

Other statements get exclusive locks on the entire table, which are released when the transaction commits.

SELECTs lock and release single rows as the user steps through the ResultSet.

UPDATEs and DELETEs get exclusive locks on a range of rows.

INSERT statements get exclusive locks on single rows (and sometimes on the preceding rows).

Repeatable Read

Same as for Serializable.

SELECT statements get shared locks on the rows that satisfy the WHERE clause (but do not prevent inserts into this range).

UPDATEs and DELETEs get exclusive locks on a range of rows.

INSERT statements get exclusive locks on single rows (and sometimes on the preceding rows).

Serializable

SELECT statements get a shared lock on the entire table.

Other statements get exclusive locks on the entire table, which are released when the transaction commits.

SELECT statements get shared locks on a range of rows.

UPDATE and DELETE statements get exclusive locks on a range of rows.

INSERT statements get exclusive locks on single rows (and sometimes on the preceding rows).

Notes on Locking

In addition to the locks already described, foreign key lookups require briefly held shared locks on the referenced table (row or table, depending on the configuration).

The table and examples in this section do not take performance-based lock escalation into account. Remember that the system may choose table-level locking for performance reasons.

Bulk Fetch and Row Locks

For SELECTs in a read committed environment, Cloudscape in theory locks a single row at a time--the row being looked at currently by the application. This isn't actually true when bulk fetch is turned on. Bulk fetch is a performance enhancement that means that Cloudscape fetches more than one row at a time from the underlying conglomerate (index or table). By default, Cloudscape fetches 16 rows at a time.

When bulk fetch is used, Cloudscape actually locks a different row from the one being looked at.

Some applications may need to rely on the current row being locked because they may go on to update the row currently being looked at. The most sensible thing in such a situation is for an application to use an updatable cursor, since bulk fetch is automatically turned off in that situation. Otherwise, applications could do one of the following:

  • turn off bulk fetch (see bulkFetch in Tuning Cloudscape)
  • use a serializable isolation level.

Locking Examples

No examples are provided for table-level locking, because the entire table is always locked.

Row-Level Locking, TRANSACTION_READ_COMMITTED

-- Cloudscape locks and releases the rows as
-- the user steps through them
SELECT hotel_id, booking_date, rooms_taken
FROM HotelAvailability

-- (no index on miles)
-- Cloudscape locks and releases the rows as
-- the user steps through them
SELECT flight_id
FROM Flights
WHERE miles > 4000

-- Cloudscape locks but does not release the rows as
-- the user steps through them
SELECT hotel_id, booking_date, rooms_taken
FROM HotelAvailability FOR UPDATE

-- Cloudscape locks the entire range right away
-- and does not release them until the commit
UPDATE HotelAvailability
SET rooms_taken = 4
WHERE hotel_id = 16
AND booking_date BETWEEN '1999-01-10' AND '1999-01-15'

-- Cloudscape locks the row being inserted until commit
-- it gets a very brief lock on the Hotels table
-- (a foreign key references Hotels)
INSERT INTO HotelAvailability
VALUES (201, '1999-05-99', 10)

-- Cloudscape locks the range of rows
-- until commit
DELETE FROM HotelAvailability
WHERE booking_date < CURRENT_DATE

Row-Level Locking, TRANSACTION_SERIALIZABLE and REPEATABLE_READ

-- Cloudscape locks the entire range (most likely
-- the entire table)
SELECT hotel_id, booking_date, rooms_taken
FROM HotelAvailability

-- (no index on miles)
-- Cloudscape locks the entire table
SELECT flight_id
FROM Flights
WHERE miles > 4000

-- (with an index on miles)
-- For serializable, Cloudscape locks the range of rows greater
-- than 4000 and prevents inserts that would satisfy this
-- WHERE clause. For repeatable read, it locks all the rows
-- that satisfy the WHERE clause but does not lock the *range*;
-- it does not prevent new
-- rows that would satisfy the WHERE clause.
SELECT flight_id
FROM Flights
WHERE miles > 4000

-- Cloudscape locks the entire
-- range until commit
UPDATE HotelAvailability
SET rooms_taken = 4
WHERE hotel_id = 16
AND booking_date BETWEEN '1999-01-10' AND '1999-01-15'

-- Cloudscape locks the row being inserted until commit
-- It gets a very brief lock on the Hotels table
-- (foreign key reference)
INSERT INTO HotelAvailability
VALUES (201, '1999-05-99', 10)

-- Cloudscape locks the range of rows being deleted
-- until commit
DELETE FROM HotelAvailability
WHERE booking_date < CURRENT_DATE

Deadlocks

In a database, a deadlock is a situation in which two or more transactions are waiting for one another to give up locks.

For example, Transaction A may hold a lock on some rows in the Accounts table and needs to update some rows in the Orders table to finish. Transaction B holds locks on those very rows in the Orders table but needs to update the rows in the Accounts table held by Transaction A. Transaction A cannot complete its transaction because of the lock on Orders. Transaction B cannot complete its transaction because of the lock on Accounts. All activity comes to a halt and remains at a standstill forever unless the DBMS detects the deadlock and aborts one of the transactions.

Figure 6-1 A deadlock.

Avoiding Deadlocks

Using both row-level locking and the TRANSACTION_READ_COMMITTED isolation level makes it likely that you will avoid deadlocks (both settings are Cloudscape defaults). However, deadlocks are still possible. Cloudscape application developers can avoid deadlocks by using consistent application logic; for example, transactions that access Accounts and Orders should always access the tables in the same order. That way, in the scenario described above, Transaction B simply waits for transaction A to release the lock on Orders before it begins. When transaction A releases the lock on Orders, Transaction B can proceed freely.

Another tool available to you is the LOCK TABLE statement. A transaction can attempt to lock a table in exclusive mode when it starts to prevent other transactions from getting shared locks on a table. For more information, see LOCK TABLE statement in the Cloudscape Reference Manual.

Deadlock Detection

When a transaction waits more than a specific amount of time to obtain a lock (called the deadlock timeout), Cloudscape can detect whether the transaction is involved in a deadlock. When Cloudscape analyzes such a situation for deadlocks it tries to determine how many transactions are involved in the deadlock (two or more). Usually aborting one transaction breaks the deadlock. Cloudscape must pick one transaction as the victim and abort that transaction; it picks the transaction that holds the fewest number of locks as the victim, on the assumption that that transaction has performed the least amount of work. (This may not be the case, however; the transaction may have recently been escalated from row-level locking to table locking and thus hold a small number of locks even though it has done the most work.)

When Cloudscape aborts the victim transaction, it receives a deadlock error (an SQLException with an SQLState of 40001). The error message gives you the transaction IDs, the statements, and the status of locks involved in a deadlock situation.

ERROR 40001: A lock could not be obtained due to a deadlock,
cycle of locks & waiters is:
Lock : TABLE, X, FLIGHTS, Tablelock
Victim XID : 1221, APP,
update flights set miles = miles + 1
    Granted XID : 1222
Lock : TABLE, X, FLIGHTAVAILABILITY, Tablelock
Waiting XID : 1222, APP, update flightavailability set
    economy_seats_taken = 1
0
Granted XID : 1221

For information on configuring when deadlock checking occurs, see Configuring Deadlock Detection and Lock Wait Timeouts.

NOTE: Deadlocks are detected only within a single database. Deadlocks across multiple databases are not detected. Non-database deadlocks caused by Java synchronization primitives are not detected by Cloudscape.

Lock Wait Timeouts

Even if a transaction is not involved in a deadlock, it may have to wait a considerable amount of time to obtain a lock because of a long-running transaction or transactions holding locks on the tables it needs. In such a situation, you may not want a transaction to wait indefinitely. Instead, you may want the waiting transaction to abort, or time out, after a reasonable amount of time, called a lock wait timeout. (For information about configuring the lock wait timeout, see Configuring Deadlock Detection and Lock Wait Timeouts.)

Configuring Deadlock Detection and Lock Wait Timeouts

You configure the amount of time a transaction waits before Cloudscape does any deadlock checking with the cloudscape.locks.deadlockTimeout property. You configure the amount of time a transaction waits before timing out with the cloudscape.locks.waitTimeout property. When configuring your database or system, you should consider these properties together. For example, in order for any deadlock checking to occur, the cloudscape.locks.deadlockTimeout property must be set to a value lower than the cloudscape.locks.waitTimeout property. If it is set to a value equal to or higher than the cloudscape.locks.waitTimeout, the transaction times out before Cloudscape does any deadlock checking.

By default, cloudscape.locks.waitTimeout is set to 180 seconds. -1 is the equivalent of no wait timeout. This means that transactions never time out, although Cloudscape may choose a transaction as a deadlock victim.

Figure 6-2, Figure 6-3, and Figure 6-4 show some example configurations.

Figure 6-2 One possible configuration: deadlock checking occurs when a transaction has waited 30 seconds; no lock wait timeouts occur.

Figure 6-3 Another typical configuration: deadlock checking occurs after a transaction has waited 60 seconds for a lock; after 90 seconds, the transaction times out and is rolled back.

Figure 6-4 A configuration in which no deadlock checking occurs: transactions time out after they have waited 50 seconds. No deadlock checking occurs.

Debugging Deadlocks

If deadlocks are happening frequently in your multi-user system with a particular application, you may need to do some debugging. Cloudscape provides a VTI class to help you in this situation, COM.cloudscape.vti.LockTable. For information, see the Cloudscape Server and Administration Guide.

Programming Applications to Handle Deadlocks

When you configure your system for deadlock and lockwait timeouts and an application could be chosen as a victim when the transaction times out, you should program your application to handle this. To do this, test for SQLExceptions with SQLStates of 40001 (deadlock timeout) or 40XL1 or 4)XL2 (lockwait timeout).

In the case of a deadlock you may want to re-try the transaction that was chosen as a victim. In the case of a lock wait timeout, you probably do not want to do this right away.

The following code is one example of how to handle a deadlock timeout.

/// if this code may encounter a deadlock, put the whole 
// thing in a try/catch block
// then try again if the deadlock victim exception was thrown
try {
    s.executeUpdate(
        "UPDATE FlightAvailability " +
        "SET economy_seats_taken = economy_seats_taken " +
         "+ 1 WHERE flight_id = 'AA1116'");
    s.executeUpdate(
        "UPDATE HotelAvailability " +
        "SET rooms_taken = 0 WHERE hotel_id = 9");
}
    // note: do not catch such exceptions in database-side methods;
    // catch such exceptions only at the outermost level of 
    // application code. 
    // See   Database-Side JDBC Methods and SQLExceptions.    catch (SQLException se) {
    if (se.getSQLState().equals("40001")) {
        // it was chosen as a victim of a deadlock.
        // try again at least once at this point.
        System.out.println(
            "Will try the transaction again.");
        s.executeUpdate(
            "UPDATE FlightAvailability "+
            "SET economy_seats_taken = economy_seats_taken " +
            "+ 1 WHERE flight_id = 'AA1116'");
        s.executeUpdate(
            "UPDATE HotelAvailability "+
            "SET rooms_taken = 0 WHERE hotel_id = 9");
}
    else
        throw se;
    }