CODEWITHSUNDEEP
sqlmultithreadingoracle-database
CodeClimber
CodeClimber

Reputation: 4664

Lock Oracle row so second thread cannot read data

How do I block one thread's transaction from reading a row until another transaction has finished processing it?

I have a trigger table on an Oracle 11 DB. A row on the trigger table has a status flag to indicate whether a process needs to happen or not - let's say it's to trigger an email. there is abatch process regularly polling the trigger table. The process is:

  1. get X number of triggers where status is NOT_SENT
  2. update status of X triggers to SENDING
  3. send X emails
  4. update status of X triggers to SENT

But I am running my application in a clustered environment. So there could be multiple versions of the batch process pulling down triggers in parallel. I want to ensure that the competing batch processes do not pull down the same triggers and perform the same processing i.e. send the same email.

I am looking at the Oracle SELECT FOR UPDATE clause. However, I'm not 100% clear if this does what I want. I need thread A to lock the trigger row and thread B to be blocked from reading from or writing to the trigger row. It is not 100% clear if SELECT FOR UPDATE blocks thread B from reading and writing OR blocks thread B from writing only. I need the former.

Thoughts welcome.

Upvotes: 2

Views: 6014

Answers (2)

Justin Cave
Justin Cave

Reputation: 231851

A SELECT FOR UPDATE locks and reads the row. If thread A has locked the row and thread B tries to lock it, thread B will block until thread A releases its lock. So if both A and B are doing a SELECT FOR UPDATE, thread B will wait until A is done.

In Oracle, a reader is never blocked by a writer so other threads will be free to read the rows that A is processing. They just won't be able to lock the rows.

Of course, it rather defeats the purpose of have multiple threads if your locking mechanism turns your application into something that is functionally single threaded. You may want to look into doing a SELECT FOR UPDATE SKIP LOCKED. Here is a nice illustration of how SKIP LOCKED works.

Upvotes: 6

Stefan Yordanov
Stefan Yordanov

Reputation: 666

In PL/SQL block (a trigger is also PL/SQL block) you can use dbms_lock package.

Upvotes: 1

Related Questions