Row-Level Locking?

From: gzoller(at)hotmail(dot)com (Greg Zoller)
To: pgsql-general(at)postgresql(dot)org(dot)pgsql-jdbc(at)postgresql(dot)org
Subject: Row-Level Locking?
Date: 2001-05-22 15:49:40
Message-ID: 4f2c4460.0105220749.c60d1c8@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-jdbc

I hope someone can help me with this problem.

I have a Java program that performs business logic operations on several
tables (reads/writes). These operations are logically one transaction,
and I protect them inside a transaction in Postgres (auto commit=false).
If something fails during processing of these operations, everything
rolls back. This works fine.

My problem is this: what happens if half-way through this set of
operations someone else (perhaps in another thread) attempts to execute
the same operation? This could corrupt my data. Other threads have no
way of knowing that someone else is already working with these tables.

I thought about using a "busy" flag field in my table, but then I'd have a
race condition with other threads unless there's some way to "test & set" the
flag in an atomic operation. Possible? I want the flag in a table rather
than the Java code because it is very likely that multiple concurrent JVMs
could be attempting to use this table simultaneously, so the database is the
point of synchronization. This is also one reason simply slapping
"synchronized" in my method won't work. (The other reason being that this
would single-thread ALL my db access rather than just the 1 table row I want
to lock.)

My desired behavior is to be able to lock a single table row and have all
other connections trying to write (and also read, if possible) this row block
until the logical operation is complete and the lock released.

Any ideas? Are there other options/solutions I haven't mentioned here?

Thanks in advance.
Greg

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Oliver Elphick 2001-05-22 15:50:56 Re: ZeroFill(.../pg_xlog/xlogtemp.20148) failed: No such file or directory
Previous Message Peter Eisentraut 2001-05-22 15:46:55 Re: psql shell problem

Browse pgsql-jdbc by date

  From Date Subject
Next Message Bruce Momjian 2001-05-22 18:03:26 JDBC commit to 7.1.2
Previous Message Bruce Momjian 2001-05-22 01:33:30 Re: Patches for Serialize.java