MySql操作指南5--事务与并发控制

数据库事务是保障数据一致性和可靠性的重要手段，并发控制则在多用户环境下确保数据的正确性。风云今天详细探讨数据库事务的管理、并发访问的最佳实践，乐观锁与悲观锁的应用，以及Golang 中的事务实现、并发访问的最佳实践，通过合理设计事务和锁机制，可以在高并发场景下有效保障数据的一致性和系统性能。

1、数据库事务管理

事务是一个操作序列，具有以下四个特性（ACID）：

原子性（Atomicity）：事务中的操作要么全部完成，要么全部回滚。

一致性（Consistency）：事务前后，数据库保持一致性状态。

隔离性（Isolation）：事务之间相互隔离，避免相互干扰。

持久性（Durability）：事务完成后，其对数据库的更改永久生效。

2、在 Golang 中实现事务

示例：事务的基本操作

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	// 数据库连接
	dsn := "user:password@tcp(127.0.0.1:3306)/testdb" // dsn格式

	db, err := sql.Open("mysql", dsn) // 打开数据库连接

	if err != nil { // 如果连接失败，则输出错误信息并退出程序
		log.Fatal(err)
	}

	defer db.Close() // 关闭数据库连接

	// 开启事务
	tx, err := db.Begin() // 开启事务

	if err != nil {
		log.Fatal(err)
	}

	// 执行操作
	_, err = tx.Exec("INSERT INTO accounts (id, balance) VALUES (?, ?)", 1, 100) // 执行操作

	if err != nil { // 如果执行失败，则回滚事务并输出错误信息并退出程序
		tx.Rollback() // 回滚事务

		log.Fatal(err)
	}

	_, err = tx.Exec("UPDATE accounts SET balance = balance - 50 WHERE id = ?", 1) // 执行操作

	if err != nil { // 如果执行失败，则回滚事务并输出错误信息并退出程序
		tx.Rollback() // 回滚事务

		log.Fatal(err)
	}

	// 提交事务
	err = tx.Commit()

	if err != nil { // 如果提交失败，则回滚事务并输出错误信息并退出程序

		log.Fatal(err)

	}

	fmt.Println("Transaction completed successfully")
}

3、事务的隔离级别

MySQL 提供四种事务隔离级别：

读未提交（Read Uncommitted）：事务可以读取其他未提交事务的数据，可能导致脏读。
读已提交（Read Committed）：事务只能读取已提交的数据，避免脏读。
可重复读（Repeatable Read）：同一事务中多次读取数据一致，避免不可重复读（MySQL 默认级别）。
可串行化（Serializable）：完全隔离，事务逐一执行，避免幻读。

设置事务隔离级别

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

在 Golang 中可以通过 SET 语句设置隔离级别：

tx.Exec("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")

4、并发访问的最佳实践

在并发环境中，事务之间可能发生以下冲突：

脏读（Dirty Read）：一个事务读取了另一个未提交事务的数据。
不可重复读（Non-repeatable Read）：同一事务中多次读取结果不一致。
幻读（Phantom Read）：事务中新增或删除的记录导致结果变化。

以下是一些避免冲突的最佳实践：

5、使用事务管理并发

事务能够隔离并发访问，保证数据一致性。结合合适的隔离级别，可以避免大多数并发问题。

使用锁机制

行锁和表锁

行锁：锁定某一行数据，适用于高并发场景。
表锁：锁定整个表，避免全表修改导致的数据不一致问题。

LOCK TABLES accounts WRITE;UPDATE accounts SET balance = balance - 50 WHERE id = 1;

UNLOCK TABLES;

在 Golang 中，利用事务和条件语句实现锁机制：

tx.Exec("SELECT * FROM accounts WHERE id = ? FOR UPDATE", 1)

6、乐观锁与悲观锁

6.1 乐观锁

乐观锁通过检查版本号或时间戳，确保并发访问不会冲突。

乐观锁实现

SELECT version FROM accounts WHERE id = 1;UPDATE accounts SET balance = balance - 50, version = version + 1 WHERE id = 1 AND version = ?;

Golang 示例

func updateBalanceWithOptimisticLock(db *sql.DB, id int, amount int) error { // 乐观锁
	var version int

	err := db.QueryRow("SELECT version FROM accounts WHERE id = ?", id).Scan(&version) // 查询账户的版本号

	if err != nil { // 如果查询失败，则输出错误信息并退出程序
		return err
	}

	result, err := db.Exec("UPDATE accounts SET balance = balance - ?, version = version + 1 WHERE id = ? AND version = ?", amount, id, version) // 更新账户的余额和版本号

	if err != nil {
		return err
	}

	rowsAffected, err := result.RowsAffected() // 获取受影响的行数

	if err != nil {
		return err
	}

	if rowsAffected == 0 { // 如果受影响的行数为0，则说明版本号不一致，返回错误信息
		return fmt.Errorf("transaction conflict, try again")
	}

	return nil
}

6.2 悲观锁

悲观锁假定冲突必然发生，在操作前加锁以避免冲突。

悲观锁实现

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

Golang 示例

func updateBalanceWithPessimisticLock(db *sql.DB, id int, amount int) error { // 悲观锁
	tx, err := db.Begin() // 开启事务

	if err != nil { // 如果开启事务失败，则输出错误信息并退出程序
		return err
	}

	_, err = tx.Exec("SELECT * FROM accounts WHERE id = ? FOR UPDATE", id) // 使用FOR UPDATE关键字锁定记录

	if err != nil { // 如果锁定记录失败，则输出错误信息并退出程序
		tx.Rollback() // 回滚事务

		return err
	}

	_, err = tx.Exec("UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, id) // 更新账户的余额

	if err != nil { // 如果更新记录失败，则回滚事务并返回错误
		tx.Rollback() // 回滚事务

		return err
	}

	return tx.Commit() // 提交事务并返回nil
}