知识点九：MySQL MyISAM表锁（共享读锁）(18)
为什么会有锁：
打个比方，我们到淘宝买一件商品，商品只有一件库存，这时候如果还有另外一个人也在买，那么如何解决是你买到还是另一个人买到的问题？
我们先从库存表中取出物品的数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个情况过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾！
锁的概念：
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性，有效性是所有数据库必须解决的
一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度说，锁对数据库而言显得尤其重要，也更加复杂。
MySQL锁的概述：
相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。
比如：MyISAM和MEMORY存储引擎采用的是表级锁(table- leavel locking）;；
BOD存储引擎采用的是页面锁（page- leavel locking）;
InnoDB存储引擎既支持行级锁（row-leavel locking），也支持表级锁，但默认情况下采用行级锁。
MySQL这三种锁的特性可大致归纳如下：
|
锁名称 |
开销 |
加锁速度 |
死锁 |
粒度 |
并发性能 |
|
表级锁 |
开销小 |
加锁快 |
不会出现死锁 |
锁定粒度大 |
发生锁冲突的概率最高，并发度最低 |
|
行级锁 |
开销大 |
加速慢 |
会出现死锁 |
锁定粒度最小 |
发生锁冲突的概率最低，并发度也最高 |
|
页面锁 |
开销和加锁时间界于表锁与行锁之间 |
会出现死锁 |
锁定粒度界于表锁和行锁之间 |
并发度一般 |
|
仅从锁的角度来说：
表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如WEB应用；
而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
从上述特点可见，很难笼统地说哪些锁更好，只能就具体应用的特点来说哪种锁更合适。
1.MyISAM表锁：
MySQL的表级锁有两种模式：表共享度锁(Table Read Lock)和表独占写锁(Table Write Lock)
MySQL中的表锁兼容性
|
请求锁模式 是否兼容 当前锁模式 |
None |
读锁 |
写锁 |
|
度锁 |
是 |
是 |
否 |
|
写锁 |
是 |
否 |
否 |
说明：
对于MyISAM表的读写操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表写的请求；
对MyISAM表的写的操作，则会阻塞其他用户对同一表的读和写的操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！
当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新的操作。其他线程的读，写操作都会等待，直到锁被释放为止。
一个session使用LOCK TABLE命令给表添加了读锁，这个session可以查询锁定表的记录，但更新或者访问其他表都会提示错误；同时，另外一个session可以查询表中的记录，但更新就会出现锁等待。
给表加锁：
1.加共享读锁：lock table 表名 read
2.表独占写锁：lock table 表名 read
解锁：
unlock tables;
查看表中有什么样的等待：
SHOW VARIABLES LIKE '%table%';
查看锁的状态：
SHOW STATUS LIKE '%table%';
查看表级锁的争用情况：
Table_locks_waited：越低越好
--把mzjj数据库到处并且添加到test_myisam
mysqldump -uroot -p --default-character-set=utf8 -d mzjj>D:/cc.sql --导出表信息
mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=utf8 mzjj>D:/cc1.sql --导出表内容
CTRATE DATABASE test_myisam --创建数据库
mysql -uroot -p test_myisam<D:/cc.sql --导入表信息
mysql -uroot -p test_myisam<D:/cc1.sql --导入表内容 --一客户端
LOCK TABLE employees read；
--二客户端
UPDATE employees SET first_name='yu' WHERE emp_no=1001;
--一客户端
UNLOCK TABLES; --二客户端
LOCK TABLE employees read；
--一客户端
INSERT INTO employees VALUES(1002,'1991-1-4','ty','io','M','2014-1-1'); SHOW VARIABLES LIKE '%table%';
SHOW STATUS LIKE '%table%';
--二客户端
UNLOCK TABLES;
共享读锁测试
知识点十：MySQL MyISAM表锁（独占写锁）(19，20)
MyISAM共享读锁：
1. 一个session使用LOCK TABLE 命令给表加了读锁，这个sessoin可以查询锁定表中的记录，但更新或者访问其他表都会提示错误。
2. 当使用LOCK TABLES是，不仅需要一次锁定用到的所有表，而且，同一个表的SQL语句中出现多少次，就要通过与SQL语句相同的别名锁定多少次，否侧也会出错。
格式：
LOCK TABLE 表名 AS 别名 READ;
-- 独占写锁
--一客户端
LOCK TABLE employees write;
--二客户端
SELECT * FROM employees; --读不了
--一客户端
INSERT INTO employees VALUES(1005,'1991-1-4','ty','io','M','2014-1-1');
UPDATE employees SET gender='F' WHERE emp_no=1005;
DELETE FROM employees WHERE emp_no=1005;
SELECT * FROM employees;
SHOW VARIABLES LIKE '%table%';
SHOW STATUS LIKE '%table%';
UNLOCK TABLES; --一客户端
LOCK TABLE employees READ;
SELECT * FROM employees AS a;--读不了
LOCK TABLE employees AS a READ;
SELECT * FROM employees AS a;
UNLOCK TABLES; --一客户端
LOCK TABLE employees READ;
SELECT * FROM employees;--可以读
SELECT * FROM salaries;--读不了，因为salaries没加锁
UNLOCK TABLES; --一客户端
LOCK TABLE employees WRITE;
SELECT * FROM employees;--可以读
SELECT * FROM salaries;--读不了，因为salaries没加锁
UNLOCK TABLES; ---一客户端
LOCK TABLE employees READ,salaries WRITE;
--一，二客户端都试
SELECT * FROM employees;
SELECT * FROM salaries;
---一客户端
UNLOCK TABLES;
独占写锁测试
独占写锁之并发插入数据：
并发插入（Concurrent Inserts）：
上文提到过MyISAM表的读和写是串行的，但这是就总体而言的。在一定条件下，MyISAM表也支持查询和插入操作的并发进行。
MyISACM存储引擎有一个系统变量concurrent_inserts,专门用以控制其并发插入的行为，其值分别为0，1或2.
1. 当concurrent_inserts设置为0(NEVER)时，不允许并发插入；
2.当concurrent_inserts设置为1(AUTO)时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。也就是MySQL的默认设置。
3. 当concurrent_inserts设置为2(ALWAYS)时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。
设置并发操作：
查看并发操作默认值 SHOW VARIABLES LIKE 'concurrent_insert'; 修改默认值为2： SET GLOBAL concurrent_insert=2;
设置并发插入格式：
LOCK TABLE tbl_name READ LOCAL;
-- 并发操作
---一客户端
SHOW VARIABLES LIKE 'concurrent_insert';
SET GLOBAL concurrent_insert=2; LOCK TABLE employees READ LOCAL;
INSERT INTO employees VALUES(1004,'1991-1-4','ty','io','M','2014-1-1');--插入不了
---二客户端
INSERT INTO employees VALUES(1004,'1991-1-4','ty','io','M','2014-1-1');--成功了
---一，二客户端
SELECT * FROM employees;--查询结果是不一样的 ---一，二客户端
UPDATE employees SET gender='F' WHERE emp_no=1001;--更新结果是不一样的，都不行
---一客户端
UNLOCK TABLES; ---一客户端
SET GLOBAL concurrent_insert=1; LOCK TABLE employees READ LOCAL; INSERT INTO employees VALUES(1009,'1991-1-4','ty','io','M','2014-1-1');--插入不了
---二客户端
INSERT INTO employees VALUES(1009,'1991-1-4','ty','io','M','2014-1-1');--成功了
并发操作测试
MyISAM锁调度：
思考：
一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁。MyISAM如何处理？
答案：
写进程先获得锁，即使读请求先到锁队列排队，写请求后到，写锁也会插入到读锁队列。MyISAM认为写请求比读请求更重要。MyISAM不太适合于有大量更新操作和查询操作的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。
解决方案：
执行SET LOW_PRIORITY_UPDATES =1;使该连接发出的更新请求优先级降低。
其中INSERT，DELETE也可以通过此方法指定。