Skip to Main Content
  • Questions
  • Will data returned from "select for update" once row lock is obtained always contain the latest committed values?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Matthew.

Asked: September 16, 2016 - 8:37 am UTC

Last updated: September 17, 2016 - 3:01 pm UTC

Version: 11.2.0.3.0

Viewed 1000+ times

You Asked

Hello

Hopefully a quick question to confirm my understanding of "select for update" and data consistency.

I have a primary JDBC thread with autocommit false that begins a transaction, selects a batch of row "for update skip locked", and recurses through the results passing the IDs of the locked rows to an intermediary object which allocates the jobs to a set of worker threads.

If the job row can be assigned to a thread, the intermediary which is using the same JDBC connection as the primary thread updates the specified row (which is locked) and changes the status column from "New" to "Queued".

Meanwhile, when the worker thread runs(), it immediately attempts to do a select * from t where jobid=X "for update" on the specified row with a reasonable size timeout due to the primary JDBC thread not having committed as yet to release the row locks. Each worker thread uses a separate JDBC Connection.

Can I be assured that when the primary JDBC thread finally commits, that the worker thread upon obtaining the row lock will see the updated status value for the row of "Queued" (assuming nothing else can modify it)?

Is there any way for the worker thread upon obtaining the row lock to see the original value "New" rather than the committed value "Queued" assuming the primary committed successfully?

many thanks

Matt Shannon.

and Connor said...

A 'select for update' will see the *current* state of the data, not a consistent (ie, potentially out of date) view of the data.

I'm curious - why would you want a worked threader to see "New" ? You could *infer* it, ie "I successully locked a row marked as Queued, so by definition it must have been New".

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Matthew Shannon, September 17, 2016 - 6:53 am UTC

Thanks for the help.

I want (and logically expect) the worker thread to see the value "Queued" once it gets hold of the lock.

I just wanted to double check with DB experts there was no way the worker thread could see the value "New". Such that when the lock released from the primary thread due to its commit, that there was no way the worker thread upon immediately obtaining the lock could somehow see the value "New" value rather than the committed "Queued" value.

thanks again for your help.
Connor McDonald
September 17, 2016 - 3:01 pm UTC

If you search this site for "write consistency" there's some interesting information about how we handle all of this.