锁
锁的简介
为什么需要锁？
假如到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？
锁的概念
锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
锁对数据库而言显得尤其重要，也更加复杂。
MySQL 中的锁
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁(gap 锁,间隙锁)：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
在这个部分只讲表级锁、行级锁，gap 锁放到事务中讲 。
表锁与行锁的使用场景
表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如 OLAP 系统；
行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适 。
MyISAM 锁
MySQL 的表级锁有两种模式：
表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）
共享读锁
以testmysam这张表为例，account是另一张表；
语法：lock table 表名 read ;
1. lock table testmysam read ; 启动另外一个 session select * from testmysam ; --可以查询
2. insert into testmysam value(2);
update testmysam set id=2 where id=1; --报错
3.在另外一个 session 中:
insert into testmysam value(2); --等待
4.在同一个 session 中:
insert into account value(4,'aa',123); --报错
select * from account ; --报错
5.在另外一个 session 中:
insert into account value(4,'aa',123); --成功
6.加锁在同一个 session 中:
select s.* from testmysam s ; --报错
lock table 表名 as 别名 read;
独占写锁
语法：lock table 表名 WRITE；
1.lock table testmysam WRITE ；
在同一个 session 中:
insert testmysam value(3);
delete from testmysam where id = 3 ;
select * from testmysam ;
2.对不同的表操作（报错）
select s.* from testmysam s ;
insert into account value(4,'aa',123);
3.在其他 session 中 （等待）
select * from testmysam ;
总结：
读锁，对 MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
读锁，对 MyISAM 表的读操作，不会阻塞当前 session 对表读，当对表进行修改会报错
读锁，一个 session 使用 LOCK TABLE 命令给表 f 加了读锁，这个 session 可以查询锁定表中的记录，但更新或访问其他表都会提示错误
写锁，对 MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作
写锁，对 MyISAM 表的写操作，当前 session 可以对本表做 CRUD,但对其他表进行操作会报错
InnoDB 锁
在 mysql 的 InnoDB 引擎支持行锁;
共享锁又称：读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。
排它锁又称：写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。
语法:
上共享锁的写法：lock in share mode ;
例如： select * from 表 where 条件 lock in share mode；
上排它锁的写法：for update ;
例如：select * from 表 where 条件 for update；
注意：
1.两个事务不能锁同一个索引。
2.insert ，delete ， update 在事务中都会自动默认加上排它锁。
3.行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。
CREATE TABLE testdemo (
`id` int(255) NOT NULL , `c1` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL , `c2` int(50) NULL DEFAULT NULL , PRIMARY KEY (`id`), INDEX `idx_c2` (`c2`) USING BTREE
)ENGINE=InnoDB;
insert into testdemo VALUES(1,'1',1),(2,'2',2);
1. BEGIN
select * from testdemo where id =1 for update ;
在另外一个 session 中
update testdemo set c1 = '1' where id = 2; 成功
update testdemo set c1 = '1' where id = 1; 等待
2.BEGIN
update testdemo set c1 = '1' where id = 1;
在另外一个 session 中
update testdemo set c1 = '1' where id = 1; 等待
3. BEGIN
update testdemo set c1 = '1' where c1 = '1'; 在另外一个 session 中
update testdemo set c1 = '2' where c1 = '2'; 等待
4.第一个 session 中
select * from testdemo where id =1 for update;
第二个 session
select * from testdemo where id =1 lock in share mode;
回到第一个 session UNLOCK TABLES 并不会解锁
使用 commit 或者 begin 或者 ROLLBACK 才会解锁
5.再来看下表锁
lock table testdemo WRITE ;
使用 commit，ROLLBACK 并不会解锁
使用 UNLOCK TABLES 或者 begin 会解锁
锁的等待问题
好了，掌握了上面这些，你对锁的了解已经超过了很多人，那么现在来说一个实际的问题，在工作中经常一个数据被锁住，导致另外的操作完全进行不下去。
你肯定碰到过这问题，有些程序员在 debug 程序的时候，经常会锁住一部分数据库的数据，而这个时候你也要调试这部分功能，却发现代码总是运行超时，你是否碰到过这问题了，其实这问题的根源我相信你也知道了。
举例来说，有两个会话。
程序员甲，正直调试代码：
BEGIN；
SELECT * FROM testdemo WHERE id = 1 FOR UPDATE；
你正直完成的功能也要经过那部分的代码，你得上个读锁：
BEGIN；
SELECT * FROM testdemo WHERE id = 1 lock in share mode；
这个时候很不幸，你并不知道发生了什么问题，在你调试得过程中永远就是一个超时得异常，而这种问题不管在开发中还是在实际项目运行中都可能会碰到，那么怎么排查这个问题呢？
这其实也是有小技巧的。首先:
select * from information_schema.INNODB_LOCKS;
真好，我通过这个 sql 语句起码发现在同一张表里面得同一个数据有了 2 个锁其中一个是 X（写锁），另外一个是 S（读锁），我可以跳过这一条数据，使用其他数据做调试;
可能如果我就是绕不过，一定就是要用这条数据呢？吼一嗓子吧（哪个缺德的在 debug 这个表，请不要锁这不动），好吧，这是个玩笑，其实还有更好的方式来看。
执行sql：select * from sys.innodb_lock_waits;
我现在执行的这个 sql 语句有了，另外看下最下面，kill 命令，你在工作中完全可以通过 kill 把阻塞了的 sql 语句给干掉，你就可以继续运行了，不过这命令也要注意使用过，如果某同事正在做比较重要的调试，你 kill，被他发现可能会被暴打一顿。
上面的解决方案不错，但如果你的 MySQL 不是 5.7 的版本呢？是 5.6 呢，你根本就没有 sys库，这个时候就难办了，不过也是有办法的。执行sql:
SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread
FROM
information_schema.innodb_lock_waits w
INNER JOIN
information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN
information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
看到没有，接下来你也可以执行 kill 29 这样的大招了，前提是别人并没有在调试很重要的东西的时候。
事务
为什么需要事务?
现在的很多软件都是多用户，多程序，多线程的，对同一个表可能同时有很多人在用，为保持数据的一致性，所以提出了事务的概念。
A 给 B 要划钱，A 的账户-1000 元， B 的账户就要+1000 元，这两个 update 语句必须作为一个整体来执行，不然 A 扣钱了，B 没有加钱这种情况很难处理。
什么存储引擎支持事务
1.查看数据库下面是否支持事务（InnoDB 支持）？
show engines;
2.查看 mysql 当前默认的存储引擎？
show variables like '%storage_engine%';
3.查看某张表的存储引擎？
show create table 表名 ;
4.对于表的存储结构的修改？
建立 InnoDB 表：Create table .... type=InnoDB； Alter table table_name type=InnoDB;
事务特性
事务应该具有 4 个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为 ACID 特性。
原子性（atomicity）
一致性（consistency）
隔离性（isolation）
持久性（durability）
原子性（atomicity）
一个事务必须被视为一个不可分割的最小单元，整个事务中的所有操作要么全部提交成功，要么全部失败，对于一个事务来说，不可能只执行其中的一部分操作。
一致性（consistency）
一致性是指事务将数据库从一种一致性转换到另外一种一致性状态，在事务开始之前和事务结束之后数据库中数据的完整性没有被破坏。
持久性（durability）
一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，已经提交的修改数据也不会丢失。
隔离性（isolation）
一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。（对数据库的并行执行，应该像串行执行一样）
事务隔离级别：
未提交读（READ UNCOMMITED）脏读
已提交读 （READ COMMITED）不可重复读
可重复读（REPEATABLE READ）
可串行化（SERIALIZABLE）
mysql 默认的事务隔离级别为 repeatable-read ；
show variables like '%tx_isolation%';
事务并发问题
脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。
不可重复读：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果 不一致。
幻读：系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现。还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。
未提交读（READ UNCOMMITED）脏读
show variables like '%tx_isolation%';
set SESSION TRANSACTION ISOLATION LEVEL read UNCOMMITTED;
一个 session 中：start TRANSACTION ； update account set balance = balance -50 where id = 1；
另外一个 session 中查询：select * from account；
回到第一个 session 中 回滚事务：ROLLBACK；
在第二个 session 中：select * from account；
在另外一个 session 中读取到了为提交的数据，这部分的数据为脏数据。
已提交读 （READ COMMITED）不可重复读
show variables like '%tx_isolation%';
set SESSION TRANSACTION ISOLATION LEVEL read committed;
一个 session中：start TRANSACTION ；update account set balance = balance -50 where id = 1 ；
另外一个 session中查询 (数据并没改变)：select * from account；
回到第一个session中 回滚事务：commit；
在第二个session中：select * from account (数据已经改变)。
可重复读（REPEATABLE READ）
show variables like '%tx_isolation%';
set SESSION TRANSACTION ISOLATION LEVEL repeatable read;
一个session中：start TRANSACTION ；update account set balance = balance -50 where id = 1；
另外一个 session中查询 (数据并没改变)：select * from account；
回到第一个session中回滚事务：commit；
在第二个session中：select * from account (数据并未改变)。
可串行化（SERIALIZABLE）
show variables like '%tx_isolation%'；
set SESSION TRANSACTION ISOLATION LEVEL serializable；
1.开启一个事务：
begin；
select * from account； --发现3条记录
2.开启另外一个事务：
begin；
select * from account； --发现3条记录也是3 条记录
insert into account VALUES(4,'deer',500) ；--发现根本就不让插入
3. 回到第一个事务 commit 。
网友评论