I am trying to circumvent double-writes by locking and unlocking certain rows.
I have a table personnel and as soon as a user tries to edit a row, I want to lock that row.
So
BEGIN;
// I guess I have to lock it somehow here
SELECT * FROM personnel WHERE id = 12;
COMMIT;
and once an edit as been made, I want to submit the UPDATE in the same style:
BEGIN;
//Unlocking
UPDATE personnel SET ...
WHERE id = 12;
COMMIT;
In the time between, when another user tries to edit the same row, he would get a message.
Locks are released at the end of a transaction. So you need to keep the transaction open to uphold the lock and do everything in a single transaction:
BEGIN;
SELECT * FROM personnel WHERE id = 12 FOR UPDATE NOWAIT; -- row level locking
COMMIT;
BEGIN;
UPDATE personnel SET ...
WHERE id = 12;
COMMIT;
Alternatively you can use the somewhat less restrictive FOR SHARE clause.
Details in the manual on SELECT in the chapter "The Locking Clause".
Concurrent reads are allowed. Quoting the chapter "Row-level Locks" in the manual:
In addition to table-level locks, there are row-level locks, which can
be exclusive or shared locks. An exclusive row-level lock on a
specific row is automatically acquired when the row is updated or
deleted. The lock is held until the transaction commits or rolls back,
just like table-level locks. Row-level locks do not affect data
querying; they block only writers to the same row.
To have concurrent attempts to do the same fail immediately, use the NOWAIT option - for all competing queries.
