<><>UPDATE:
见评论,这似乎是MySQL.5确定的,这些例子中,我们仍然有一张桌锁,下锁指数不能ool,AFAIK。
www.un.org/Depts/DGACM/index_french.htm
昨天,我就MVCC的可核查模式因诺德也提出了问题。
因此,我做了一些<>测试>。 MySQL 5.1.37。 http://www.postgresql.org/docs/9.0/static/transaction-iso.html#MVCC-SERIALABILITY"rel=“noreferer”>postgrESQL 9.0 MVCC 文件,载于本章。 如果不实行预先锁定,那么,我们就可以看到MVCC模式在序列可承受性方面的局限性。
因此,请在我的SQL上测试它:
CREATE TABLE t1 (
class integer,
value integer
) ENGINE=InnoDB;
INSERT INTO t1 (`class`,`value`) VALUES
(1,10),
(1,20),
(2,100),
(2,200);
现在,我们将打开两个不同的联系,以便进行两个平行交易(T1和T2):
T1:
SET TRANSACTIOn ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT SUM(value) FROM t1 WHERE class = 1;
结果30。
SET TRANSACTIOn ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT SUM(value) FROM t1 WHERE class = 2;
结果为300。
现在是一系列问题。 如果T1插入一行,使T2的甄选无效(T2相同)。
T1:
INSERT INTO t1 (`class`,`value`) VALUES (2,30);
=> 等候(有锁)
INSERT INTO t1 (`class`,`value`) VALUES (1,300);
=> ERROR 1213 (40001):在试图锁闭时发现的死锁;试图重新启动交易
T1现在成功插入,t2有ROLBACK,od seriesizability。
This would fail on PostgreSQL 9.0 (things are changing on 9.1, but it s another problem).
In fact only one of the transactions can perform an insert on the table. Even if we try to insert on class=3 with.
INSERT INTO t1 (`class`,`value`) VALUES (3,30);
We would see a waiting lock, and deadlocks in case of problems. Looks like we have a predicate locking in MySQL...
But in fact it s a next-key locking implementation in InnoDB.
因诺德表演了锁锁锁,有些空白被锁定,还有指数。 在这里,我们没有表象我的SQL决定打上桌子。
因此,请尝试测试下一个关键锁,看看这一可强制执行的序列。 首先收回交易(T1)。 然后制定指数。
CREATE index t1class ON t1 (class);
现在考验。 <>Success,序号仍予执行。 好消息。
但是,随着指数的形成,我认为,下锁和连续锁在指数上。 这意味着,如果不影响平行交易,我们就应当能够加入。
T1:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT SUM(value) FROM t1 WHERE class = 1;
结果30。
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT SUM(value) FROM t1 WHERE class = 2;
结果为300。
Here we gonna make an unrelated insert on T1, now that we have an index this will succeed:
T1:
INSERT INTO t1 (`class`,`value`) VALUES (3,30);
两者都可以执行正常的“(如果我只做一个)”字。 采用预设锁定吨数,在<代码>}=3上未提出过电离层查询。 如果我们提供良好的指数,像下一个关键锁一样,效果较好(没有表锁插入)。
Now we try to insert on the next-key lock, On a row matching selection of T2 (class=2):
T1:
INSERT INTO t1 (`class`,`value`) VALUES (2,30);
Ouch。 ∗ ∗∗
INSERT INTO t1 (`class`,`value`) VALUES (1,300);
=>等候。 那里仍然有锁。 希望。
T1:
COMMIT;
(where the lock has gone, the insert is made)
SELECT SUM(value) FROM t1 WHERE class = 2;
COMMIT;
Still having 300 here. Seems the serializability has gone.
select * from t1;
+-------+-------+
| class | value |
+-------+-------+
| 1 | 10 |
| 1 | 20 |
| 2 | 100 |
| 2 | 200 |
| 3 | 30 | <-- test
| 2 | 30 | <-- from trans1
| 1 | 300 | <-- from trans2 ERROR!
+-------+-------+
Result: By inserting a new unrelated row before inserting a row impacting a parallel transaction query we have spoofed the next-key locking mechanism. Or at least this is what I understand from my tests. So I would say, do not trust the engine for true serializability. When you have aggregates functions in a transaction the best thing is to manually lock the table, transform your serializability problem in a real only-one-guy situation, no surprises! Other serializability problems in examples are constraint verifications (check that the amount is still positive after your operation), do you own locks on these cases as well.
