Optimistic Locking


Optimistic locking is locking at COMMIT time rather than locking during the course of a transaction. With optimistic locking, conflicts don't cause waits, they cause rejections. This is indeed optimistic, because you must pray that trouble will be rare. Let's look at how you can handle a common database application requirement with optimistic locking.

The requirement is that you must display an on-screen form giving existing database information, like an address and phone number. A user will edit the information on the form, and you must write the edited information back to the database. Nothing remarkable about that except that the user can take forever to fill in the form. Be realisticyou can't lock for the whole time this may take.

Oracle Locking

To avoid misleading you, we once again stress that a versioning DBMS like Oracle doesn't handle concurrency in the manner we've been describing throughout this chapter. Here is a summary of the major differences in method.

SHARED and UPDATE LOCKS: Oracle won't need them. You can explicitly ask for shared locks with Oracle's non-standard SQL-extension LOCK TABLE statement, but typical transactions get only exclusive locks. These locks are done with marks on the wall, not with RAM records.

ESCALATION: Oracle won't do it. All automatic exclusive locks have row-level granularity.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED: Oracle won't actually support READ UNCOMMITTED. Instead, it upgrades to a higher level.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED: Oracle supports READ COMMITTED by exclusive-locking writes and by using the log to read data rows "as at start of SQL statement."


SET TRANSACTION ISOLATION LEVEL SERIALIZABLE: Oracle supports SERIALIZABLE by exclusive-locking writes and by using the log to read data rows "as at start of transaction."

SET TRANSACTION READ ONLY: Oracle supports READ ONLY transactions by using the log to read data rows "as at start of transaction"which in effect means that a READ ONLY transaction will effectively be at "repeatable read" isolation level.

To handle this situation, your address records need to contain two fields in addition to the address information itself: a unique row identifier and a unique serialized transaction identifier. Let's use a GUID for the unique row identifier and a timestamp for the unique serialized transaction identifier. (In this case, the timestamp has nothing to do with date plus time, it's just a number that shows us the order of transactions. With Microsoft this is the TIMESTAMP field that goes in each row automatically, with Oracle you can generate a timestamp with a SEQUENCE object, and with other DBMSs you can do your own generation with a stored procedure or use a "serial" data type as shown in Table 7-10 in Chapter 7, " Columns .") A transaction with a higher serial number is " younger " than another transaction.

Here's how the system works. Whenever you INSERT or UPDATE, you also set the timestamp field to equal the transaction's current timestamp, and whenever you SELECT, you select the unique identifier and the timestamp only. In both cases, you then examine the timestamp to see if a younger transaction has changed the row. If that has happened , you ROLLBACK the transaction and ask the user to try again.

Let's try it out. Assume you have a table called Addresses , with these columns: guid_column, timestamp_column, address, phone . The Addresses table is already populated with the rows shown in Table 15-11.

The first thing to do is get a timestamp for yourself. For the sake of simplicity, assume that the current timestamp value is in a one-row table of timestamps and that you must increment it yourself (although in a real situation you'll certainly want to use the DBMS's utility to do this). These four SQL statements accomplish this task:

 (Addresses_transaction start) SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SELECT timestamp_counter   INTO :transaction_timestamp   FROM Timestamp_table UPDATE Timestamp_table   SET timestamp_counter = timestamp_counter + 1 COMMIT 

The next step is to execute a SELECT to get the original address information for a row, using the GUID field. (You could use a non-unique field, but then you'd have to select into arrays, which would merely complicate the example.) The transaction needs three SQL statements and looks like this:

Table 15-11. Addresses Table
guid_column timestamp_column address phone
gui-apr-001 00005000 1 First Avenue 470-1111
gui-apr-062 00005000 2 Second Avenue 470-2222
gui-mar-517 00005000 3 Third Avenue 470-3333
gui-mar-118 00005000 4 Fourth Avenue 470-4444
gui-jan-176 00005000 5 Fifth Avenue 470-5555
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT guid_column,        timestamp_column,        address,        phone   INTO :address_guid,        :address_timestamp,        :address_address,        :address_phone   FROM Addresses   WHERE guid_column = 'gui-mar-517' COMMIT 

Notice the COMMIT statement. You haven't finished yet, but you have told the DBMS the transaction is over.

The third step is to put up a screen form, a Web form, or something similar for the user to edit:

 MessageBox(address_address, address_phone, MB_OK); 

When the user has finished editing the form, there will be new information in the address_address and address_phone variables . Again, in a real situation, you would now validate that the data has changed, looks like a valid address and phone number, and so on. But let's skip to the fun part. Here's the transaction and subsequent code that actually does the UPDATE:

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED UPDATE Addresses   SET timestamp_column = :transaction_timestamp,       address = :address_address,       phone = :address_phone   WHERE guid_column = 'gui-mar-517'     AND timestamp_column = :address_timestamp { Call SQLRowCount to get update_count = # of rows   updated by UPDATE statement } COMMIT IF update_count==0 THEN BEGIN   MessageBox("Row was updated while you were editing.\               Please re-try.");   GOTO (Addresses_transaction start);   END 

Notice the UPDATE statement in this transaction. The clause AND timestamp_column = :address_timestamp is subtle. If timestamp_column doesn't have the same value as it had when the row was read the first time, then the row won't be found and the number of rows updated will equal zero. The count of the updated rows is always returned automatically, and the transaction cleverly uses the automatic count so that one SQL statement both updates and tests for change. Because update_count will equal zero only if an interruption occurred, the user is then asked to do the whole thing again. Admittedly, if retries happen frequently or after the user has done lots of work, the user will be mad. But we're being optimistic, remember?

Now consider what happens if another user accesses the same row and edits the screen form at the same time. When User #2 tries to UPDATE the row, a rejection will occur because the first user has already updated and the timestamp_column therefore has the earlier transaction timestamp value.

Often the functionality of optimistic locking is hidden in the driver or in a program that generates SQL statements, so you may not have seen optimistic locking frequently, even though it's very common. One potential flaw of optimistic locking is that, if you don't define a unique field or primary key, then an SQL generator will use a WHERE clause that contains every value in the row; so make sure there's a single unique field that nobody can change.

The Bottom Line: Optimistic Locking

If everyone uses the same program, then Lost Update can't happen. Our optimistic locking example is an illustration of READ COMMITTED in a transaction that can take a very long time.


SQL Performance Tuning
SQL Performance Tuning
ISBN: 0201791692
EAN: 2147483647
Year: 2005
Pages: 125

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net