当前位置：首页 > news >正文

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

news 2025/8/26 20:36:33

建表字段:

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 executeresult = self._query(query)File "C:\ProgramData\Anaconda3\lib\site-packages\pymysql\cursors.py", line 310, in _queryconn.query(q)File "C:\ProgramData\Anaconda3\lib\site-packages\pymysql\connections.py", line 548, in queryself._affected_rows = self._read_query_result(unbuffered=unbuffered)File "C:\ProgramData\Anaconda3\lib\site-packages\pymysql\connections.py", line 775, in _read_query_resultresult.read()File "C:\ProgramData\Anaconda3\lib\site-packages\pymysql\connections.py", line 1156, in readfirst_packet = self.connection._read_packet()File "C:\ProgramData\Anaconda3\lib\site-packages\pymysql\connections.py", line 725, in _read_packetpacket.raise_for_error()File "C:\ProgramData\Anaconda3\lib\site-packages\pymysql\protocol.py", line 221, in raise_for_errorerr.raise_mysql_exception(self._data)File "C:\ProgramData\Anaconda3\lib\site-packages\pymysql\err.py", line 143, in raise_mysql_exceptionraise errorclass(errno, errval)
pymysql.err.DataError: (1264, "Out of range value for column 'status' at row 1")