I compared the method of using PostgreSQL’s hidden column ctid to implement optimistic locking and the new column version to implement optimistic locking. I found that using ctid would fail, but I couldn’t figure out the reason.

PostgreSQL version: 15.4
OS: Windows 10

The following are the test steps:

[Test 1] use ctid column

1. Create test table.

create table public.test_optimistic_lock (
    id bigserial primary key,
    name varchar,
    rest_count integer,
    update_user varchar,
    update_time timestamp without time zone,
    create_time timestamp without time zone
);

insert into public.test_optimistic_lock (name, rest_count, update_time, create_time) values
('Product1', 1, current_timestamp, current_timestamp);

2. Open transaction in window A and query the ctid and rest_count of Product1, ctid = (0,1) and rest_count = 1.

begin;
select ctid, rest_count from public.test_optimistic_lock where name = 'Product1';

3. Open transaction in window B and query the ctid and rest_count of Product1, ctid = (0,1) and rest_count = 1.

begin;
select ctid, rest_count from public.test_optimistic_lock where name = 'Product1';

4. Execute UPDATE statement in window A. (Result: UPDATE 1)

update public.test_optimistic_lock
set rest_count = rest_count - 1,
    update_user = 'A',
    update_time = current_timestamp
where name = 'Product1' and ctid = '(0,1)';

5. Execute the UPDATE statement in window B, and the transaction enters the waiting state.

update public.test_optimistic_lock
set rest_count = rest_count - 1,
    update_user = 'B',
    update_time = current_timestamp
where name = 'Product1' and ctid = '(0,1)';

6. Execute COMMIT statement in window A.

7. Check the B window and it shows that the Update is successful. (Result: UPDATE 1)

8. Execute COMMIT statement in window B.

9. Check the table public.test_optimistic_lock and find ctid = (0,3), rest_count = -1, update_user = ‘B’. The data of Product1 has been updated repeatedly.

[Test 2] use version column

1. Create test table.

create table public.test_optimistic_lock (
    id bigserial primary key,
    version integer default 1,
    name varchar,
    rest_count integer,
    update_user varchar,
    update_time timestamp without time zone,
    create_time timestamp without time zone
);

insert into public.test_optimistic_lock (name, rest_count, update_time, create_time) values
('Product1', 1, current_timestamp, current_timestamp);

2. Open transaction in window A and query the ctid and rest_count of Product1, version = 1 and rest_count = 1.

begin;
select ctid, version, rest_count from public.test_optimistic_lock where name = 'Product1';

3. Open transaction in window B and query the ctid and rest_count of Product1, version = 1 and rest_count = 1.

begin;
select ctid, version, rest_count from public.test_optimistic_lock where name = 'Product1';

4. Execute UPDATE statement in window A. (Result: UPDATE 1)

update public.test_optimistic_lock
set rest_count = rest_count - 1,
    update_user = 'A',
    update_time = current_timestamp
where name = 'Product1' and version = 1;

5. Execute the UPDATE statement in window B, and the transaction enters the waiting state.

update public.test_optimistic_lock
set rest_count = rest_count - 1,
    update_user = 'B',
    update_time = current_timestamp
where name = 'Product1' and version = 1;

6. Execute COMMIT statement in window A.

7. Check the B window and it shows that the Update is successful. (Result: UPDATE 0)

8. Execute COMMIT statement in window B.

9. Check the table public.test_optimistic_lock and find ctid = (0,2), rest_count = 0, update_user = ‘A’. The result is consistent with optimistic locking.

I think the hidden column ctid is the version of the data row, which is the same as the version column I created. But I don’t understand why optimistic locking cannot be achieved through ctid?