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)