Обсуждение: Row-Level Locking?
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
On 22 May 2001, Greg Zoller wrote: > 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. > > 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. You might consider using select for update on the rows you want to modify before the modification which will grab a row lock and force later transactions to wait for the lock to be released. You have to be careful that you don't deadlock yourself if you need to be grabbing locks on multiple tables.
gzoller@hotmail.com (Greg Zoller) writes: > 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? See http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/mvcc.html SELECT FOR UPDATE and/or the implicit row lock done by UPDATE should solve your problem. regards, tom lane