Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Concurrent UPDATE...WHERE behaviour

 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • 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.
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • 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
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic