Can I have a mutex table in my database (Oracle) with just plain SQL?

Question

I would like to have a table in my Oracle database whose rows act as locks. The table would have one column, a varchar, and my clients (Java processes over JDBC) would run statements to acquire and release locks.

The acquire statement should check existence of a row with a given value and insert if not there. The statement should somehow signal to the caller whether the row was free or not.

The release statement should release the lock by deleting the row.

The release statement is straightforward, but what should my acquire statement look like?

Answer 1

You could define a primary key unique index on lockname. A client would try to:

INSERT  Locks
        (lockname)
VALUES  'MyLock'

If this generates a primary key violation, the client did not succeed in acquiring the lock.

Answer 2

I recommend you use DBMS_LOCK for this purpose. Under the hood, it pretty much does what you're suggesting.

DBMS_LOCK package