MonetDB table level write lock? OR it's repeatable read above isolate?

2016-03-31 1520

简介：

+关注继续查看

在测试对单个表执行insert时, 发现并行的话会导致除最早提交的事务以外的其他事务回滚.

来看个例子 :

sql>\d d
CREATE TABLE "sys"."d" (
        "id"   INTEGER,
        "info" VARCHAR(64),
        "c1"   VARCHAR(64)
);

当前d表有1条记录.

sql>select * from d;
+------+------+------+
| id   | info | c1   |
+======+======+======+
|    1 | test | test |
+------+------+------+
1 tuple (1.698ms)

开启会话1, 插入一条记录, 可以查看到有2条记录

sql>start transaction;
auto commit mode: off
sql>insert into d values (2,'test','test');
1 affected rows (0.726ms)
sql>select * from d;
+------+------+------+
| id   | info | c1   |
+======+======+======+
|    1 | test | test |
|    2 | test | test |
+------+------+------+
2 tuples (1.595ms)

开启会话2, 查看只有1条记录, 说明至少是read committed隔离级别.

sql>start transaction;
auto commit mode: off
sql>select * from d;
+------+------+------+
| id   | info | c1   |
+======+======+======+
|    1 | test | test |
+------+------+------+
1 tuple (1.299ms)

在会话2删除这条记录. 再次查看没有记录了

sql>delete from d;
1 affected rows (0.684ms)
sql>select * from d;
+----+------+----+
| id | info | c1 |
+====+======+====+
+----+------+----+
0 tuples (1.152ms)

在会话1, 还可以看到2条记录

sql>select * from d;
+------+------+------+
| id   | info | c1   |
+======+======+======+
|    1 | test | test |
|    2 | test | test |
+------+------+------+
2 tuples (0.926ms)

会话2提交, 成功

sql>commit;
auto commit mode: on

在会话1, 还可以看到2条记录, 现在有点像repeatable read或者ssi隔离级别了.

sql>select * from d;
+------+------+------+
| id   | info | c1   |
+======+======+======+
|    1 | test | test |
|    2 | test | test |
+------+------+------+
2 tuples (1.065ms)

提交会话1失败

sql>commit;
COMMIT: failed

在会话1再次查看表d, 已经没有记录了. 因为会话2删除了d的所有记录

sql>select * from d;
+----+------+----+
| id | info | c1 |
+====+======+====+
+----+------+----+
0 tuples (1.399ms)

从现象上看, 在事务过程中并没有锁冲突发生, 在事务提交的时候检测到冲突后回滚.

所以先提交的事务成功了, 后提交的事务就失败了.

这样也没有死锁的问题. 反正不会等待.

来看一下MonetDB对事务的介绍 : 用的是OCC并发控制方法, 提交时检测冲突, 所以不适合有增删改冲突的并行长事务场景.

Transactions

MonetDB/SQL supports a multi-statement transaction scheme marked by START TRANSACTION and closed with either COMMIT or ROLLBACK. The session variableauto_commit can be set to true if each SQL statement should be considered an independent transaction.

WARNING. The transaction management scheme is based on optimistic concurrency control. It provides each transaction with a consistent view on the database, but updates are collected in an addendum processed on transaction commit. If at commit time it can be assured that the data prepared for update affects tables has not changed in the mean time, the results are merged.
This optimistic concurrency scheme is particularly useful for query dominant environments. It negatively affects long running transactions which concurrently are affected by updates on their underlying tables.

WARNING. Optimistic concurrency control may be confusing for those who built online-transaction applications, because the granularity of the concurrency control scheme will show higher then expected transaction failures. There is not a locking schema to avoid this. Applications may have to resort to serial execution.

WARNING. The tuples being deleted are only marked as such. They do not reduce the table size. It calls for a vacuum cleaning algorithm.

为什么说是表级别的冲突呢? 因为不同的表的DML不会冲突.

例如 :

会话1
sql>start transaction;
auto commit mode: off
sql>insert into d values(100,'test','test');
1 affected rows (0.678ms)
会话2
sql>start transaction;
auto commit mode: off
sql>insert into c values(100,'test','test');
1 affected rows (0.678ms)
会话1
sql>commit;
auto commit mode: on
会话2
sql>commit;
auto commit mode: on

都提交成功

[参考]

1. https://www.monetdb.org/Documentation

2. http://en.wikipedia.org/wiki/Optimistic_concurrency_control

3. https://www.monetdb.org/Documentation/Manuals/SQLreference/Transactions