mysql 事务初步了解_事务中正在操作某一条数据其他-优快云博客

本文链接：https://blog.youkuaiyun.com/caideb/article/details/84951873

本文深入探讨MySQL中的事务管理，包括如何使用begin、commit和rollback控制事务，以及事务对数据操作的影响。通过具体示例，展示了在commit前可以回滚事务，以及在事务中操作的数据对外部是不可见的特性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1关键字begin打开事务，中间执行相关sql语句，最后commit提交完成事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student (stu_id,name,c_id) values ('100','soul','1');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from student where stu_id='100';
+--------+------+------+
| stu_id | name | c_id |
+--------+------+------+
| 100    | soul | 1    |
+--------+------+------+
1 row in set (0.00 sec)

2.在事务中执行的sql语句在commit之前可以回滚rollback

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from student;
Query OK, 7 rows affected (0.03 sec)

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.06 sec)

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

3.在事务中操作某条数据时，其他sql语句无法对其操作

事务内插入数据，外部不可见

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student (stu_id,name,c_id) values ('101','soul','1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student where stu_id="101";
+--------+------+------+
| stu_id | name | c_id |
+--------+------+------+
| 101    | soul | 1    |
+--------+------+------+
1 row in set (0.00 sec)

mysql> select * from student where stu_id="101";
Empty set (0.00 sec)

事务内修改数据，外部无法同时修改，会一直阻塞到commit

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update student set name="abc" where stu_id="101";
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update student set name="ccc" where stu_id="101";
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction