MySQL——锁和事务管理
MySQL的锁 Lock
保证数据安全的一种手段。防止数据文件同时被多个用户同时修改，从而造成数据的破坏。
MySQL锁的分类：
-
读锁：S锁，例如给一个表加了读锁以后，大家都能读表里面的内容（只能读不能改）
-
写锁:X锁。加了写锁以后，只有自己才能访问，其他人不行（读和写都不行）
读锁和写锁是不兼容了，加了写锁就不能加读锁。
MySQL锁的范围：
-
表级锁:myisam吗，整个表全部都加锁，例如改其中一条记录会把整个表都锁起来。
-
行级锁：innodb，只锁定自己正在修改的那条记录。
实现锁这个功能：
-
显式实现：手动设置
-
隐式实现：通过存储引擎自动添加
手动实现锁的功能
加锁：
lock tables tb_name lock_type
lock_type:
READ #读锁
WRITE #写锁
解锁：
unlock lock_name
查询锁：
select claue
加全局读锁：
关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁来保证数据的只读性。
添加全局读锁：
FLUSH TABLES WITH READ LOCK
解锁：
unlock tables
MySQL事务 Transactions：一组原子性的SQL语句
事务的特性：ACID
-
A：atomicity 原子性；事务中的所有操作要么全部成功执行，要么全部失败后回滚
-
C：consistency 一致性；数据库总是从一个一致性状态转换为另一个一致性状态。
-
I：Isolation隔离性；事务执行操作的期间相互之间是隔离的，互不影响。
事务在执行期间产生的数据叫做脏数据（没有确定下来的数据） -
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中
事务的生命周期
-
刚开始数据库处于初始阶段
-
开启一个事务
-
进行相关操作（事务没有结束前都属于脏数据）
-
提交或者取消事务
-
一旦提交以后数据就存到了数据库，取消事务就回到了初始状态。
MySQL事务的管理：
- 启动事务：
三个都表示事务的开始
BEGIN
BEGIN WORK
START TRANSACTION
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from teachers where tid = 5 ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
- 结束事务：
只用DML这种SQL语言才能撤销。其他的不能撤销
#提交,相当于vi中的wq保存退出
COMMIT
#回滚,相当于vi中的q!不保存退出
ROLLBACK
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
mysql> delete from teachers;
Query OK, 4 rows affected (0.00 sec)
mysql> select * from teachers;
Empty set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
- 自动提交功能：更改变量autocommit的值
set autocommit={1|0}，默认是1
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
MySQL每一条独立的DML语句就是一个完整的事务。执行操作后就默认自动提交了。
- 查看事务：
死锁的概念：
两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态
事务的隔离级别：
隔离级别查看：
select @@tx_isolation;
这个变量支持四个值：
READ UNCOMMITTED：能看到脏数据
READ COMMITTED：不可重复度
REPEATABLE READ：幻读
SERIALIZABLE：可串行化
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
隔离级别比较
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 加读锁 |
|---|---|---|---|---|
| 读未提交 | 可以出现 | 可以出现 | 可以出现 | 否 |
| 读提交 | 不允许出现 | 可以出现 | 可以出现 | 否 |
| 可重复读 | 不允许出现 | 不允许出现 | 可以出现 | 否 |
| 序列化 | 不允许出现 | 不允许出现 | 不允许出现 | 是 |
例如：可重复读，一个事务将数据改变以后，即使提交了，另一个事务里面也不能看到事务发生的变化，只能看到刚开启事务时数据的状态。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into teachers(name,age,gender) values('bob',24,'M');
Query OK, 1 row affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
| 6 | bob | 24 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
| 6 | bob | 24 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
这个事务在执行过程中，永远看到的是自己刚开启事务时数据的状态。
但是这个数据早就发生改变了，这就是可重复读（幻读）。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
例如：不可重复读：只要数据发生了改变（已经提交），就能看到数据发生的改变
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
| 6 | bob | 24 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
mysql> update teachers set age=25 where tid=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
| 6 | bob | 25 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
| 6 | bob | 24 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
| 6 | bob | 25 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
| 6 | bob | 24 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
