Allow configuration of postgresql_psycopg2 isolation level
|Reported by:||Owned by:||Malcolm Tredinnick|
|Component:||Database layer (models, ORM)||Version:||master|
|Cc:||sam@…, jarek.zgoda@…, richard.davies@…, chenyuejie@…, guillermo.gutierrez@…||Triage Stage:||Accepted|
|Has patch:||yes||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
Postgresql only has two transaction isolation levels: read committed and serializable. READ COMMITTED is the default and the one django expects to use. psycopg2 however defines three isolation levels: autocommit, read committed, and serializable. The difference between autocommit and read committed is that read committed in psycopg2 puts all statements inside a BEGIN/END block (or BEGIN/ROLLBACK or BEGIN/COMMIT). Inside the BEGIN it also executes SET TRANSACTION ISOLATION LEVEL READ COMMITTED (which is redundant as this isolation level is the default).
Django currently uses psycopg2's read committed isolation level, and this leads to every query (almost) being surrounded by spurious BEGIN/END. In a heavily loaded system, this consumes a massive amount of the total database time.
Django should be using the autocommit isolation level, which does not have this overhead. Anywhere explicit transaction blocks are needed they can be used. The attached patch switches the backend to the autocommit isolation level. I have tested this patch on our production system and it makes a noticeable speed improvement.
NOTE: Django's ORM does an existence test followed by and UPDATE or INSERT when save() is called. The read committed isolation level does not protect against this failing even within a transaction block, and therefor this change should have no side effects on Django code.
Change History (55)
comment:22 Changed 8 years ago by
|Owner:||changed from Collin Grady to Richard Davies <richard.davies@…>|
comment:26 Changed 8 years ago by
|Summary:||postgresql_psycopg2 backend uses wrong isolation level → Allow configuration of postgresql_psycopg2 isolation level|
|Triage Stage:||Design decision needed → Accepted|
comment:35 Changed 8 years ago by
|Owner:||changed from Richard Davies <richard.davies@…> to Malcolm Tredinnick|