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)从现象上看, 在事务过程中并没有锁冲突发生, 在事务提交的时候检测到冲突后回滚.
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.
会话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