【mysql 数据库事务】开启事务操作数据库，写入失败后，不回滚，会有问题么？这里隐藏着大坑，复试，面试时可以镇住面试老师！！！！

建表字段:

CREATE TABLE `user` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`nickname` VARCHAR(32) NOT NULL COLLATE 'utf8mb4_general_ci',
	`email` VARCHAR(32) NOT NULL COLLATE 'utf8mb4_general_ci',
	`status` SMALLINT(6) UNSIGNED NULL DEFAULT NULL,
	`password` VARCHAR(256) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	PRIMARY KEY (`id`) USING BTREE,
	UNIQUE INDEX `ix_user_nickname` (`nickname`) USING BTREE,
	UNIQUE INDEX `ix_user_email` (`email`) USING BTREE
)
COLLATE='utf8mb4_general_ci'

要注意status是unsigned的smallint
待会就从这里制造写入update失败

看下数据库的版本和隔离级别

select version();
select  @@transaction_isolation;

版本：5.7.26
隔离级别是默认的 RR：REPEATABLE-READ，四个隔离级别自行百度学习


begin;

INSERT INTO user values(NULL,7,7,7,7);
UPDATE user SET STATUS = -1 WHERE nickname=7;

COMMIT;
ROLLBACK;

插入 7777 然后 update status = -1 会失败
在这里插入图片描述

此时先select 表，


SELECT * FROM user;

在这里插入图片描述
可以看到当前session下是插入了。
再在python侧验证一下是否真的插入成功了：

是空。

然后再sqlyou管理端执行插入insert

INSERT INTO user values(NULL,8,8,8,8);

在这里插入图片描述
可以成功！但是这可能是当前session下的幻觉~ 再再python侧验证
still，空空入也

再再sqlyou 管理端执行rollback

在这里插入图片描述
7777 和 8888 都没有了。
其实不rollback的话，sqlyou侧只有在重启数据库后才能发现这个问题，或者重启一个session也能。
验证：

果然如此

TRUNCATE user
下面我再python侧单独写个case再验证下：


# @Time      :2024-2024/2/27-23:08
# @Author    :Justin
# @Email     :514422868@qq.com
# @file      :check_transition_mysql.py
# @Software  :01-fishbook
import pymysql

# 创建数据库连接
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='123456',
                             database='yushu_book1',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    # 创建游标
    with connection.cursor() as cursor:
        connection.begin()
        # 插入记录
        insert_query = "INSERT INTO user VALUES (NULL,7,7,7,7)"
        cursor.execute(insert_query)
        # 更新记录
        update_query = "UPDATE user SET status = -1 WHERE nickname=7"
        cursor.execute(update_query)
        connection.commit()  # 提交事务

except Exception as error:
    print(f"发生错误: {error}")

finally:
    print("-------")
    # connection = pymysql.connect(host='localhost',
    #                              user='root',
    #                              password='123456',
    #                              database='yushu_book1',
    #                              cursorclass=pymysql.cursors.DictCursor)
    # connection.rollback()
    with connection.cursor() as cursor:
        select_query = "select * from user where nickname='7'"
        cursor.execute(update_query)
        results = cursor.fetchall()

        print(results)
    # 关闭连接
    connection.close()

屏蔽 # connection.rollback() 用原先的connection是会报错

发生错误: (1264, "Out of range value for column 'status' at row 1")
-------
Traceback (most recent call last):
  File "D:\code\python_project\01-fishbook\test\check_transition_mysql.py", line 40, in <module>
    cursor.execute(update_query)
  File "C:\ProgramData\Anaconda3\lib\site-packages\pymysql\cursors.py", line 148, in execute
    result = self._query(query)
  File "C:\ProgramData\Anaconda3\lib\site-packages\pymysql\cursors.py", line 310, in _query
    conn.query(q)
  File "C:\ProgramData\Anaconda3\lib\site-packages\pymysql\connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "C:\ProgramData\Anaconda3\lib\site-packages\pymysql\connections.py", line 775, in _read_query_result
    result.read()
  File "C:\ProgramData\Anaconda3\lib\site-packages\pymysql\connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "C:\ProgramData\Anaconda3\lib\site-packages\pymysql\connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "C:\ProgramData\Anaconda3\lib\site-packages\pymysql\protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "C:\ProgramData\Anaconda3\lib\site-packages\pymysql\err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.DataError: (1264, "Out of range value for column 'status' at row 1")