CODEWITHSUNDEEP
javapostgresqljdbctransactionsdb2
agad
agad

Reputation: 2189

Transaction in PostgreSql

I'd like to realize following scenario in PosgreSql from java:

I'd like data not be available for other users during the transaction. It would be enough if I'd get an exception when other user tries to update the table.

I've tried to use select for update or select for share, but it locks data for reading also. I've tried to use lock command, but I'm not able to get a lock (ERROR: could not obtain lock on relation "fppo10") or another transaction gets lock when trying to commit transaction, not when updating the data.

Does it exist a way to lock data in a moment of transaction start to prevent any other call of update, insert or delete statement?

I have this scenario working successfully for a couple of years on DB2 database. Now I need the same application to work also for PostgreSql.

Upvotes: 2

Views: 2014

Answers (2)

Clockwork-Muse
Clockwork-Muse

Reputation: 13106

Finally, I think I get what you're going for.
This isn't a "transaction" problem per se (and depending on the number of tables to deal with and the required statements, you may not even need one), it's an application design problem. You have two general ways to deal with this; optimistic and pessimistic locking.

Pessimistic locking is explicitly taking and holding a lock. It's best used when you can guarantee that you will be changing the row plus stuff related to it, and when your transactions will be short. You would use it in situations like updating "current balance" when adding sales to an account, once a purchase has been made (update will happen, short transaction duration time because no further choices to be made at that point). Pessimistic locking becomes frustrating if a user reads a row and then goes to lunch (or on vacation...).

Optimistic locking is reading a row (or set of), and not taking any sort of db-layer lock. It's best used if you're just reading through rows, without any immediate plan to update any of them. Usually, row data will include a "version" value (incremented counter or last updated timestamp). If your application goes to update the row, it compares the original value(s) of the data to make sure it hasn't been changed by something else first, and alerts the user if the data changed. Most applications interfacing with users should use optimistic locking. It does, however, require that users notice and pay attention to updated values.

Note that, because a lock is rarely (and for a short period) taken in optimistic locking, it usually will not conflict with a separate process that takes a pessimistic lock. A pessimistic locking app would prevent an optimistic one from updating locked rows, but not reading them.
Also note that this doesn't usually apply to bulk updates, which will have almost no user interaction (if any).

tl;dr

Don't lock your rows on read. Just compare the old value(s) with what the app last read, and reject the update if they don't match (and alert the user). Train your users to respond appropriately.

Upvotes: 2

Andomar
Andomar

Reputation: 238276

Instead of select for update try a "row exclusive" table lock:

LOCK TABLE YourTable IN ROW EXCLUSIVE MODE;

According to the documentation, this lock:

The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables). In general, this lock mode will be acquired by any command that modifies data in a table.

Note that the name of the lock is confusing, but it does lock the entire table:

Remember that all of these lock modes are table-level locks, even if the name contains the word "row"; the names of the lock modes are historical

Upvotes: 1

Related Questions