Implementing Optimistic Locking in Oracle

Question

I am confused about the subject of locking in Oracle. As far as my research has lead me, you can use the FOR UPDATE NOWAIT/WAIT to lock rows.

I want to implement my locking this way. Once I issue the FOR UPDATE, the row will then be locked and I can check for mutations. I have a versionNumber column that increments by 1 everytime the table is updated. Can i use this versionNumber to verify the row has or hasn't mutated? something like

if (:new.versionNum != :old.versionNum) raise_application_error(20000, 'Mutated'); end if;

My question is where do I go about actually writing the line of code FOR UPDATE? I've made a small GUI to handle changing first names and saving them back to the database. Is this done in a trigger on oracle on that table or on the side of my JDBC client?

Any clarification would be nice!

Thanks

Answer 1

There are two general approaches to locking.

First, you have pessimistic locking. In this approach, you lock the row (SELECT ... FOR UPDATE) which prevents anyone else from changing the row. Then you do the UPDATE. When you commit your change, the lock is released. There is no need in this case to have a version number/ timestamp column (at least not to support locking) and the code is relatively easy.

The downside to pessimistic locking is that you need to hold the lock the entire time a user is sitting on a page potentially editing data. This is technically really hard if you're building a web-based application since HTTP is a stateless protocol. The request that initially renders the page would normally get a connection from the connection pool, do the SELECT, and then return the connection to the pool once the page was done. The subsequent request to update the data would generally happen on a different connection with a different database session so you can't lock the row in the first session and update it in the second. If you wanted to pessimistically lock the row, you'd need to do a lot of work on the back end to ensure that the one database connection was tied to a particular middle tier session until the user was done editing the data. This generally has very negative impacts on scalability and introduces all sorts of session management problems-- how do you know, for example, whether I requested a page, locking a row, and then closed my browser without ever logging out or making a change? How long are you going to leave the record locked in the database? What happens if some other session is trying to lock the row? How long are you going to let that session block waiting for a lock if the first person went out to lunch? Generally, people don't implement pessimistic locking in web-based apps because managing sessions and session state is just too impractical.

The second option is optimistic locking. In this approach, you add a version number/ timestamp to the row. You select this version number/ timestamp when you query the data. Then you use this in your WHERE clause when you later do the update and check how many rows were actually modified. If you modify exactly one row, you know the row hasn't changed since you read it. If you modify 0 rows, you know that the row did change and you can handle the error.

So, for example, you'd select the data along with the version number

SELECT address_line1, city, state, zip, version
  FROM addressTable
 WHERE address_id = `<<some key>>`

When you were ready to do the update, you'd do something like this where you use the version in your UPDATE and throw an error if the row changed

UPDATE addressTable
   SET address_line1 = `<<new address line 1>>`,
       city = `<<new city>>`,
       state = `<<new state>>`,
       zip = `<<new zip>>`,
       version = version + 1
 WHERE address_id = `<<some key>>`
   AND version = `<<version you read initially>>`

IF( SQL%ROWCOUNT = 0 )
THEN
  -- Darn.  The row must have changed since you read it.  Do something to
  -- alert the user.  Most likely, the application will need to re-query the
  -- data to see what the address has been changed to and then ask the user
  -- whether they want to re-apply the changes.
  RAISE_APPLICATION_ERROR( -20001, 'Oops, the row has changed since you read it.' );
END IF;

Your application would then do something useful with the error. Normally, that would mean doing something like querying the data again, presenting the changes to the user, and asking them whether they still wanted to apply their changes. If, for example, I read an address and start editing it, go to lunch, my colleague logs in, reads the same address, makes some edits and saves it, then I return and try to save my changes, it would generally make sense to show me something telling me that my colleague already changed the address to something new-- do I want to continue making edits or do I want to abandon them.