3.4 锁机制 事务
MySQL锁机制
锁：
读锁：共享锁，也成为s锁，只读不可写（包括当前事务），多个读互不阻塞
写锁：独占锁，排它锁，也成为X锁，写锁会阻塞其它事务（不包括当前事务）的读和写
举例：
读锁和读锁是兼容的，写锁和其它锁不兼容
事务T1获取了一个行r1的读锁，另外事务T2可以立即获得r1的读锁，此时T1和T2共同获得行r1的读锁，此种情况成为锁兼容
但是另外一个事务T3如果想获得行r1的写锁，则必须等待T1对行r1读锁的释放，此种情况也成为锁冲突。
锁粒度：
表级锁：MyISAM
行级锁：InnoDB
实现：
存储引擎：自行实现其锁策略和锁粒度
服务器级：实现了锁，表级锁，用户可显示请求
分类：
隐式锁：由存储引擎自动施加锁
显式锁：用户手动请求
锁策略：在锁粒度及数据安全性寻求的平衡机制
锁优缺点：
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
案例一： 需求：将投资的钱拆成几份随机分配给借款人。 起初业务程序思路是这样的： 投资人投资后，将金额随机分为几份，然后随机从借款人表里面选几个，然后通过一条条select for update 去更新借款人表里面的余额等。 抽象出来就是一个session通过for循环会有几条如下的语句： Select * from xxx where id='随机id' for update 基本来说，程序开启后不一会就死锁。 这可以是说最经典的死锁情形了。 例如两个用户同时投资，A用户金额随机分为2份，分给借款人1，2 B用户金额随机分为2份，分给借款人2，1 由于加锁的顺序不一样，死锁当然很快就出现了。
测试单个会话读锁： 1,对表date加读锁 lock table date read; 2,执行读操作和写操作 select * from date; insert into date (name) values ("cccc");
测试多个会话读锁： 会话1 1.对表table读锁 lock table date read; 会话2 1.查看当前能否读取和写入 2.对表table读锁（能否成功） lock table date read; lock table date write; 在会话1和2测试读取写入： 当会话2 unlock tables,会话1的读锁还在么 写锁能否成功？
测试单个会话写锁：
测试多个会话写锁：
事务：
有事务之前：
数据库修改一条数据是直接在内存中修改然后写入数据库文件
有事务之后：
数据库修改的数据先存入事务日志（告诉用户成功），然后再存入数据库文件
日志是顺序写的，更快（更详细），
事务Transactions：一组原子性的SQL语句，或一个独立工作单元
事务日志：记录事务信息，实现undo,redo等故障恢复功能
事务特性
ACID特性：
A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态
I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中
事务生命周期
管理事务
显示启动事务
BEGIN BEGIN WORK START TRANSCATION
结束事务
#提交 COMMIT #回滚 ROLLBACK
注意：只有事务型存储引擎中的DML语句才能支持此类操作
自动提交
set autocommit={1|0} 默认为1，为0时非自动提交 建议：显示请求和提交事务，而不要使用自动提交功能
事务支持保存点
SAVEPOINT identifier ROLLBACK [WORK] TO [SAVEPOINT] identifier RELEASE SAVEPOINT identifier
查看事务
#查看当前正在进行的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
#查看当前锁定的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
#查看当前等锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
范例：找到未完成的导致阻塞的事务
#在第一个会话中开启一个事务 mysql> use db1; Database changed mysql> update date set name="centos";^C mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update date set name="centos"; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from date; +----+--------+--------+--------+--------+------+-------+---------+-------+-------+-------+ | id | name | gender | height | weight | bust | hobby | address | years | dates | times | +----+--------+--------+--------+--------+------+-------+---------+-------+-------+-------+ | 1 | centos | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 2 | centos | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 4 | centos | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +----+--------+--------+--------+--------+------+-------+---------+-------+-------+-------+ 3 rows in set (0.00 sec)
#在第二个会话中执行（不需要开启一个事务） mysql> use db1; Database changed mysql> select * from date; +----+----------+--------+--------+--------+------+-------+---------+-------+-------+-------+ | id | name | gender | height | weight | bust | hobby | address | years | dates | times | +----+----------+--------+--------+--------+------+-------+---------+-------+-------+-------+ | 1 | huakai1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 2 | yase2021 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 4 | dd | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +----+----------+--------+--------+--------+------+-------+---------+-------+-------+-------+ 3 rows in set (0.00 sec) mysql> update date set name="rhel";
#在运维管理会话
mysql> show engine innodb status\G;
···
------------
TRANSACTIONS
------------
Trx id counter 131099
Purge done for trx's n:o < 131097 undo n:o < 0 state: running but idle
History list length 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421830269083248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421830269082336, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 131097, ACTIVE 127 sec
2 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 3
MySQL thread id 11, OS thread handle 140354841700096, query id 163 localhost root
Trx read view will not see trx with id >= 131098, sees < 131098
···
查看当前正在进行的事务
mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
trx_id: 131097
trx_state: RUNNING
trx_started: 2021-03-12 11:13:14
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 5
trx_mysql_thread_id: 11
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 4
trx_rows_modified: 3
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
查看当前锁定的事务
mysql> select * from information_schema.innodb_locks\G;
*************************** 1. row ***************************
lock_id: 131099:335:3:2
lock_trx_id: 131099
lock_mode: X
lock_type: RECORD
lock_table: `db1`.`date`
lock_index: PRIMARY
lock_space: 335
lock_page: 3
lock_rec: 2
lock_data: 1
*************************** 2. row ***************************
lock_id: 131097:335:3:2
lock_trx_id: 131097
lock_mode: X
lock_type: RECORD
lock_table: `db1`.`date`
lock_index: PRIMARY
lock_space: 335
lock_page: 3
lock_rec: 2
lock_data: 1
2 rows in set, 1 warning (0.00 sec)
ERROR:
No query specified
查看当前等锁的事务
mysql> select * from information_schema.innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 131100
requested_lock_id: 131100:335:3:2
blocking_trx_id: 131097
blocking_lock_id: 131097:335:3:2
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
#在运维管理会话中查看 mysql> show processlist; +----+------+-----------+------+---------+------+----------+-----------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------+-----------------------------+ | 9 | root | localhost | NULL | Query | 0 | starting | show processlist | | 10 | root | localhost | db1 | Query | 6 | updating | update date set name="rhel" | | 11 | root | localhost | db1 | Sleep | 574 | | NULL | +----+------+-----------+------+---------+------+----------+-----------------------------+ 3 rows in set (0.00 sec) 杀死lock： mysql >kill 11; 可以通过show processlist和查看系统事务得到 #查看事务锁的超时时长，默认50s mysql> show global variables like 'innodb_lock_wait_timeout'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 50 | +--------------------------+-------+ 1 row in set (0.00 sec)
MySQL 支持四种隔离级别，事务隔离级别：从上至下更加严格
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 加读锁 |
| 读未提交 | 可以出现 | 可以出现 | 可以出现 | 否 |
| 读提交 | 不允许出现 | 可以出现 | 可以出现 | 否 |
| 可重复读 | 不允许出现 | 不允许出现 | 可以出现 | 否 |
| 序列化 | 不允许出现 | 不允许出现 | 可以出现 | 是 |
READ UNCOMMITTED 可读取到未提交数据，产生脏读 READ COMMITTED 可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致 REPEATABLE READ 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置 SERIALIZABLE 可串行化，未提交的读事务阻塞修改事务（加读锁，但不阻塞读事务），或者未提交的修改事务阻塞读事务（加写锁，其它事务的读，写都不可以执行）。会导致并发性能差
MVCC和事务的隔离级别： MVCC（多版本并发控制机制）只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁
