how to do column-level locking? Is that possible?

Question

Let's say I have a table as below:

+----+------+--------+
| ID | NAME | STATUS |
+----+------+--------+
|  1 | ADAM | ACTIVE |
|  2 | EVE  | ACTIVE |
|  3 | JOHN | ACTIVE |
+----+------+--------+

Let's say I want to do column-level locking - the transaction abort if other transaction modify the value of the same column, e.g

+----+------+--------+
| ID | NAME | STATUS |
+----+------+--------+
|  1 | ADAM | ACTIVE | <- OK: Tx1: change NAME to ACE, Tx2: change STATUS to INACTIVE
|  2 | EVE  | ACTIVE | <- Abort: Tx1: change NAME to CAROL, Tx2: change NAME to CAT
|  3 | JOHN | ACTIVE | <- OK, same value: Tx1: change NAME to JAN, Tx2: change NAME to JAN
+----+------+--------+

What lock or isolation level I need to set?

1

You can only do row level locking. – Jul 25 '20 at 08:00

score 0 · Answer 1 · answered Jul 25 '20 at 16:16

You can't lock individual column values natively. You could make multiple tables with a one to one relationship; or you could roll your own optimistic locking:

select name from t where id=2; -- get Eve
update t set name='Carol' where id=2 and name='Eve' returning id;
-- if no rows updated, rollback and throw an error.

This would not consider it an error if other session(s) changed the value from Eve to Cat then back to Eve again in between the first two lines shown.

Are these two separate transactions or wrapped. in one transaction? — janetsmith, Jul 28 '20 at 21:34

how to do column-level locking? Is that possible?

1 Answers1