#9964: Transaction middleware closes the transaction only when it's marked as
dirty
---------------------------------------------------+------------------------
Reporter: ishirav | Owner:
mtredinnick
Status: assigned | Milestone: 1.1
Component: Database layer (models, ORM) | Version: 1.0-beta-1
Resolution: | Keywords:
transactions
Stage: Accepted | Has_patch: 0
Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------
Comment (by shai):
I suspect jdunck's description is inaccurate, and it is not committing a
transaction that has an overhead, but closing the transaction. That is,
for select-only transactions, whether you commit or roll back, it costs
about the same; the "cheap" option is to leave the transaction be.
I ran some very basic tests on my desktop, to evaluate the costs of
closing transactions. The bench.py file I just attached has functions to
create connections into SQLite, MySQL, and PostgreSQL databases I had set
up; to create a simple table; to fill it up with 100,000 rows; to run some
(<100) selects as warm-up; and to run a big bunch of selects -- about 1450
batches of 3, each batch ending with a "transaction ending" operation. I
tested for the three databases, against the three operations: no-op,
rollback and commit. The results, in milliseconds, were:
|| '''Database''' || '''PostgreSQL'''|| '''MySQL'''|| '''SQLite'''||
|| '''Module''' || ''psycopg2'' || ''MySQLdb''|| ''sqlite3'' ||
|| '''Leave open'''|| 455-576 || 356-366 || 309-402 ||
|| '''Rollback''' || 569-750 || 395-414 || 297-359 ||
|| '''Commit''' || 573-717 || 397-426 || 298-305 ||
These results support my suspicion, that rollback does not save much (if
any) overhead, compared to commit, in select-only transactions.
Assuming this evidence is accepted, then, the only question that remains
is whether Django should always close transactions. The arguments I am
aware of are:
* Leaving dangling transactions is "impolite", and in some cases can lead
to data loss (lost commits as well as bad commits)
* Closing every transaction costs.
The table above shows that, for select-only transactions, this would
cost about 10% performance on MySQL and about 20% on PostgreSQL (see note
below for SQLite, and I have no access to Oracle or other common DBMSs at
the moment). These numbers are, of course, expected to change wildly with
the introduction of multi-threading, different transaction isolation
levels, and realistic workloads. However, one should note that the single-
threaded case tends to hide the benefits of shorter transactions; I expect
the 'commit' option to look better in realistic conditions.
Notes:
1. After preparing the databases with the functions in the file, I ran
all my tests with
{{{
$ python -m timeit -v -n 10 -r 10 -s 'from bench import *' -s
'conn=sqlite()' -s 'warm_up(conn)' 'leave_open(conn)'
}}}
Replacing the connection function and the benchmarking function, of
course. All the databses use the system default options; the system is
32-bit Debian sid running on a quad-core 2.4GHz cpu, with 2GHz memory, a
consumer-grade 250G hard-disk, and 3 KDE sessions up (though only one is
active).
1. The SQLite results seem to indicate that having long transactions
increases the performance variance; I found this surprising, and repeated
the tests several times with similar results, though this is certainly not
rigorous.
--
Ticket URL: <http://code.djangoproject.com/ticket/9964#comment:6>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
