数据库事务隔离级别测试-postgresql

来源：互联网发布：高端化妆品知乎编辑：程序博客网时间：2024/06/09 20:04

1. read-committed (default 级别)

客户端A：
epmdb=> start transaction ;
START TRANSACTION
epmdb=> update t1.dbversion set value =1200 where dbversionid=1;
UPDATE 1
epmdb=>

B查询：
epmdb=> select * from t1.dbversion ;
dbversionid | value | currentdataversion | targetdataversion
-------------+-------+--------------------+-------------------
1 | 1200 | 100 | 1
(1 row)

2. read-uncommited（Postgresql 不支持）

修改隔离级别：修改后需要重新连接数据库否则级别不会更改，重新连接也不行，需要分别修改A B的事务隔离级别，或者修改默认的事务隔离级别

不是设置问题，经过查阅doc，postgresq 不支持此级别

epmdb=> start transaction ;
START TRANSACTION
epmdb=> set default_transaction_isolation='read uncommitted';
SET
epmdb=> commit;
COMMIT
epmdb=> show default_transaction_isolation;
default_transaction_isolation
-------------------------------
read uncommitted
(1 row)

epmdb=> set default_transaction_isolation='read uncommitted';
SET
epmdb=> show default_transaction_isolation;
default_transaction_isolation
-------------------------------
read uncommitted
(1 row)

开启事务，查询当前数据：

3. repeatable-read

epmdb=> set default_transaction_isolation TO 'repeatable read'
;
SET
epmdb=> show default_transaction_isolation ;
default_transaction_isolation
-------------------------------
repeatable read
(1 row)

A修改数据并且commit，

epmdb=> start transaction ;
START TRANSACTION
epmdb=> update t1.dbversion set value =66 where dbversionid=1;
UPDATE 1
epmdb=> commit;
COMMIT

B 提交事务，再次查询：

epmdb=> commit;
COMMIT
epmdb=> select * from t1.dbversion ;
dbversionid | value | currentdataversion | targetdataversion
-------------+-------+--------------------+-------------------
1 | 66 | 100 | 1
(1 row)

在事务进行中，其他事务的提交不会影响当前事务

4. serialable

epmdb=> show default_transaction_isolation ;
default_transaction_isolation
-------------------------------
serializable
(1 row)

修改隔离级别一定要开启事务，然后修改，再提交，直接set 不会生效。

B开启事务，查询：

epmdb=> start transaction ;
START TRANSACTION
epmdb=> select * from t1.dbversion ;
dbversionid | value | currentdataversion | targetdataversion
-------------+-------+--------------------+-------------------
1 | 66 | 100 | 1
2 | 3 | 4 | 50
(2 rows)

epmdb=> select * from t1.dbversion ;
dbversionid | value | currentdataversion | targetdataversion
-------------+-------+--------------------+-------------------
1 | 66 | 100 | 1
2 | 3 | 4 | 50
(2 rows)

A开启事务，试图修改或者插入：

epmdb=> start transaction ;
START TRANSACTION
epmdb=> select * from t1.dbversion ;
dbversionid | value | currentdataversion | targetdataversion
-------------+-------+--------------------+-------------------
1 | 66 | 100 | 1
2 | 3 | 4 | 50
(2 rows)

START TRANSACTION
epmdb=> update t1.dbversion set value =644446 where dbversionid=1;
UPDATE 1
epmdb=> insert into t1.dbversion (dbversionid,value,currentdataversion,targetdataversion) values(12444,3,4,50);
INSERT 0 1
epmdb=> commit;
COMMIT

都成功，查询文档，得知，serial是把事务并行提交，转换为串行执行

Suppose that serializable transaction A computes:

SELECT SUM(value) FROM mytab WHERE class = 1;

and then inserts the result (30) as the value in a new row with class = 2. Concurrently, serializable transaction B computes:

SELECT SUM(value) FROM mytab WHERE class = 2;

and obtains the result 300, which it inserts in a new row with class = 1. Then both transactions try to commit. If either transaction were running at the Repeatable Read isolation level, both would be allowed to commit; but since there is no serial order of execution consistent with the result, using Serializable transactions will allow one transaction to commit and will roll the other back with this message:

ERROR:  could not serialize access due to read/write dependencies among transactions

由于无法测试同时提交两个查询，无法测试

阅读全文

0 0