среда, 5 сентября 2012 г., 5:32:44 UTC+6 пользователь Anssi Kääriäinen
написал:
>
>
>
> On 4 syys, 17:53, lucky <e.genera...@gmail.com> wrote:
> > *E) Use locking with SELECT query*
> > According tohttp://
> dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html
> >
> > If you want to see the “freshest” state of the database, use either the
> > READ COMMITTED isolation level or a locking read:
> > SELECT * FROM t LOCK IN SHARE MODE;
>
> If I recall correctly the problem with this approach is that while you
> can read the object from the DB, you can't save it back. UPDATE and
> SELECT in .save() doesn't see the row, INSERT results in integrity
> error.
>
> I might remember this wrong, so this is something to test when trying
> this approach.
>
>
>From http://dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html :
The snapshot of the database state applies to SELECT statements within a
transaction, not necessarily to DML statements. If you insert or modify
some rows and then commit that transaction, a DELETE or UPDATE statement
issued from another concurrent REPEATABLE READ transaction could affect
those just-committed rows, even though the session could not query them. If
a transaction does update or delete rows committed by a different
transaction, those changes do become visible to the current transaction.
Therefore REPEATABLE READ is all about the SELECT results. To spy on the
results committed by another transaction you could use "SELECT .. LOCK IN
SHARE MODE". All DML statements (INSERT, DELETE, UPDATE, REPLACE, and etc.)
will be affect to committed rows in any way.
If DML statement from the current transaction changes the row, committed by
another transaction, then it becomes available among ordinary SELECT
results.
(but it seems that there is no way to hack the consistent reading with
"dummy" update at INSERT INTO t (k, v) VALUES (1,2) ON DUPLICATE KEY UPDATE
v=v; because, technically it has no affect any rows).
Session A Session B CREATE
TABLE t (k INT, v INT, PRIMARY KEY (k));
SET autocommit=0; SET autocommit=0;
time
| SELECT * FROM t;
| empty set
| INSERT INTO t VALUES (1, 2);
|
v SELECT * FROM t;
empty set
SELECT * FROM t LOCK IN SHARE MODE;
(it blocks here)
COMMIT;
---------------------
| 1 | 2 |
---------------------
1 row in set
SELECT * FROM t;
empty set
UPDATE t SET v=3 WHERE k=1;
1 row affected
SELECT * FROM t;
---------------------
| 1 | 3 |
---------------------
1 row in set
- Anssi
>
--
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/django-developers/-/PtDOS3SmpNAJ.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en.
