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.
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
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 LockingIf 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. |
EAN: 2147483647
Pages: 125