开始一个事务
postgres=# begin;BEGIN
或者
postgres=# begin TRANSACTION ;BEGIN
也可以定义事务的级别。
postgres=# begin transaction isolation level read committed ;BEGIN
提交事务
进程#1访问 。
postgres=# begin;BEGINpostgres=# delete from tdsql_pg where id=5;DELETE 1postgres=#postgres=# select * from tdsql_pg order by id;id | nickname----+---------------1 | hello tdsql_pg2 | tdsql_pg好3 | tdsql_pg好4 | tdsql_pg default
TDSQL PostgreSQL版 完全支持 ACID 特性,没提交前开启另一个连接查询,会看到是5条记录,这是 TDSQL PostgreSQL版 隔离性和多版本视图的实现,如下所示。
进程#2访问。
postgres=# select * from tdsql_pg order by id;id | nickname----+---------------1 | hello tdsql_pg2 | tdsql_pg好3 | tdsql_pg好4 | tdsql_pg default5 | tdsql_pg swap(5 rows)
进程#1提交数据。
postgres=# commit;COMMITpostgres=#
进程#2再查询数据,这时已经能看到提交的数据,这个级别叫“读已提交”。
postgres=# select * from tdsql_pg order by id;id | nickname----+---------------1 | hello tdsql_pg2 | tdsql_pg好3 | tdsql_pg好4 | tdsql_pg default(4 rows)
回滚事务
postgres=# begin;BEGINpostgres=# delete from tdsql_pg where id in (3,4);DELETE 2postgres=# select * from tdsql_pg;id | nickname----+-------------1 | hello tdsql_pg2 | tdsql_pg好(2 rows)postgres=# rollback;ROLLBACK
Rollback 后数据又回来了。
postgres=# select * from tdsql_pg;id | nickname----+---------------1 | hello tdsql_pg2 | tdsql_pg好3 | tdsql_pg好4 | tdsql_pg default(4 rows)
事务读一致性 REPEATABLE READ
这种事务级别表示事务自始至终读取的数据都是一致的,如下所示。
#session1
postgres=# create table t_repeatable_read (id int,mc text);NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLEpostgres=# insert into t_repeatable_read values(1,'tdsql_pg');INSERT 0 1postgres=# begin isolation level repeatable read ;BEGINpostgres=# select * from t_repeatable_read ;id | mc----+-------1 | tdsql_pg(1 row)
#session2
postgres=# insert into t_repeatable_read values(1,'pgxz');INSERT 0 1postgres=# select * from t_repeatable_read;id | mc----+-------1 | tdsql_pg1 | pgxz(2 rows)
#session1
postgres=# select * from t_repeatable_read ;id | mc----+-------1 | tdsql_pg(1 row)postgres=#
行锁在事务中的运用
环境准备
postgres=# create table t_row_lock(id int,mc text,primary key (id));NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLEpostgres=#postgres=# insert into t_row_lock values(1,'tdsql_pg'),(2,'pgxz');INSERT 0 2postgres=# select * from t_row_lock;id | mc----+-------1 | tdsql_pg2 | pgxz(2 rows)
直接 update 获取
#session1
postgres=# begin;BEGINpostgres=# set lock_timeout to 1;SETpostgres=# update t_row_lock set mc='postgres' where mc='pgxz';UPDATE 1postgres=#
#session2
postgres=# begin;BEGINpostgres=# set lock_timeout to 1;SETpostgres=# update t_row_lock set mc='postgresql' where mc='tdsql_pg';UPDATE 1postgres=#
上面 session1 与 session2 分别持有 mc=pgxz 行和 mc=tdsql_pg 的行锁。
select...for update 获取
#session1
postgres=#BEGINpostgres=# set lock_timeout to 1;SETpostgres=# select * from t_row_lock where mc='pgxz' for update;id | mc----+------2 | pgxz(1 row)
#session2
postgres=# begin;BEGINpostgres=# set lock_timeout to 1;SETpostgres=# select * from t_row_lock where mc='pgxz' for update;id | mc----+------2 | pgxz(1 row)
上面 session1 与 session2 分别持有 mc=pgxz 行和 mc=tdsql_pg 的行锁。
与 MySQL 获取行级锁的区别
mysql> select version();+-----------+| version() |+-----------+| 5.6.36 |+-----------+1 row in set (0.00 sec)
#session1
mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t_row_lock where mc='pgxz' for update;+----+------+| id | mc |+----+------+| 2 | pgxz |+----+------+1 row in set (0.00 sec)
#session2
mysql> select * from t_row_lock where mc='tdsql_pg' for update;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql>
这是因为 MySQL 要使用行级锁需要有索引来配合使用,如下所示,使用 ID 主键来获取行锁。
#session1
mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t_row_lock where id=1 for update;+----+-------+| id | mc |+----+-------+| 1 | tdsql_pg |+----+-------+1 row in set (0.00 sec)
#session2
mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t_row_lock where id=2 for update;+----+------+| id | mc |+----+------+| 2 | pgxz |+----+------+1 row in set (0.00 sec)