id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
3460	Allow configuration of postgresql_psycopg2 isolation level	Jack Moffitt <metajack@…>	Malcolm Tredinnick	"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."		closed	Database layer (models, ORM)	dev		fixed		sam@… jarek.zgoda@… richard.davies@… chenyuejie@… guillermo.gutierrez@…	Accepted	1	0	0	0	0	0