#13906: REPEATABLE READ (as used by default on MySQL) breaks atleast
QuerySet.get_or_create().
-------------------------------------+-------------------------------------
Reporter: | Owner:
sebastian_noack | Status: new
Type: | Component: Database layer
Cleanup/optimization | (models, ORM)
Milestone: 1.4 | Severity: Normal
Version: SVN | Keywords: mysql transaction
Resolution: | isolation
Triage Stage: Accepted | Has patch: 1
Needs documentation: 1 | Needs tests: 0
Patch needs improvement: 1 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Comment (by mila):
MySQL manual states that:
> A SELECT ... FOR UPDATE reads the latest available data, setting
exclusive locks on each row it reads. Thus, it sets the same locks a
searched SQL UPDATE would set on the rows.
http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html
I have tested it - see bellow - and it works for me. You need to use the
FOR UPDATE statement in second select, not the first one.
Btw if SELECT FOR UPDATE is used for with the first select dead lock can
occur - see second test bellow.
=== Using FOR UPDATE with second select ===
Client 1 - selects and gets empty result set.
{{{
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE t (c INT);
Query OK, 0 rows affected (0.12 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE c=1;
Empty set (0.00 sec)
}}}
Client 2 - inserts new row
{{{
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE c=1;
Empty set (0.00 sec)
mysql> INSERT INTO t VALUES(1);
Query OK, 1 row affected (0.00 sec)
}}}
Client 1 - still selects empty result set but if for update is used, it
blocks (because Client 2 has lock on that row)
{{{
mysql> SELECT * FROM t WHERE c=1;
Empty set (0.00 sec)
mysql> SELECT * FROM t WHERE c=1 FOR UPDATE;
}}}
Client 2 - commits
{{{
mysql> commit;
Query OK, 0 rows affected (0.04 sec)
}}}
Client 1 - returns inserted result
{{{
+------+
| 1 |
+------+
1 row in set (7.03 sec)
}}}
=== Using FOR UPDATE with first SELECT ===
Client 1 - selects empty result set with FOR UPDATE option.
{{{
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE t (c INT);
Query OK, 0 rows affected (0.14 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE c=1 FOR UPDATE;
Empty set (0.00 sec)
}}}
Client 2 - selects empty result set too, trying to insert row blocks
{{{
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE c=1 FOR UPDATE;
Empty set (0.00 sec)
mysql> INSERT INTO t VALUES(1);
}}}
Client 1 - trying to insert causes dead lock
{{{
mysql> INSERT INTO t VALUES(1);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting
transaction
}}}
Client 2 - row is inserted
{{{
Query OK, 1 row affected (9.99 sec)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/13906#comment:22>
Django <https://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 django-updates@googlegroups.com.
To unsubscribe from this group, send email to
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en.
