1、锁机制

锁类型：
- 读锁：共享锁，也称为 S 锁,只读不可写（包括当前事务），多个读互不阻塞
- 写锁：独占锁，排它锁，也称为 X 锁,写锁会阻塞其它事务（不包括当前事务）的读和写
- S 锁和 S 锁是兼容的，X 锁和其它锁都不兼容，举个例子，事务 T1 获取了一个行 r1 的 S 锁，另外事务 T2 可以立即获得行 r1 的 S 锁，此时 T1 和 T2 共同获得行 r1 的 S 锁，此种情况称为锁兼容，但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁，则必须等待 T1 对行 r1 锁的释放，此种情况也称为锁冲突
锁粒度：
- 表级锁：MyISAM
- 行级锁：InnoDB
实现
- 存储引擎：自行实现其锁策略和锁粒度
- 服务器级：实现了锁，表级锁，用户可显式请求
分类：
- 隐式锁：由存储引擎自动施加锁
- 显式锁：用户手动请求
锁策略：在锁粒度及数据安全性寻求的平衡机制

2、显式使用锁

# 加锁
LOCK TABLES tbl_name [[AS] alias] lock_type  [, tbl_name [[AS] alias]
lock_type] ...

lock_type:
READ   #读锁
WRITE  #写锁

# 解锁
UNLOCK TABLES

# 关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁
FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]

@ # 解锁
UNLOCK TABLES

# 关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁
FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]

# # 解锁
UNLOCK TABLES

# 关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁
FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]

# 查询时加写或读锁
SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

# 加读锁
mysql> lock tables students read ;
Query OK, 0 rows affected (0.00 sec)

mysql> update students set classid=2 where stuid=24;
ERROR 1099 (HY000): Table 'students' was locked with a READ lock and can't be
updated

mysql> unlock tables ;

mysql> update students set classid=2 where stuid=24;
Query OK, 1 row affected (1 min 45.52 sec)
Rows matched: 1 Changed: 1 Warnings: 0

# 同时在两个终端对同一行记录修改
#在第一终端提示1行成功
MariaDB [hellodb]> update students set classid=1 where stuid=24;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0

#在第二终端提示0行修改
MariaDB [hellodb]> update students set classid=1 where stuid=24;
Query OK, 0 rows affected (0.000 sec)
Rows matched: 1 Changed: 0 Warnings: 0

3、事务

事务 Transactions：一组原子性的 SQL语句，或一个独立工作单元
事务日志：记录事务信息，实现undo，redo等故障恢复功能

3.1、事务特性

ACID特性：

A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态,类似于能量守恒定律
I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

3.2、管理事务

# 显式启动事务
BEGIN
BEGIN WORK
START TRANSACTION

# 结束事务
# 提交,相当于vi中的wq保存退出
COMMIT

# 回滚,相当于vi中的q!不保存退出
ROLLBACK

# 只有事务型存储引擎中的DML语句方能支持此类操作

# 自动提交
set autocommit={1|0}

# 默认为1，为0时设为非自动提交
# 建议使用显式请求和提交事务，而不要使用"自动提交"功能

# 事务支持保存点
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

# 查看事务
# 查看当前正在进行的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

# 以下两张表在MySQL8.0中已取消
# 查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
# 查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

# 死锁
# 两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态

# 找到未完成的导致阻塞的事务(支持Mariadb)
# 在第一会话中执行
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update students set classid=10;
Query OK, 25 rows affected (0.00 sec)
Rows matched: 25  Changed: 25  Warnings: 0

# 在第二个会话中执行
mysql> update students set classid=20;

# 在第三个会话中执行
mysql> show engine innodb status;
---TRANSACTION 1873, ACTIVE 77 sec
2 lock struct(s), heap size 1136, 26 row lock(s), undo log entries 25
MySQL thread id 2, OS thread handle 139689989875456, query id 151 localhost root

# 此指令不支持MySQL8.0
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+-------------+-------------+-----------+-----------+----------------------+------------+------------+-----------+----------+-----------+
| lock_id     | lock_trx_id | lock_mode | lock_type | lock_table           | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------+-------------+-----------+-----------+----------------------+------------+------------+-----------+----------+-----------+
| 1875:37:3:2 | 1875        | X         | RECORD    | `hellodb`.`students` | PRIMARY    |         37 |         3 |        2 | 1         |
| 1873:37:3:2 | 1873        | X         | RECORD    | `hellodb`.`students` | PRIMARY    |         37 |         3 |        2 | 1         |
+-------------+-------------+-----------+-----------+----------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

# 此指令不支持MySQL8.0
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 1875              | 1875:37:3:2       | 1873            | 1873:37:3:2      |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

# 查看正在进行的事务
mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 1875
                 trx_state: LOCK WAIT
               trx_started: 2022-11-18 22:25:08
     trx_requested_lock_id: 1875:37:3:2
          trx_wait_started: 2022-11-18 22:25:08
                trx_weight: 2
       trx_mysql_thread_id: 7
                 trx_query: update students set classid=20
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 0
   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
*************************** 2. row ***************************
                    trx_id: 1873
                 trx_state: RUNNING
               trx_started: 2022-11-18 22:22:09
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 27
       trx_mysql_thread_id: 2
                 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: 26
         trx_rows_modified: 25
   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
2 rows in set (0.00 sec)

mysql>  show processlist;
+----+------+-----------+---------+---------+------+----------+--------------------------------+
| Id | User | Host      | db      | Command | Time | State    | Info                           |
+----+------+-----------+---------+---------+------+----------+--------------------------------+
|  2 | root | localhost | hellodb | Sleep   |  214 |          | NULL                           |
|  7 | root | localhost | hellodb | Query   |   35 | updating | update students set classid=20 |
|  8 | root | localhost | hellodb | Query   |    0 | starting | show processlist               |
+----+------+-----------+---------+---------+------+----------+--------------------------------+
3 rows in set (0.00 sec)

# 杀掉未完成的事务
mysql> kill 2;

# 查看事务锁的超时时长，默认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)

3.3、事务隔离级别

MySQL 支持四种隔离级别，事务隔离级别从上至下更加严格

READ UNCOMMITTED
- 可读取到未提交数据，产生脏读
READ COMMITTED
- 可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致
REPEATABLE READ
- 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
SERIALIZABLE
- 可串行化，未提交的读事务阻塞写事务（加读锁，但不阻塞读事务），或者未提交的写事务阻塞读和写事务（加写锁，其它事务的读，写都不可以执行）。会导致并发性能差

MVCC和事务的隔离级别：

MVCC（多版本并发控制机制）只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容，因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁

# 指定事务隔离级别
# 变量tx_isolation(MySQL8.0改名为transaction_isolation)指定，默认为REPEATABLEREAD，可在GLOBAL和SESSION级进行设置

# MySQL8.0之前版本
SET tx_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLEREAD|SERIALIZABLE'

# MySQL8.0
SET transaction_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLEREAD|SERIALIZABLE'




# 服务器选项中指定
vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE