mysql锁

1.全局锁

　　a. Flush tables with read lock (FTWRL)　　　　（解锁mysql> UNLOCK TABLES;）

　　mysqldump -uroot -p --single-transaction --all-databases --master-data > dbdump.db

　　b. set global readonly=true

 2.表级锁

　　a.表锁

　　lock table_1 write/read; 主要针对非事务型存储引擎

　　b.metadata lock(MDL)元数据锁

　　InnoDB存储引擎，不需要显示调用，事务开始时自动加上，主要是对DML和DDL冲突的保护

　　　　1. 读锁不互斥（增删改查DML）

　　　　2.读/写锁； 写锁/写锁相互互斥（DDL）

InnoDB举例：

session1: 拿到读锁，事务未提交

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dep_demo;
+------+--------------------+
| no   | name               |
+------+--------------------+
| d001 | Marketing          |
| d002 | Finance            |
+------+--------------------+
2 rows in set (0.00 sec)

其他两个会话阻塞一段时间后，提交事务

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

 

session2: DML修改表结构，添加字段f，阻塞，直至session1提交事务

mysql> alter table dep_demo add f int;
Query OK, 0 rows affected (29.69 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

session3: DDL查询表，阻塞，直至session1提交事务，返回session修改之前的结果，即没有字段f

mysql> select * from dep_demo;
+------+--------------------+
| no   | name               |
+------+--------------------+
| d001 | Marketing          |
| d002 | Finance            |
+------+--------------------+
2 rows in set (8.59 sec)

 

3.InnoDB行锁及其优化

1.两阶段锁协议，主要表锁和行锁的区别

行锁是需要是自动加上，直到事务提交以后才会自动释放。如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发的行放后面执行。

2.解决死锁方法

#1. # 查询全局等待事务锁超时时间, 默认开启50s
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout'; 
# 设置全局等待事务锁超时时间
SET  GLOBAL innodb_lock_wait_timeout=100;
# 查询当前会话等待事务锁超时时间
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

#2. #innodb_deadlock_detect 设置为 on，默认自动检测死锁以及自动回滚释放死锁

#3. 业务控制

 

问题：如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：

第一种，直接执行 delete from T limit 10000;

第二种，在一个连接中循环执行 20 次 delete from T limit 500;

第三种，在 20 个连接中同时执行 delete from T limit 500。

你会选择哪一种方法呢？为什么呢？ 

问题来自：极客时间版权所有: https://time.geekbang.org/column/article/70215 

 

我选第二种。

第一种，执行时间过长，可能影响其他业务的写操作，时间太长，不建议使用。

第二种，单线程执行，会与其他业务线程写操作产生竞争，但是会这种竞争相对是可容忍的。

第三种，多线程去，占用行锁，彼此之间就已经形成了竞争，还有其他业务也会与这20个线程竞争，我认为是最差的，结果很不可控。