在MySQL中处理同时进行的SELECT和UPDATE操作20240729

已于 2024-07-29 11:28:54 修改
阅读量704 收藏 4
点赞数 14
分类专栏： 技术干货分享 文章标签： mysql 数据库
于 2024-07-29 11:28:21 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Narutolxy/article/details/140767025
版权

在MySQL中处理同时进行的SELECT和UPDATE操作

在MySQL中同时对同一张表执行SELECT和UPDATE操作可能会引发脏读、不可重复读和幻读等问题。本文将详细介绍这些问题及其解决方法，并提供具体的示例代码。

问题描述

在多事务环境下，可能会遇到以下问题：

  1. 脏读 (Dirty Read): 一个事务在读取数据的同时，另一个事务修改了同一行的数据并提交，第一个事务读取的数据可能是不一致的。
  2. 不可重复读 (Non-repeatable Read): 一个事务在读取数据的同时，另一个事务修改了同一行的数据并提交，第一个事务再次读取同一行的数据时，可能会得到不同的结果。
  3. 幻读 (Phantom Read): 一个事务在读取数据的同时，另一个事务插入了符合第一个事务查询条件的新数据并提交，第一个事务再次执行相同的查询时，会发现存在新的数据。

解决方法

为了解决上述问题，可以使用MySQL提供的事务隔离级别和锁机制来确保数据的一致性。

事务隔离级别

MySQL支持四种事务隔离级别：

  1. READ UNCOMMITTED: 允许脏读、不可重复读和幻读。
  2. READ COMMITTED: 防止脏读，但允许不可重复读和幻读。
  3. REPEATABLE READ: 防止脏读和不可重复读，但允许幻读。
  4. SERIALIZABLE: 防止脏读、不可重复读和幻读。

可以通过设置事务隔离级别来控制并发事务的行为。下面是设置事务隔离级别的示例：

-- 设置事务隔离级别为 REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;

-- 你的查询和更新操作
SELECT * FROM employees WHERE id = 1 FOR UPDATE;
UPDATE employees SET name = 'new_name' WHERE id = 1;

COMMIT;
锁机制

MySQL的InnoDB存储引擎提供了行级锁，可以在查询时使用锁来确保数据的一致性。

-- 设置事务隔离级别为 SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;

-- 你的查询和更新操作
SELECT * FROM employees WHERE id = 1 FOR UPDATE;
UPDATE employees SET name = 'new_name' WHERE id = 1;

COMMIT;

在上述示例中，使用FOR UPDATE语句对查询的行加锁，以防止其他事务在当前事务完成之前修改这些行。

实际案例分析

假设我们在两个不同的数据库实例中进行实验，以验证不同配置下MySQL的行为。

表结构

假设我们有一个名为employees的表，表结构如下：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    department VARCHAR(255)
);
实验步骤和SQL语句
  1. 环境准备：
    • 创建数据库和表。
    • 插入初始数据。
-- 创建数据库
CREATE DATABASE company_db;

-- 选择数据库
USE company_db;

-- 创建表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    department VARCHAR(255)
);

-- 插入初始数据
INSERT INTO employees (id, name, department) VALUES (1, 'Alice', 'HR');
  1. Session 1: 开始事务，查询employees表。
-- Session 1
START TRANSACTION;
SELECT * FROM employees;
  1. Session 2: 插入一行数据到employees表并提交事务。
-- Session 2
START TRANSACTION;
INSERT INTO employees (id, name, department) VALUES (2, 'Bob', 'Engineering');
COMMIT;
  1. Session 1: 再次查询employees表。
-- Session 1
SELECT * FROM employees;

实验现象

在两个环境中进行上述操作，并观察结果。

  • 环境A: Session 1无法查询到Session 2提交的数据。
  • 环境B: Session 1可以查询到Session 2提交的数据。

实际案例分析

通过不同的环境验证，我们发现：

环境A
  • MySQL版本: 5.7.13
  • autocommit: OFF
  • 事务隔离级别: REPEATABLE READ

在此环境中，Session 1在第二次查询时无法看到Session 2提交的数据，因为事务在开始时就固定了读取的数据版本。

环境B
  • MySQL版本: 5.7.13
  • autocommit: ON
  • 事务隔离级别: REPEATABLE READ

在此环境中，Session 1在第二次查询时可以看到Session 2提交的数据，因为每个查询都是一个新的事务。

验证问题

通过不同组合的autocommit和隔离级别参数，验证查询结果是否符合预期。

验证组合
  1. autocommit=ON，隔离级别=REPEATABLE-READ: Session 1能否在T3时刻看到Session 2提交的数据？
    • 答：能。
  2. autocommit=OFF，隔离级别=REPEATABLE-READ: Session 1能否在T3时刻看到Session 2提交的数据？
    • 答：不能。
  3. autocommit=ON，隔离级别=READ-COMMITTED: Session 1能否在T3时刻看到Session 2提交的数据？
    • 答：能。
  4. autocommit=OFF，隔离级别=READ-COMMITTED: Session 1能否在T3时刻看到Session 2提交的数据？
    • 答：能。

代码示例

以下是Python、C语言和Go语言的示例代码，演示如何在MySQL中使用事务和锁机制来解决SELECT和UPDATE操作的并发问题。

Python代码示例
import pymysql

# 连接数据库
conn1 = pymysql.connect(host='localhost', user='root', password='password', db='company_db', autocommit=False)
conn2 = pymysql.connect(host='localhost', user='root', password='password', db='company_db', autocommit=True)

try:
    with conn1.cursor() as cursor1, conn2.cursor() as cursor2:
        # Session 1: 开始事务并查询数据
        cursor1.execute("START TRANSACTION;")
        cursor1.execute("SELECT * FROM employees;")
        result1 = cursor1.fetchall()
        print("Session 1 - First Query:", result1)

        # Session 2: 插入数据并提交事务
        cursor2.execute("INSERT INTO employees (id, name, department) VALUES (2, 'Bob', 'Engineering');")
        cursor2.execute("COMMIT;")

        # Session 1: 再次查询数据
        cursor1.execute("SELECT * FROM employees;")
        result2 = cursor1.fetchall()
        print("Session 1 - Second Query:", result2)
finally:
    conn1.close()
    conn2.close()
C语言代码示例
#include <mysql/mysql.h>
#include <stdio.h>

void finish_with_error(MYSQL *con) {
    fprintf(stderr, "%s\n", mysql_error(con));
    mysql_close(con);
    exit(1);
}

int main() {
    MYSQL *con1 = mysql_init(NULL);
    MYSQL *con2 = mysql_init(NULL);

    if (con1 == NULL || con2 == NULL) {
        fprintf(stderr, "mysql_init() failed\n");
        exit(1);
    }

    if (mysql_real_connect(con1, "localhost", "root", "password", "company_db", 0, NULL, 0) == NULL ||
        mysql_real_connect(con2, "localhost", "root", "password", "company_db", 0, NULL, 0) == NULL) {
        finish_with_error(con1);
        finish_with_error(con2);
    }

    // Session 1: 开始事务并查询数据
    if (mysql_query(con1, "START TRANSACTION") ||
        mysql_query(con1, "SELECT * FROM employees")) {
        finish_with_error(con1);
    }

    MYSQL_RES *result1 = mysql_store_result(con1);
    if (result1 == NULL) {
        finish_with_error(con1);
    }

    MYSQL_ROW row;
    printf("Session 1 - First Query:\n");
    while ((row = mysql_fetch_row(result1))) {
        printf("%s %s %s\n", row[0], row[1], row[2]);
    }
    mysql_free_result(result1);

   

 // Session 2: 插入数据并提交事务
    if (mysql_query(con2, "START TRANSACTION") ||
        mysql_query(con2, "INSERT INTO employees (id, name, department) VALUES (2, 'Bob', 'Engineering')") ||
        mysql_query(con2, "COMMIT")) {
        finish_with_error(con2);
    }

    // Session 1: 再次查询数据
    if (mysql_query(con1, "SELECT * FROM employees")) {
        finish_with_error(con1);
    }

    MYSQL_RES *result2 = mysql_store_result(con1);
    if (result2 == NULL) {
        finish_with_error(con1);
    }

    printf("Session 1 - Second Query:\n");
    while ((row = mysql_fetch_row(result2))) {
        printf("%s %s %s\n", row[0], row[1], row[2]);
    }
    mysql_free_result(result2);

    mysql_close(con1);
    mysql_close(con2);

    return 0;
}
Go语言代码示例
package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    db1, err := sql.Open("mysql", "root:password@tcp(localhost:3306)/company_db?autocommit=false")
    if err != nil {
        panic(err)
    }
    defer db1.Close()

    db2, err := sql.Open("mysql", "root:password@tcp(localhost:3306)/company_db?autocommit=true")
    if err != nil {
        panic(err)
    }
    defer db2.Close()

    // Session 1: 开始事务并查询数据
    tx1, err := db1.Begin()
    if err != nil {
        panic(err)
    }

    rows1, err := tx1.Query("SELECT * FROM employees")
    if err != nil {
        panic(err)
    }
    defer rows1.Close()

    fmt.Println("Session 1 - First Query:")
    for rows1.Next() {
        var id int
        var name, department string
        err = rows1.Scan(&id, &name, &department)
        if err != nil {
            panic(err)
        }
        fmt.Println(id, name, department)
    }

    // Session 2: 插入数据并提交事务
    tx2, err := db2.Begin()
    if err != nil {
        panic(err)
    }

    _, err = tx2.Exec("INSERT INTO employees (id, name, department) VALUES (?, ?, ?)", 2, "Bob", "Engineering")
    if err != nil {
        panic(err)
    }

    err = tx2.Commit()
    if err != nil {
        panic(err)
    }

    // Session 1: 再次查询数据
    rows2, err := tx1.Query("SELECT * FROM employees")
    if err != nil {
        panic(err)
    }
    defer rows2.Close()

    fmt.Println("Session 1 - Second Query:")
    for rows2.Next() {
        var id int
        var name, department string
        err = rows2.Scan(&id, &name, &department)
        if err != nil {
            panic(err)
        }
        fmt.Println(id, name, department)
    }

    tx1.Commit()
}

总结

通过设置合适的事务隔离级别和使用行级锁，可以有效地解决在MySQL中同时执行SELECT和UPDATE操作同一张表时遇到的脏读、不可重复读和幻读问题。这不仅确保了数据的一致性，还提高了系统的并发处理能力。理解并正确使用这些机制，是保证数据库操作正确性和效率的关键。

Narutolxy
关注
  • 14
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySQL中SELECT+UPDATE处理并发更新问题解决方案分享
09-10
在MySQL数据库环境中，处理并发更新问题是一个常见的挑战，特别是在高流量的应用中。本文将深入探讨如何使用`SELECT+UPDATE`策略解决这个问题，并分享两种具体的解决方案。 首先，让我们回顾一下问题的背景。假设...
MySQL select、insert、update批量操作语句代码实例
09-08
本篇文章将详细阐述MySQL中的`select`、`insert`和`update`批量操作语句，并通过具体的代码实例进行解析。 1. **批量insert插入语句** 在MySQL中，批量插入可以一次插入多行数据，减少与数据库的交互次数，提高...
MySQL SELECT同时UPDATE同一张表问题发生及解决
12-15
在MySQL中，当你尝试在`UPDATE`语句中同时使用`SELECT`来更新同一张表时，会遇到一个限制，即“你不能在`FROM`子句中指定要更新的目标表”。这是因为MySQL的语法规范不支持这样的操作，以避免数据更新的不确定性。...
mysql事务select for update及数据的一致性处理讲解
12-16
MySQL中的事务，默认是自动提交的，即autocommit = 1； 但是这样的话，在某些情形中就会...在MySQL的InnoDB中，预设的Tansaction isolation level 为REPEATABLE READ（可重读） 在SELECT 的读取锁定主要分为两种方式：
web小项目-曼波生日录(Servlet+JSP+MySQL)
m0_75138009的博客
07-24 882
当记录条数过多时会自动出现滚轮，数据不会超出紫框。
《MySQL DBA 修炼之道》第四章 开发进阶
Trishia的博客
07-25 626
博主学习《MySQL DBA 修炼之道》结合自身理解的总结。主要讲解了范式、反范式、慢SQL查询、事务和锁等。
测试环境搭建整套大数据系统（十七：mysql同步，字段类型映射错误问题）
weixin_43446246的博客
07-24 453
mysql表hive表其中 type是字符串类型，但是yarn上一直报错number这个类是往int类型转才会使用到的。
MySQL中的CREATE EVENT 语句详解
u011565038的博客
07-24 604
CREATE EVENT语句在 MySQL 中用于创建一个事件调度器（Event Scheduler）的事件。事件调度器允许你安排数据库任务（如查询、数据更新等）在将来的某个时间点自动执行，或者根据特定的时间间隔重复执行。这对于需要定期执行的任务特别有用，比如数据清理、报表生成等。
[Mysql-DML数据操作语句]
2301_78794952的博客
07-23 1088
insert into student (sid,sname,birthday,ssex,classid) values(9,'张三','2007-1-1','男',1);insert into student(sname,ssex) values('某某1','男'),('某某2','女'),('某某3','男');insert into student values(9,'张三','2007-1-1','男',1);-- 将姓名为某某的生日改为1888-1-1。
Mysql - 索引
m0_69594200的博客
07-23 971
存储引擎、SQL优化工具、SQL优化，索引的分类、语法和使用规则等【笔记】内容
详解Mysql InnoDB引擎 04
程序员进阶之路
07-24 704
InnoDB 是MySQL数据库管理系统的一个存储引擎，它为MySQL提供了事务处理、外键约束和并发控制等关系型数据库管理系统（RDBMS）中重要的功能。以下是关于InnoDB存储引擎的一些关键特点和特性：
Mysql中DML的几种操作
最新发布
m0_63227888的博客
07-25 363
DML（Data Manipulation Language，数据操纵语言）是SQL中用于添加、删除、更新和查询数据库记录的一类语句。
mysql的存储过程：
m0_71178834的博客
07-25 387
完成特点功能的sql语句的集合。把定义好的sql集合在一个特定的sql的函数当中每次执行调用函数即可，还可以实现传参的调用。
Mysql的索引
2401_84592402的博客
07-24 504
索引是一个排序的列表，在列表当中存储索引的值以及索引对应数据所在的物理行索引值和数据是一个映射关系。
mysqlbackup与xtrabackup的对比
cc20100608的专栏
07-24 341
在MySQL数据库管理中，备份是确保数据安全性和可恢复性的重要环节。在众多备份工具中，mysqlbackup和xtrabackup是两款备受关注的工具。本文将围绕这两款工具进行详细对比，以探讨它们的优缺点，并试图给出在何种场景下更适合使用哪款工具。
MySQL：增删改查、临时表、授权相关示例
变成我......
07-24 885
Linux系统下MySQL服务的增删改查、临时表、授权相关实例，以及相关概念的讲解
MySQL的存储过程
2402_84868182的博客
07-25 280
存储过程的概念：为了完成特定功能的sql语句的集合，把定义号的sql集合在一个特定的sql的函数当中，每次执行调用函数即可，还可以实现传参的调用。delimiter ： 开始和结束的语法$$：标志位，可以自定义，不要用汉字和数字以及特殊字符开头create procedure +存储过程的名称 （）beginselect * from 表名；show procedure status where db='库名';​。
centos7 mysql 基本测试（7）主从并行简单测试
abc
07-25 426
centos7 mysql 基本测试（7）主从并行简单测试。
MySQL表的增删查改
Xpccccc的博客
07-25 733
MySQL表的增删查改
在mysql中支持insert、delete、update和select四种触发器类型
05-01
在MySQL中，触发器是在特定的表上执行的一些自动化操作。MySQL支持四种触发器类型，分别是insert、delete、update和select。 当在MySQL表中插入一条数据时，可以在插入操作之前或之后执行一些自动化操作。这就是insert触发器。它允许开发人员在执行插入操作时，对数据进行额外的验证和处理。 delete触发器可以在从MySQL表中删除一条数据时执行自动化操作。有时在从表中删除数据之前，需要对其进行某些检查或操作。这是删除触发器的一个常见用例。 update触发器允许开发人员在MySQL表中更新数据时执行额外操作。例如，可以在更新一条记录之前进行特定的验证或操作。 select触发器在从MySQL表中提取数据时执行自动化操作。这些触发器在需要每次调用SELECT查询时执行的计算或逻辑方面非常有用。 总之，四种触发器类型允许开发人员在MySQL的表操作过程中增加自动化，并允许开发人员更好地控制他们的数据和操作。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Narutolxy

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值