• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Concurrent UPDATE...WHERE behaviour

 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm curious about how the following scenario might behave. If any one has any ideas please post.

Scenario:
I have an application running in a clustered environment. The application uses a "listener" thread which polls a table in the DB to process records in a queue-like fashion (i.e. read a record, do something then delete the record). There will obviously be a thread per JVM - so access to this one table is open to concurrency problems. To flag to a process that a record is already being processed (so I don't get the same piece of logic being run more than once for one record) what I though I'd do is set a PID field for each record which doesn't already have one and only process those records where the thread's PID matches the record PID value. To do this I do "UPDATE [table] WHERE PID = null". I think this is OK - but to complicate the issue, I can't lock the table, and as I understand most DB platforms use row level locking for update statements.

What I'm worried about is that there may be conflicts where multiple threads issue multiple "UPDATE....WHERE [condition]" statements. Suppose thread1 starts this process and there are 100 records in the table. It gets to the 70th record then thread2 starts. The condition is basically where PID = null so at the point thread2 starts its condition matches 30 records. Does anyone know if this means that basically thread1's update will be truncated or will there be deadlocks?

Any opinions welcomed.
 
author
Posts: 3252
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Neither problem will occur. Individual database operations such as UPDATE are atomic with respect to each other. In other words, the first UPDATE will get all the rows fulfilling the WHERE condition at that time; the second one might get rows that have been added while the first UPDATE was executing. Do take care though that your PIDs are really unique - if they are Unix process IDs they are not.

Not sure that this is the best possible implementation, but it would work. What surprises me though is that you actually spend time writing plumbing like this. What's wrong with, say, JMS?

- Peter
[ July 22, 2004: Message edited by: Peter den Haan ]
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Cheers Peter. That's pretty much what I though - but I just needed clarification. PID's will come from our PK generation startegy - so will be unique.

Just so you know: JMS isn't an option because (in the context of our app) it would require XA, and not all the DBs/JDBC drivers the app needs to support support XA.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic