Optimistic locking with postgresql blocking due to locks

Question

I'm trying to use optimistic lock in postgresql but it seems I'm misunderstanding how it should work. I thought that if I used 'serializable' isolation level, each transaction would act as if the other transactions didn't exist and just at commit time any checking would be done and the transaction would eventually abort. However, in the test I'm doing, the transactions are affecting others, in the sense that one transaction might block in a lock. This is the test I'm doing:

First, create the db:

CREATE USER myuser WITH PASSWORD '1234';
CREATE DATABASE tempdb;
GRANT ALL PRIVILEGES ON DATABASE tempdb to myuser;
\connect tempdb;
CREATE TABLE temptable (
    id        integer PRIMARY KEY,
    name       varchar(40) NOT NULL
);
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

Then, create two python programs.

The first one will try to insert a row and then will sleep 10 seconds before committing the transaction:

import time
import psycopg2

def main():
    conn = psycopg2.connect(dbname='tempdb', host='localhost', user='myuser', password='1234')
    cur = conn.cursor()
    cur.execute("begin transaction isolation level serializable")
    cur.execute("insert into temptable (id, name) values (1, 'my name');")
    time.sleep(10)
    cur.execute("commit")
    print('test finished')

if __name__ == '__main__':
    main()

The second will try to do almost the same, however it won't sleep for 10 seconds:

import time
import psycopg2

def main():
    conn = psycopg2.connect(dbname='tempdb', host='localhost', user='myuser', password='1234')
    cur = conn.cursor()
    cur.execute("begin transaction isolation level serializable")
    cur.execute("insert into temptable (id, name) values (1, 'my other name');")
    cur.execute("commit")
    print('test finished')

if __name__ == '__main__':
    main()

If I run temp1.py and then, in other window, temp2.py, temp2.py will hang until temp1.py commits. But, what I would expect is that temp2.py would insert the row, commit and then, when temp1.py tries to commit, it would get an error.

Am I doing anything wrong? Is it possible to do what I want in postgresql?

(I'm using postgresql 10.12)

Answer 1

Q: However, in the test I'm doing, the transactions are affecting others, in the sense that one transaction might block in a lock.

Concurrent transactions not affecting each other at the serializable isolation level does not mean that any one transaction does not lock any other. Locks are inevitable in databases as soon as concurrent transactions work on the same data.

Not affecting each other means that the result of any particular transaction does not differ from what it would be if other transactions were not working concurrently with the same data.

The time it takes to complete a transaction, or whether it had to wait for locks to be released do not count as results. Results are rows and values that are read or written or returned to the caller.

Q: But, what I would expect is that temp2.py would insert the row, commit and then, when temp1.py tries to commit, it would get an error.

The error concerns a violation of a unique constraint. Constraint checks can be deferred or immediate, independently of the isolation level, and they're immediate by default.

If the unique column was declared as id integer PRIMARY KEY DEFERRABLE INITIALLY DEFERRED, the INSERT in transaction #2 would not hang, but transaction #2 would hang at COMMIT time until transaction #1 commits or aborts.

Q: Is it possible to do what I want in postgresql?

If you want the second transaction (the one that inserts in second) to be guaranteed to succeed because it's faster to commit than the other one that inserted first, I don't think it's possible. From the point of view of the serializable isolation logic, it doesn't really matter which transaction fails. Any transaction may fail and the caller must always be prepared to react appropriately according to SQLSTATE (the error code).