Re: Executing transaction with serializable isolation level

I am trying to do sth similar with different versions but the problem remains the same, did you find a way to overcome this issue?

On Monday, January 25, 2010 9:26:30 PM UTC+1, Paweł Roman wrote:

Hi everyone

I'm using django with Postgres 8.3, and have just one quesiton: Is it
possible to make django use SERIALIZABLE isolation level in given
block of code?

Imagine you have a table with some error messages, table has 2
columns: message_text (text) and count (integer). It is supposed to
work in a very simple way: whenever a message is saved, a check is
made to see if a record with the same message_text already exists. If
yes - the count value is increased. If not - the new record is
created. Very simple logic :)

My first way to implement this was quite straightforward: I overloaded
save() on the model, and tried save() catching for IntegrityError (I
have unique constraint on message_text). If this error was raised, I
did get() to get the existing record with the same message,
incremented count and save()-d.

This method worked (I even made some unit tests that proved it), but I
quickly realized it was not good enough: I wrote another test with
multiple concurrent threads creating record with the same message_text
value and calling save(). It turned out my oiginal implementation was
naive. Few concurrent threads can detect IntegrityError and get()
existing record, each of them will increment the counter by one from
the same value (which is wrong). Obviously, both the get() and save()
(when IntegrityError was caught) must be in one critical section to
prevent that.

The problem is - I cannot use critical sections. This is because I
will have few application instances running the same code
(communicating with one database). In my case, the only level to
actually control the concurrency is database level. Thus, to prevent
that no other transaction cuts in between SELECT (django's get()) and
UPDATE (django's save()) - I have to use serializable isolation level.
Using this level I can detect 'dirty updates' (by catching
psycopg2.extensions.TransactionRollbackError) and repeat the attempt
until I succeed.

I tried to do that, but only with very limited success. It seems that
django (both in unit unit tests and in web application views) always
starts off with creating the transaction using default isolation level
(which is read commited). So, in order to execute ANY query on the
database using serializable - I have to close (commit) this django's
transaction first. If I do this - I'm screwed, for millions of reasons
(believe me, I've tried).

Anyway, back to the original question: does django have some secret
way to execute a piece of code e.g. get() then save() as one
transaction using serializable level and catch the dirty update errors
without interfering with other django transactions?

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/137b9b79-4c48-451b-8b14-323b1840f103%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.