I create tables like this:
CREATE TABLE tgroups ( group_id text PRIMARY KEY, vacancy_id text ); CREATE INDEX tgroups_vacancy_id_idx ON tgroups (vacancy_id); CREATE TABLE tscores ( application_id text NOT NULL, group_id text NOT NULL, CONSTRAINT tscores_pkey PRIMARY KEY (application_id, group_id), CONSTRAINT tscores_group_id_fkey FOREIGN KEY (group_id) REFERENCES tgroups(group_id) ON DELETE CASCADE ); CREATE INDEX tscores_group_id_idx ON tscores(group_id);
I then have two processes executing SQL transactions in very rapid succession. Both have the standard READ COMMITTED isolation level. The first process is running this, except the values in the
INSERT...VALUES are in a random order (I’ve done it in Python):
BEGIN; DELETE FROM tgroups WHERE vacancy_id = 'vacancy1'; INSERT INTO tgroups (group_id, vacancy_id) VALUES ('group1', 'vacancy1'), ('group2', 'vacancy1'), ... ('group10', 'vacancy1'); COMMIT;
The second process runs the following SQL. Again, the values in
INSERT...VALUES are in a random order. The variable :application_id is chosen from the set [“application1″…”application10”], which I’ve done with Python (I guess you could achieve the same in pgsql):
BEGIN; DELETE FROM tscores WHERE application_id = :application_id; INSERT INTO tscores (application_id, group_id) VALUES (:application_id, 'group1'), (:application_id, 'group2'), ... (:application_id, 'group10'); COMMIT;
This sometimes deadlocks:
sqlalchemy.exc.OperationalError: (psycopg2.extensions.TransactionRollbackError) deadlock detected DETAIL: Process 527 waits for ShareLock on transaction 391860; blocked by process 526. Process 526 waits for ShareLock on transaction 391861; blocked by process 527. HINT: See server log for query details. CONTEXT: while deleting tuple (2,218) in relation "tgroups" [SQL: "DELETE FROM tgroups WHERE vacancy_id = 'vacancy1';"]
I can only reproduce the deadlock if both of the following are true:
The deletes in tscores from the first transaction are carried out behind the scenes by
ON DELETE CASCADE. If I remove the constraint and manually delete the rows in question at the beginning of the transaction there is no deadlock
The values inside the insert statement are in a random order
I was under the impression that both
INSERT...VALUES were atomic, and that
ON DELETE CASCADE happened at the same time as the DELETE it is related to. Therefore I’d have thought that if one transaction was already in process, the blocked transaction would block on
DELETE until the other transaction finished, and then continue, and indeed this is what seems to happen if I manually run the two transactions line-by-line in separate psql sessions.
Somehow I clearly misunderstand the way that locks, inserts, foreign keys and cascades interact.
NB I am aware sometimes the
INSERT into tscores will sometimes fail because the group that is referenced has just been deleted, but this isn’t my problem (though I guess it could be related).
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!