postgresql 事务_Go示例的PostgreSQL事务隔离级别

最新推荐文章于 2024-09-23 12:15:00 发布
最新推荐文章于 2024-09-23 12:15:00 发布
阅读量570 收藏 1
点赞数
文章标签： 数据库 mysql java postgresql 分布式
原文链接：https://hackernoon.com/postgresql-transaction-isolation-levels-with-go-examples-lt5g3yh5
版权
本文探讨了在PostgreSQL中事务的隔离度，包括脏读、不可重复读和幻影阅读等现象，以及如何在Go中处理并发事务。通过示例展示了不同隔离级别（读未提交、读已提交、可重复读和序列化）的行为，并指出序列化异常可能导致的问题。建议在并发访问数据库时谨慎选择隔离级别以确保数据一致性。
摘要由CSDN通过智能技术生成

postgresql 事务

插图由 玛丽亚·莱塔（MariaLetta）/ free-gophers-pack组成 ，Renee French 创作的地鼠 。

事务是一系列数据库操作，分为一个单元。 所有操作都可以提交或回滚。 最简单的例子是余额转移。

如果两个帐户之间有余额，那么在Alice和Bob之间进行转帐时，我们需要从Alice余额中减去并在一个操作中增加Bob余额。 该操作SQL代码如下所示： 

BEGIN ;
UPDATE users SET balance = balance - 10 WHERE name = 'Alice' ;
UPDATE users SET balance = balance + 10 WHERE name = 'Bob' ;
COMMIT ;

我不会深入探讨Postgres如何在内部处理所有这些问题，而只关注Go示例。

Go有两个主要的not-orm库与Postgres pg / lib和jackc / pgx一起使用 。 Pgx是更好的选择，我将在示例中使用它。 尽管pg / lib支持database/sql ，但是它不再被维护并且有一些问题，例如发生错误时出现panic ，而不是返回错误。

让我们来看一些更复杂的示例：

假设我们有一个用户表，每个用户都有名称，余额和group_id。 种子表有5个用户，每个用户的余额为100，分为3组。 

CREATE TABLE users
	(
		id serial ,
		name text ,
		balance integer ,
		group_id integer ,
		PRIMARY KEY ( id )
	);

INSERT INTO users ( name , balance, group_id )
VALUES ( 'Bob' , 100 , 1 ),
       ( 'Alice' , 100 , 1 ),
       ( 'Eve' , 100 , 2 ),
       ( 'Mallory' , 100 , 2 ),
       ( 'Trent' , 100 , 3 );

我们需要在一个ACID事务中将数据读取到程序中，对其进行处理，然后进行更新。 如果其他人尝试同时更新同一数据，则事务的行为将取决于其隔离级别而有所不同。

隔离度

它理论上有4个隔离级别，Postgres仅支持其中3个。 和4现象，不同的隔离级别应防止。

Read uncommitted ， Read committed ，可Repeatable read和可Serializable 。

Read uncommitted等于Read committed ，是Postgres中的默认隔离级别。

隔离级别的目标是防止不良现象：脏读，不可重复读，幻像读和序列化异常。

脏读

基本上，它是读取不同事务中未提交的更改。 所有事务均受Postgres保护，以防脏读，无法读取尚未提交的更改。

隔离的默认级别Read Committed ，等于Postgres中的Read uncommitted 。

在不同数据库中Read uncommitted读取允许脏读取。

首先，我们需要准备到同一数据库的两个单独的连接，以便同时发送与它们两个的事务： 

ctx = context.Background()

conn1, err := pgx.Connect(ctx, connString)if err != nil {
	fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n" , err)
	os.Exit( 1 )
}
defer conn1.Close(ctx)

conn2, err := pgx.Connect(ctx, connString)
if err != nil {
	fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n" , err)
	os.Exit( 1 )
}
defer conn2.Close(ctx)

尝试脏读：

  1. 将Bob余额更改为256 Inside主交易。
  2. 从交易中读取Bob余额。
  3. 通过第二个连接读取Bob平衡。
  4. 提交交易。

如果可能进行脏读，则步骤2和3的读取结果将相同。 但是由于更改是在事务内部进行的，因此在提交之前在外部是不可用的。 

tx, err := conn1.Begin(ctx)if err != nil {
	panic (err)
}
tx.Exec(ctx, "SET TRANSACTION ISOLATION LEVEL " +isolationLevel)

_, err = tx.Exec(ctx, "UPDATE users SET balance = 256 WHERE name='Bob'" )
if err != nil {
	fmt.Printf( "Failed to update Bob balance in tx: %v\n" , err)
}

var balance int
row := tx.QueryRow(ctx, "SELECT balance FROM users WHERE name='Bob'" )
row.Scan(&balance)
fmt.Printf( "Bob balance from main transaction after update: %d\n" , balance)

row = conn2.QueryRow(ctx, "SELECT balance FROM users WHERE name='Bob'" )
row.Scan(&balance)
fmt.Printf( "Bob balance from concurrent transaction: %d\n" , balance)

if err := tx.Commit(ctx); err != nil {
	fmt.Printf( "Failed to commit: %v\n" , err)
}

对于两个隔离级别，结果将是相同的： 

Dirty read

Isolation level - READ UNCOMMITTED
Bob balance from main transaction after update: 256
Bob balance from concurrent transaction: 100
Final table state:
 1 |        Bob |   256 | 1
 2 |      Alice |   100 | 1
 3 |        Eve |   100 | 2
 4 |    Mallory |   100 | 2
 5 |      Trent |   100 | 3

Isolation level - READ COMMITTED
Bob balance from main transaction after update: 256
Bob balance from concurrent transaction: 100
Final table state:
 1 |        Bob |   256 | 1
 2 |      Alice |   100 | 1
 3 |        Eve |   100 | 2
 4 |    Mallory |   100 | 2
 5 |      Trent |   100 | 3

不可重复读

事务从行中读取一些值，并且这些值可以在事务结束之前通过并发转换进行更改。 为防止这种情况，可Repeatable read隔离级别在提交事务之前再次读取所有这些值，如果值与初始值不同，则取消事务。 否则，如果忽略数据更改，则是不可重复的读取情况。

使用已Read committed Repeatable read ，可Repeatable read隔离级别进行测试：

  1. 从交易中读取Bob余额。
  2. 通过第二个连接将Bob余额更改为1000。
  3. 从交易中将Bob余额更改为110。
  4. 提交交易。

Read committed事务只会忽略并发更改并将其覆盖。 使用Repeatable read Postgres在步骤3中检测到并发更改并停止事务。 

tx, err := conn1.Begin(ctx)if err != nil {
	panic (err)
}
tx.Exec(ctx, "SET TRANSACTION ISOLATION LEVEL " +isolationLevel)

row := tx.QueryRow(ctx, "SELECT balance FROM users WHERE name='Bob'" )
var balance int
row.Scan(&balance)
fmt.Printf( "Bob balance at the beginning of transaction: %d\n" , balance)

fmt.Printf( "Updating Bob balance to 1000 from connection 2\n" )
_, err = conn2.Exec(ctx, "UPDATE users SET balance = 1000 WHERE name='Bob'" )
if err != nil {
	fmt.Printf( "Failed to update Bob balance from conn2  %e" , err)
}

_, err = tx.Exec(ctx, "UPDATE users SET balance = $1 WHERE name='Bob'" , balance+ 10 )
if err != nil {
	fmt.Printf( "Failed to update Bob balance in tx: %v\n" , err)
}

if err := tx.Commit(ctx); err != nil {
	fmt.Printf( "Failed to commit: %v\n" , err)
}

结果不同，在第二种情况下，交易失败： 

Nonrepeatable read

Isolation level - READ COMMITTED
Bob balance at the beginning of transaction: 100
Updating Bob balance to 1000 from connection 2
Final table state:
 1 |        Bob |   110 | 1
 2 |      Alice |   100 | 1
 3 |        Eve |   100 | 2
 4 |    Mallory |   100 | 2
 5 |      Trent |   100 | 3

Isolation level - REPEATABLE READ
Bob balance at the beginning of transaction: 100
Updating Bob balance to 1000 from connection 2
Failed to update Bob balance in tx: ERROR: could not serialize access due to concurrent update (SQLSTATE 40001)
Failed to commit: commit unexpectedly resulted in rollback
Final table state:
 1 |        Bob |  1000 | 1
 2 |      Alice |   100 | 1
 3 |        Eve |   100 | 2
 4 |    Mallory |   100 | 2
 5 |      Trent |   100 | 3

幻影阅读

虚拟读取类似于不可重复读取 ，但是它涉及在事务中选择的一组行 。 如果进行外部更改，则一组行也会更改，这是幻像读取的情况。 Repeatable read级别可防止它在Postgres中使用。

使用已Read committed Repeatable read ，可Repeatable read隔离级别进行测试：

  1. 从事务中读取group_id = 2的用户。
  2. 通过第二个连接将Bob移到组2。
  3. 再次从事务中读取group_id = 2的用户。
  4. 更新所选用户余额+15。
  5. 提交交易。

Read committed为Read committed事务将在步骤1和3上读取不同的行。使用Repeatable read Postgres将保存从事务开始的数据，而1和3的读取将返回同一行集，与并发更改隔离。 

tx, err := conn1.Begin(ctx)if err != nil {
	panic (err)
}
tx.Exec(ctx, "SET TRANSACTION ISOLATION LEVEL " +isolationLevel)

var users []User
var user User
rows, _ := tx.Query(ctx, "SELECT name, balance FROM users WHERE group_id = 2" )
for rows.Next() {
	var user User
	rows.Scan(&user.Name, &user.Balance)
	users = append (users, user)
}
fmt.Printf( "Users in group 2 at the beginning of transaction:\n%v\n" , users)

fmt.Printf( "Cuncurrent transaction moves Bob to group 2\n" )
conn2.Exec(ctx, "UPDATE users SET group_id = 2 WHERE name='Bob'" )

users = []User{}
rows, _ = tx.Query(ctx, "SELECT name, balance FROM users WHERE group_id = 2" )
for rows.Next() {
	rows.Scan(&user.Name, &user.Balance)
	users = append (users, user)
}
fmt.Printf( "Users in group 2 after cuncurrent transaction:\n%v\n" , users)

fmt.Printf( "Update selected users balances by +15\n" )
for _, user := range users {
	_, err = tx.Exec(ctx, "UPDATE users SET balance = $1 WHERE name=$2" , user.Balance+ 15 , user.Name)
	if err != nil {
		fmt.Printf( "Failed to update in tx: %v\n" , err)
	}
}

if err := tx.Commit(ctx); err != nil {
	fmt.Printf( "Failed to commit: %v\n" , err)
}

根据第二选择的结果，受升级影响的不同用户的结果是不同的： 

Phantom read

Isolation level - READ COMMITTED
Users in group 2 at the beginning of transaction:
[{Eve 100} {Mallory 100}]
Cuncurrent transaction moves Bob to group 2
Users in group 2 after cuncurrent transaction:
[{Eve 100} {Mallory 100} {Bob 100}]
Update selected users balances by +15
Final table state:
 1 |        Bob |   115 | 2
 2 |      Alice |   100 | 1
 3 |        Eve |   115 | 2
 4 |    Mallory |   115 | 2
 5 |      Trent |   100 | 3

Isolation level - REPEATABLE READ
Users in group 2 at the beginning of transaction:
[{Eve 100} {Mallory 100}]
Cuncurrent transaction moves Bob to group 2
Users in group 2 after cuncurrent transaction:
[{Eve 100} {Mallory 100}]
Update selected users balances by +15
Final table state:
 1 |        Bob |   100 | 2
 2 |      Alice |   100 | 1
 3 |        Eve |   115 | 2
 4 |    Mallory |   115 | 2
 5 |      Trent |   100 | 3

序列化异常

假设我们有几个正在进行的并发事务，它们都使用表进行一些读取和写入。 如果最终表状态将取决于运行和提交这些事务的顺序，则为Serialization anomaly 。

在这种情况下，成绩可能会受到比赛条件的影响。 隔离级别可Serializable帮助可防止此类问题。 我不得不说，即使在这种序列化级别下，一些罕见的情况仍然可能导致这种现象。

使用可Repeatable read和可Serializable隔离级别进行测试：

  1. 使用第二个连接启动第二个事务。
  2. 将第二个事务隔离级别设置为与主事务相同。
  3. 从事务1中读取group_id = 2的用户余额总和。
  4. 将Bob与事务2移到组2。
  5. 从事务1中读取group_id = 2的用户。
  6. 通过1次操作以+ sum更新所选用户余额。
  7. 提交主要交易。
  8. 提交第二笔交易。

具有“可Repeatable read和“可Repeatable read事务将被提交而没有错误。 使用Serializable隔离级别，将不会提交第二个事务。 这两个事务使用相同的数据工作，并且提交的顺序将影响结果，这可能导致不可预测的结果。 Postgres将阻止提交第二笔交易，以防止这种不确定性。 

tx, err := conn1.Begin(ctx)if err != nil {
	panic (err)
}
tx.Exec(ctx, "SET TRANSACTION ISOLATION LEVEL " +isolationLevel)

tx2, err := conn2.Begin(ctx)
if err != nil {
	panic (err)
}
tx2.Exec(ctx, "SET TRANSACTION ISOLATION LEVEL " +isolationLevel)

var sum int
row := tx.QueryRow(ctx, "SELECT SUM(balance) FROM users WHERE group_id = 2" )
row.Scan(&sum)

tx2.Exec(ctx, "UPDATE users SET group_id = 2 WHERE name='Bob'" )
if err != nil {
	fmt.Printf( "Error in tx2: %v\n" , err)
}

rows, _ := tx.Query(ctx, "SELECT name, balance FROM users WHERE group_id = 2" )
type User struct {
	Name    string
	Balance int
}
var users []User
for rows.Next() {
	var user User
	rows.Scan(&user.Name, &user.Balance)
	users = append (users, user)
}

for _, user := range users {
	_, err = tx.Exec(ctx, "UPDATE users SET balance = $1 WHERE name=$2" , user.Balance+sum, user.Name)
	if err != nil {
		fmt.Printf( "Failed to update in tx: %v\n" , err)
	}
}

if err := tx.Commit(ctx); err != nil {
	fmt.Printf( "Failed to commit tx: %v\n" , err)
}

if err := tx2.Commit(ctx); err != nil {
	fmt.Printf( "Failed to commit tx2: %v\n" , err)
}

在第二种情况下，事务失败并显示“由于事务之间的读/写依赖性而无法序列化访问”错误： 

Serialization anomaly

Isolation level - REPEATABLE READ
Final table state:
 1 |        Bob |   100 | 2
 2 |      Alice |   100 | 1
 3 |        Eve |   300 | 2
 4 |    Mallory |   300 | 2
 5 |      Trent |   100 | 3

Isolation level - SERIALIZABLE
Failed to commit tx2: ERROR: could not serialize access due to read/write dependencies among transactions (SQLSTATE 40001)
Final table state:
 1 |        Bob |   100 | 1
 2 |      Alice |   100 | 1
 3 |        Eve |   300 | 2
 4 |    Mallory |   300 | 2
 5 |      Trent |   100 | 3

结论

当您具有多个连接并可以同时访问Postgres数据库时，请仔细选择隔离级别。 较高的隔离级别可提供安全性，但会降低性能。 另外，您应该检查该事务是否已成功提交，并在必要时重复进行。

本文中的所有示例：
https://github.com/kochetkov-av/go-postgresql-transaction-isolation

翻译自: https://hackernoon.com/postgresql-transaction-isolation-levels-with-go-examples-lt5g3yh5

postgresql 事务

dfsgwe1231
关注
PostgreSQL 事务隔离级别
椟夜
08-11 4460
PostgreSQL事务的隔离级别目前有4种，分别是：读未提交，读已提交，可重复读，串行化。 duyeweb=# \h begin Command: BEGIN Description: start a transaction block Syntax: BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ] where ...
一文彻底读懂PostgreSQL事务隔离级别
12-14
SQL标准定义了四种隔离级别。隔离级别从低到高分别是：Read uncommitted 、Read committed 、Repeatable read 、Serializable。在各个隔离级别上被禁止出现的现象是： 脏读：一个事务读取了另一个并行未提交事务写入...
数据库事务隔离级别测试-postgresql
Just Do It!
11-10 1739
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=> start  
postgresql 事务隔离级别与锁
weixin_41637329的博客
07-31 4940
postgresql的隔离级别与锁
Linux杂命令学习（全局信息、jupyterlab、tmux）
木尧大兄弟
11-27 323
发送全局消息 wall “hello world” jupyterlab安装和使用 (sum2020) xxx@xn307-Server:~$ pip install jupyterlab (sum2020) xxx@xn307-Server:~$ jupyter lab tmux后台启动jupyter lab (sum2020) xxx@xn307-Server:~$ tmux 新建会话：tmux new -s [name] 退出会话：ctrl+b d 查看会话列表：tmux ls 进入会话：tmux a
在go中使用PostgreSQL
LittleFish's Blog
06-25 4984
文章目录在go中使用PostgreSQL1. PostgreSQL2. PostgreSQL 相关操作3. 数据库驱动4. go 操作数据库（1）打开数据库（2）查询（3）插入（4）更新（5）删除（6）count5. 其他 在go中使用PostgreSQL 1. PostgreSQL 进行后台开发的时候，不可避免的就是需要使用到数据库，在众多数据库中，PostgreSQL是一个很好的选择，本人一开...
ssm_postgresql_事务_log4j.rar
07-19
标题中的"ssm_postgresql_事务_log4j.rar"表明这是一个关于SSM（Spring、SpringMVC、MyBatis）框架与PostgreSQL数据库在事务处理和日志记录方面结合使用的资源压缩包。SSM是Java web开发中常用的三大组件，它们提供...
PostgreSQL find_in_set 内核开发
03-20
博客：PostgreSQL的学习心得和知识总结（六十五）|关于PostgreSQL数据库 实现MySQL数据库find_in_set()函数 的实现方案
PostgreSQL_8.2.3.rar_postgresql_windows 8
09-24
2. **SQL语言基础**：PostgreSQL遵循SQL标准，所以文档会教授基本的SQL语法，如创建数据库、表，插入、更新和删除数据，以及查询语句的使用。 3. **安全性与权限**：在8.2.3版本中，理解角色、用户管理和权限控制是...
PostgreSQL数据库事务实现方法分析
12-16
PostgreSQL支持ACID（原子性、一致性、隔离性和持久性）属性，以确保事务的正确性。 事务管理器在PostgreSQL中扮演关键角色，它是一个有限状态机，负责跟踪事务的不同阶段。事务状态包括启动、进行中、提交、回滚等...
事务的提交commit、回滚rollback
qq_35091353的博客
05-16 4871
1、事务 事物由一个或多个sql语句组成，如果其中一个语句不能完成，整个单元就会回滚，所有影响到的数据将返回到事物开始以前的状态。因而，只有事物中的所有语句都成功执行，才能说明这个事物被成功的执行。 2、事务的提交与回滚 /* 创建账户表 */ create table account ( id int primary key auto_increment, name varchar(40), money float ) character set utf8 collate utf8
关于COMMIT和ROLLBACK语句的使用注意事项
cmcagfdvr09264907的博客
08-30 972
作者:莫怀远1． COMMIT WORK 语句执行许多与任务的同步执行有关的功能。ROLLBACK WORK语句“取消”任务同步执行的所有请求。COMMIT WORK 语句进行下列处理：• 执行所有由PERFORM ON COM...
mysql 回滚之后抛出异常_在PHP中 开始事务后，程序抛出异常 没有执行commit也没有执行rollback mysql事务会回滚吗？...
weixin_32287433的博客
01-28 253
网上有人说开启事务后 sql语句执行错误或程序出错 没有执行rollback的情况下，下一次运行就会自动commit程序出错不会回滚rollback但是也有人说抛出异常等 程序如果MySQL存在没有提交的事务，那么这时候 AUTOCOMMIT 自动提交的参数应该是为 0 的。不过如果你通过其他的语言的MySQL驱动来操作的话，这些驱动一般都带有自动恢复 AUTOCOMMIT 的功能，在请求处理完成...
PostgreSQL 事务与并发控制
这个人很懒什么都没有~
10-21 1724
而MVCC是一种后验性的机制，读不阻塞写，写也不阻塞读，等到提交的时候才检验是否有冲突，由于没有锁，所以读写不会相互阻塞，避免了大粒度和长时间的锁定，能更好地适应对读的响应速度和并发性要求高的场景，大大提升了并发性能。在该隔离级别，所有事务都可以看到其他未提交事务的执行结果，在多用户数据库中，脏读是非常危险的，在并发情况下，查询结果非常不可控，即使不考虑结果的严谨性只追求性能，它的性能也并不比其他事务隔离级别好多少，可以说脏读没有任何好处。简言之，它是在每个读的数据行上加上共享锁。
postgresql中的事务-begin  、commit  、rollback 
10-18 1万+
事务：确保一系列操作必须全部成功后，才算成功。一旦其中一个操作出错，那么会回到初始状态，哪怕前几个操作已经成功了。 比如某人要在商店使用电子货币购买100元的东西，当中至少包括两个操作： 该人账户减少 100 元。 商店账户增加100元。 支持事务的数据库管理系统就是要确保以上两个操作（整个"事务"）都能完成，或一起取消，否则就会出现 100 元平白消失或出现的情况。 事务有三个命令，如下： begin 开始事务。开始事务后，后面的操作并不会直接修改数据库，除非提交事务 commit 提交事..
8.3 PostgreSQL事务隔离级别与应用
最新发布
Python老吕的博客
09-23 1260
事务隔离级别是数据库管理系统用来解决并发事务中可能出现的问题（如脏读、不可重复读和幻读）的一种机制。在数据库系统中，多个用户或应用程序可能同时对数据进行读写操作，事务隔离级别定义了事务在并发执行时彼此之间如何被隔离，以确保数据的一致性和完整性。目的：防止脏读：确保事务不会读取到其他未提交事务对数据所做的修改。防止不可重复读：确保在同一事务中多次读取同一数据集合时，结果保持一致，不受其他事务对数据的修改影响。防止幻读：确保事务在读取数据集合时，不会因为其他事务的插入、删除操作而改变其读取的数据集合。
PostgreSQL之 事务的提交与回滚
热门推荐
HighGO
02-11 2万+
在PostgreSQL的psql中，如果执行一个dml或ddl语句，没有先运行begin;的话，一执行完就马上提交了，不能回滚，这样容易导致误操作的发生，避免这个风险的办法是关闭自动提交，方法如下：设置\set AUTOCOMMIT offtestdb=# create table tt1(x int);CREATE TABLETime: 0.593 mstestdb=# select * f...
定位数据库里的长事务语句之一
HighGO
01-08 1613
定位数据库里的长事务 SELECT client_addr, usename, datname, clock_timestamp() - xact_start AS xact_age, clock_timestamp() - query_start AS query_age, state, query FROM pg_stat_activity ORDER BY coalesce(xact_sta
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值