mysql 事务初步了解

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