# 查看锁等待情况
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE ‘Innodb_row_lock%’;
+——————————-+——-+
| Variable_name | Value |
+——————————-+——-+
| Innodb_row_lock_current_waits | 5 |
| Innodb_row_lock_time | 10000 |
| Innodb_row_lock_time_avg | 2000 |
| Innodb_row_lock_time_max | 5000 |
| Innodb_row_lock_waits | 100 |
+——————————-+——-+
# 查看正在执行的事务
SHOW PROCESSLIST;
+—-+——+———–+——–+———+——+———-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+——–+———+——+———-+——————+
| 1 | root | fgedu.localhost | fgedudb | Sleep | 10 | | NULL |
| 2 | root | fgedu.localhost | fgedudb | Query | 5 | Updating | UPDATE fgedu_users SET name = ‘test’ WHERE id = 1 |
| 3 | root | fgedu.localhost | fgedudb | Query | 3 | Waiting | UPDATE fgedu_users SET name = ‘test2’ WHERE id = 1 |
+—-+——+———–+——–+———+——+———-+——————+
# 分析锁竞争原因
# 发现两个事务同时更新同一行数据，导致锁竞争

解决方案：

# 优化事务，减少事务长度
# 示例：将长事务拆分为短事务
START TRANSACTION;
UPDATE fgedu_users SET name = ‘test’ WHERE id = 1;
COMMIT;
# 合理使用索引，避免全表扫描
# 确保WHERE条件使用索引
EXPLAIN UPDATE fgedu_users SET name = ‘test’ WHERE id = 1;
+——+————-+————+——-+—————+———+———+——-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+————-+————+——-+—————+———+———+——-+——+——-+
| 1 | SIMPLE | fgedu_users | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+——+————-+————+——-+—————+———+———+——-+——+——-+

# 查看当前事务隔离级别
MariaDB [(none)]> SELECT @@tx_isolation;
+—————–+
| @@tx_isolation |
+—————–+
| REPEATABLE-READ |
+—————–+
# 修改事务隔离级别为READ COMMITTED
SET GLOBAL transaction_isolation = ‘READ-COMMITTED’;
# 查看修改后的事务隔离级别
SELECT @@tx_isolation;
+—————-+
| @@tx_isolation |
+—————-+
| READ-COMMITTED |
+—————-+
# 优化查询语句，使用索引
CREATE INDEX idx_user_id ON fgedu_orders(user_id);
# 分析查询执行计划
EXPLAIN SELECT * FROM fgedu_orders WHERE user_id = 100;
+——+————-+————+——+—————+———+———+——-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+————-+————+——+—————+———+———+——-+——+——-+
| 1 | SIMPLE | fgedu_orders | ref | idx_user_id | idx_user_id | 4 | const | 10 | |
+——+————-+————+——+—————+———+———+——-+——+——-+

执行结果：

Query OK, 0 rows affected (0.00 sec)
+—————-+
| @@tx_isolation |
+—————-+
| READ-COMMITTED |
+—————-+
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
+——+————-+————+——+—————+———+———+——-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+————-+————+——+—————+———+———+——-+——+——-+
| 1 | SIMPLE | fgedu_orders | ref | idx_user_id | idx_user_id | 4 | const | 10 | |
+——+————-+————+——+—————+———+———+——-+——+——-+

# 查看死锁情况
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE ‘Innodb_deadlocks’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| Innodb_deadlocks | 5 |
+——————+——-+
# 查看死锁日志
SHOW ENGINE INNODB STATUS\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
======================================
2023-01-01 00:00:00 0x7f1234567890 INNODB MONITOR OUTPUT
======================================
Per second averages calculated from the last 30 seconds
————————
LATEST DETECTED DEADLOCK
————————
2023-01-01 00:00:00 0x7f1234567890
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 2, OS thread handle 140737354123456, query id 100 fgedu.localhost root updating
UPDATE fgedu_users SET name = ‘test’ WHERE id = 1
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 4 n bits 72 index PRIMARY of table `fgedudb`.`fgedu_users` trx id 12345 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5;
compact format;
info bits 0
0: len 4;
hex 80000001;
asc;
1: len 6;
hex 000000003039;
asc 09;
2: len 7;
hex b60000019d0110;
asc;
3: len 4;
hex 74657374;
asc test;
4: len 8;
hex 80000000000003e8;
asc;
*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3, OS thread handle 140737354123457, query id 101 fgedu.localhost root updating
UPDATE fgedu_users SET name = ‘test2’ WHERE id = 2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 4 n bits 72 index PRIMARY of table `fgedudb`.`fgedu_users` trx id 12346 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5;
compact format;
info bits 0
0: len 4;
hex 80000002;
asc;
1: len 6;
hex 00000000303a;
asc 0:;
2: len 7;
hex b60000019d0111;
asc;
3: len 5;
hex 7465737432;
asc test2;
4: len 8;
hex 80000000000003e9;
asc;
*** WE ROLL BACK TRANSACTION (2)

解决方案：

# 分析死锁原因：两个事务相互等待对方释放锁
# 解决方法1：调整事务操作顺序，避免循环等待
# 示例：统一按照相同的顺序操作资源
START TRANSACTION;
UPDATE fgedu_users SET name = ‘test’ WHERE id = 1;
UPDATE fgedu_users SET name = ‘test2’ WHERE id = 2;
COMMIT;
# 解决方法2：减少事务长度，尽快释放锁
# 示例：将长事务拆分为短事务
START TRANSACTION;
UPDATE fgedu_users SET name = ‘test’ WHERE id = 1;
COMMIT;
START TRANSACTION;
UPDATE fgedu_users SET name = ‘test2’ WHERE id = 2;
COMMIT;
# 解决方法3：使用乐观锁
# 示例：使用版本号或时间戳
UPDATE fgedu_users SET name = ‘test’, version = version + 1 WHERE id = 1 AND version = 1;