Working with Connections and Transactions
Page 2 of 3

Transactions

Relational DBMSs owe much of their success to their support for transactions. A transaction is a set of one or more SQL statements that make up a logical unit of work. A transaction ends with either a commit or a rollback (which you saw in Database-Side JDBC Methods). A commit makes permanent the changes resulting from the SQL statements in the transaction; rollback undoes them all. Commit and rollback, which are JDBC methods on a Connection object, also mark the beginning of a new transaction.

It is common to talk about the ACID properties of transaction control:

  • Atomic

    All of the operations in a command are performed, or none of them. The transaction has atomicity: it either commits or aborts.

  • Consistent

    Transactions allow programmers to declare consistency points that can be validated by the system.

  • Isolated

    Since more than one transaction can be in process at a time, the system must give each user data that appears unaffected by other transactions until the user's transaction commits.

  • Durable

    A transaction's committed updates must be durable--even in the case of hardware or software failures. Once a transaction commits, it stays committed.

Transactions guarantee atomicity, which is useful in the case of system failure. If a system goes down while a transaction is pending, when the system restarts the entire transaction is rolled back. For example, in a banking-related application, a transaction that involves a transfer of money from one account to another might involve two steps:

  • withdrawing money from a savings account
  • depositing money into a checking account

Without transaction control, if the system crashes between the two steps, the customer has lost money. However, since the steps are part of the transaction, when the system comes up, all transactions that were as yet uncommitted are rolled back. That means that the money is returned to the savings account.

Transactions also guarantee durability. This means that transactions, once committed, won't be affected by system failures. If the above transaction commits before the system goes down, when the system comes up, the database state reflects the activities of the transaction.

Transactions allow a DBMS to maintain consistency.

A DBMS must isolate one transaction from another. In a multi-user system, it is possible for two users to look at the same data at the same time. If one user inserts, updates, or deletes data, it is possible that the other user may see partly old and partly updated data; data will appear to be inconsistent. The degree to which a DBMS isolates one transaction from another is standardized by both SQL-92 and JDBC. Using the JDBC terminology, the isolation levels that Cloudscape currently supports are called:

  • TRANSACTION_SERIALIZABLE

    When more than one transaction is in process at one time, Cloudscape serializes the transactions. This means that if a user, within a transaction, alters data, all other users are denied access to the data until the transaction has committed. If a user within a transaction views data in a table, all other transactions are prevented from altering the data until the transaction has committed. Even though applications may attempt actions against the database concurrently, Cloudscape processes them as if they happened serially (one after the other). Within the TRANSACTION_SERIALIZABLE level, some actions are permitted to happen concurrently:

    • Two or more transactions may view (SELECT) the same data.
    • If a transaction is the first to view data and other transactions read the data, the first transaction can modify or delete the data; other transactions cannot.
  • READ_COMMITTED (the default setting)

    When more than one transaction is in process at one time, Cloudscape makes sure that a transaction reads changes made by other transactions only when they have been committed by the transaction. Transactions are permitted to modify data already viewed by other uncommitted transactions, but they are not permitted to modify data that is currently being viewed by uncommitted transactions (the current row). Once a single transaction modifies data, no other transaction can view or modify the data until that transaction commits.

Transaction Isolation and Locking

Cloudscape enforces the isolation of transactions with locking. The first transaction to SELECT data gets a shared lock on the data. This lock may be shared with SELECT statements from other transactions.

In order to insert, update, or delete data, a transaction needs an exclusive lock on data. A transaction can get an exclusive lock if it currently has a shared lock on the data, or if no other transaction has any lock on the data.

Once data is locked by an exclusive lock, no other transaction can view (SELECT) or insert, update, or delete the data.

You can configure Cloudscape to use a simple kind of locking called table-level locking. That means that when a transaction gets a lock on data, it locks the entire table, even if only a single row is involved in the transaction, until a commit or rollback releases the lock. Table-level locking uses fewer resources but provides poor performance for multi-user applications (unless they are read-only).

The default setting for Cloudscape is to use a more sophisticated kind of locking called row-level locking. Row-level locking means that a transaction gets a lock only on the rows involved in the transaction, not on the entire table. Such a locking schema allows greater concurrency among transactions but uses more resources than table-level locking. Multiple-user systems typically benefit from row-level locking.

Transactions and Connections

In a Cloudscape system, a transaction is associated with a single connection to Cloudscape. A single connection cannot do the work of more than one transaction; and the work of a single transaction cannot span more than one connection.

Also, a transaction does not endure longer than a single session. A session begins when a connection is first established, and ends when a connection ends.

A single connection can execute multiple transactions only serially--one after the other. However, multiple connections can execute transactions concurrently (at the same time as each other).

In a multi-user system, a connection--and its transactions--are typically associated with a single user. However, because Cloudscape permits an application to have more than one connection to the system, a single application/user may be associated with more than one connection/transaction.

NOTE: The queries in this chapter should return rows if you have run JBMSTours.BuildATour, as described several times in this tutorial. If you skipped those sections, run that application as described in Run JBMSTours.BuildATour a Few More Times before trying out this chapter.

In this section, you will first set the transaction isolation level for each transaction to TRANSACTION_SERIALIZABLE, the most restrictive. Then you will set the isolation level to READ_COMMITTED and observe the difference in behavior.

Start ij and Open a New, Named Connection

ij permits you to name a connection with the connect command. In this section, you will open a named connection to toursDB and turn off the auto-commit feature.

  1. Start ij following the instructions in Start ij.
  2. Open a connection to toursDB called TransactionA. Turn off auto-commit by using the "false" value with the autocommit attribute on the database name.

    connect 'toursDB;autocommit=false' as TransactionA;

  3. Change the isolation level for this transaction to SERIALIZABLE.

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

  4. Have ij show all current connections using the show connections command.

    show connections;

    TRANSACTIONA* - jdbc:cloudscape:toursDB;autocommit=false
    * = current connection

Set a Database Property

The default lock wait timeout is 90 seconds. You probably don't want to wait that long when you do some of the steps in this lesson, so set a property to lower that to 35 seconds. You will do that by setting a database property, which is a property that is stored in the database and valid for the current database only.

To set a database property, you use the void method setDatabaseProperty in COM.cloudscape.databasePropertyInfo (aliased as PropertyInfo). This method takes two arguments:

  • the name of the property
  • its value
  • Execute the following SQL-J statement:

    CALL PropertyInfo.setDatabaseProperty('cloudscape.locks.waitTimeout', '35');

  • Commit:

    commit;

  • Double-check that you did it right:

    VALUES PropertyInfo.getDatabaseProperty(
        'cloudscape.locks.waitTimeout');

    The statement should return 35.

Open a Second Named Connection

  1. Open a second connection named TransactionB:

    connect 'toursDB;autocommit=false' as TransactionB;

  2. Change the isolation level for this transaction to SERIALIZABLE.

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

  3. Have ij show all current connections using ij's show connections command.

    show connections;

    TransactionA - jdbc:cloudscape:toursDB;autocommit=false
    TransactionB* - jdbc:cloudscape:toursDB;autocommit=false
    * = current connection

    Note that the current connection is now TransactionB.

  4. Switch back to TransactionA using ij's set connection command:

    set connection TransactionA;

    An ij command prompt including the name of the current connection in parentheses should appear:

    ij(TransactionA)>

    The connection name appears in the ij prompt only when more than one connection is available.

Begin a Transaction in Each Connection

  1. In TransactionA, view all data in the HotelBookings table:

    SELECT * FROM HotelBookings;

  2. Switch to TransactionB:

    set connection TransactionB;

  3. View all data in the HotelBookings table:

    SELECT * FROM HotelBookings;

    Both transactions can view data from this table concurrently. When a transaction selects data from a table, it gets a shared lock on data in the table. This lock can be shared with any other transaction that tries to select data from the table. However, another transaction that tries to modify data will not be able to. In order to INSERT, UPDATE, or DELETE data, a transaction needs an exclusive lock. A transaction cannot get an exclusive lock on a table when another transaction has a shared lock on it.

  4. From the current connection (TransactionB), try to insert a row into the table:

    INSERT INTO HotelBookings VALUES
    (100, 4, DATE'1998-01-01', DATE'1998-01-05', 3, 900.00);

    ij will not respond for quite some time (well, for about 35 seconds). Cloudscape is preventing TransactionB from updating data currently being viewed (held in a shared lock) by TransactionA. Since no one is attending to TransactionA to complete that transaction, the system should respond with a lock wait timeout.

    ij should eventually respond with an error message:

    ERROR 40XL1: A lock could not be obtained within the time requested

    TransactionB could not get the exclusive lock. TransactionB's transaction is rolled back, and a new transaction implicitly begins.

    Quiz: I don't get a deadlock; I can read the data just fine. Why?

    Answer: Turn off auto-commit. With auto-commit turned on, a commit is implicitly executed after every statement, freeing up locks.

  5. Return to TransactionA:

    set Connection TransactionA;

  6. Now try to insert the row from within TransactionA:

    INSERT INTO HotelBookings VALUES
    (100, 4, DATE'1998-01-01', DATE'1998-01-05', 3, 900.00);

    TransactionA is able to promote its shared lock to an exclusive lock and can successfully insert the data.

    1 row inserted/updated/deleted

  7. From TransactionA, view the current rows in HotelBookings:

    SELECT * FROM HotelBookings;

    The new row is visible from the current transaction even though it is not yet committed.

  8. Switch to TransactionB:

    set connection TransactionB;

  9. Try to view data in the table:

    SELECT * FROM HotelBookings;

    Quiz: Will you be able to view data in the table?

    Answer: No. TransactionA now has an exclusive, nonshareable lock on data in the table.

Commit TransactionA

  1. After the transaction times out, return to TransactionA and commit the transaction:

    set Connection TransactionA;

    commit;

  2. Switch to TransactionB:

    set connection TransactionB;

  3. Now try to view data in the table:

    SELECT * FROM HotelBookings;

    TransactionB can now view data in HotelBookings, and the new (committed) row is visible.

Repeat the Previous Tasks Using READ_COMMITTED

In the previous two tasks, you were working in the TRANSACTION_SERIALIZABLE isolation level, the most restrictive in terms of concurrency. In this task, you will go through some of the same steps in the READ_COMMITTED isolation level to see how the system allows greater concurrency.

  1. Commit TransactionB:

    commit;

  2. Change the isolation level for this transaction to READ_COMMITTED.

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

  3. Switch to TransactionA, and change its isolation level to READ_COMMITTED also:

    set connection TransactionA;

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

  4. Select all the data out of the table:

    SELECT * from HotelBookings;

  5. Switch to TransactionB and try to insert data into the table:

    set connection TransactionB;

    INSERT INTO HotelBookings VALUES
    (100, 2, DATE'1998-01-01', DATE'1998-01-05', 3, 900.00);

    Success! The first time you tried this, Cloudscape did not allow you to insert data into the table. This time, it does. TransactionA does not have a lock on all the data in the table, because it has already finished stepping through the results in the SELECT (in the read committed isolation level).

  6. See if TransactionA can see the new row:

    set connection TransactionA;

    SELECT * FROM HotelBookings;

    Quiz: Will you be able to view data in the table?

    Answer: No. TransactionB now has an exclusive, nonshareable lock on data in the table that TransactionA is trying to view.

    When TransactionA tries to view all the data, it cannot get a lock on the data it is trying to view.

  7. After the transaction times out, try to view only specific rows:

    SELECT * FROM HotelBookings WHERE hotel_id > 150;

  8. Commit both transactions:

    commit;

    set connection TransactionB;

    commit;

Get Cloudscape to Automatically Roll Back a Transaction

A transaction that gets a deadlock error receives an SQLException with a transaction severity. Cloudscape automatically rolls back the entire transaction, not just the statement.

  1. In TransactionA, insert a row into the Countries table:

    set connection TransactionA;

    INSERT INTO COUNTRIES VALUES ('Bhutan', 'BH', 'Asia');

  2. Check to see if the (uncommitted) insert worked:

    SELECT * FROM Countries WHERE Country LIKE 'B%';

    Bhutan should appear. The current transaction is allowed to see its own uncommitted changes.

  3. Switch to TransactionB:

    set connection TransactionB;

  4. Insert a row in the HotelBookings table:

    INSERT INTO HotelBookings VALUES
    (105, 2, DATE'1998-01-01', DATE'1998-01-05', 3, 900.00);

  5. Switch back to TransactionA:

    Set Connection TransactionA;

  6. Try to view data in the HotelBookings table:

    SELECT * FROM HotelBookings;

    ij will not respond for quite some time (35 seconds). Cloudscape is preventing TransactionA from reading data currently being updated (held in an exclusive lock) by TransactionB.

    The timeout causes Cloudscape to abort the transaction with a rollback. It therefore implicitly begins a new transaction.

  7. Without changing connections, check to see if the first insert (into the Countries table) was rolled back:

    SELECT * FROM Countries WHERE Country LIKE 'B%';

    Bhutan should not appear; since that insert statement was part of the transaction that got an error of transaction severity, it was rolled back, along with the rest of the transaction.

Work With the LockTable VTI

In multi-connection systems, it is sometimes useful to be able to get information about locks. Remember VTIs? (you learned about those in Lesson 8, "Virtual Tables, External Data, and Aggregates"). Cloudscape provides a built-in VTI class, COM.cloudscape.vti.LockTable (aliased as LockTable) that contains information about locks.

To make things easier, toursDB contains a view that selects from this vti. This view is called Locks. In this task, you can simply select from the view instead of having to instantiate the VTI.

  1. Switch back to transactionB, and commit.

    set connection transactionB;

    commit;

  2. Now switch back to transactionA.

    set connection transactionA;

  3. Insert a new row into HotelBookings:

    INSERT INTO HotelBookings VALUES
    (102, 2, DATE'1998-01-01', DATE'1998-01-05', 3, 900.00);

  4. Now let's look at the locks on the HotelBookings table. Execute the following SQL-J statement:

    SELECT XID, TYPE, MODE, LOCKNAME, STATE, INDEXNAME
    FROM LOCKS WHERE TABLENAME = 'HOTELBOOKINGS';

    The output should show something like this (the actual transaction XIDs and locknames will be different):

    XID

    Type

    Mode

    Lockname

    State

    Indexname

    1691

    TABLE

    IX

    Tablelock

    GRANT

    NULL

    1691

    ROW

    X

    (2,10)

    GRANT

    NULL

    This output shows one lock of type row on the table, held by transaction id 1691 (the current transaction, transactionA). The mode of this lock is X (which stands for exclusive). That makes sense, because the transaction just inserted a single row. (Your output may show two row locks on the table; sometimes inserts lock the previous row as well).

    It also shows a lock of type table and mode IX on the table. IX stands for intent exclusive; the transaction does not hold an exclusive lock on the table; this intent lock is there as a placeholder to block any other transactions from getting a table lock on the table.

    It also shows an indexname of NULL (meaning no index was used). That makes sense, too, because INSERT statements do not use indexes.

  5. Go back to transactionB and update a single row:

    set connection transactionB;

    UPDATE HOTELBOOKINGS SET DEPARTURE=DATE'1998-01-06' WHERE hotel_ID=105 AND group_id=2 AND ARRIVAL=DATE'1998-01-01';

  6. Now look at all the locks on the HotelBookings table:

    SELECT XID, TYPE, MODE, LOCKNAME, STATE, INDEXNAME
    FROM LOCKS WHERE TABLENAME = 'HOTELBOOKINGS';

    Your output should show something like the following (keeping in mind that it could show one more row lock for the first transaction):

    XID

    Type

    Mode

    Lockname

    State

    Indexname

    1691

    TABLE

    IX

    Tablelock

    GRANT

    NULL

    1692

    TABLE

    IX

    Tablelock

    GRANT

    NULL

    1691

    ROW

    X

    (2,12)

    GRANT

    NULL

    1691

    ROW

    X

    (2,9)

    GRANT

    NULL

    1692

    ROW

    X

    (2, 19)

    GRANT

    NULL

    Note two new rows for the UPDATE statement: a different transaction id (it's a different transaction); a new IX Tablelock; a new X row lock (on a different row). Your output may or may not show an indexname. The Update statement is eligible to use an index; in our case, the optimizer did not use the index because the table was so small.

  7. Rollback both transactions and exit ij.

    rollback;

    set connection transactionA;

    rollback;

    exit;