mysql高级---mysql锁机制

mysql锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制。

分类

根据数据操作类型分类

• 读锁（共享锁）

  针对同一份数据，多个读操作可以同时进行而不会互相影响

• 写锁（排他锁）

  当前写操作没有完成前，会阻断其他写锁和读锁

根据数据操作的粒度分类

• 表锁
• 行锁

表锁(偏读)

偏向myisam存储引擎，开销小，加锁快；无死锁；锁定力度大，发生锁冲突的概率最高，并发读最低。
myisam的读写锁是写优先，因此不适合做写为主表的引擎，写锁后，其他线程不能做任何动作，大量更新会使查询很难得到锁，从而永远阻塞。因此偏向读锁。

手动加表锁

Lock table 表名 read/write,表名 read/write,.....;

查看表上加过的锁

Show open tables;

释放表锁

Unlock tables;

为表加读锁

在连接1中添加读锁，此时

• 可以读加锁的表；
• 不可以读其他表，写本表。

 lock table mylock read;

对于连接2

• 可以读被锁表，其他表
• 不可以更新此表（被阻塞），1解锁后，更新语句立刻执行
  

为表加写锁

lock tables mylock write;

对于连接1

• 可以查询本表，更新本表
• 不能查询其他表
  

对于连接2
查询、更新此表被阻塞

总结

读锁会阻塞写，但不阻塞读
写锁会阻塞读和写

分析表的锁定

show status like 'table%';

• Table_locks_immediate 产生表级锁定的次数，表示可以立即获取锁的查询次数。
• Table_locks_waited 表级锁定争用时发生等待的次数，此值高说明存在较严重的表级锁争用情况。

行锁

• 偏向innodb存储引擎，开销大，加锁慢，会出现死锁，锁定力度最小，发生锁冲突的概率最低，发生锁冲突的概率最低，并发度最高
• Innodb和myisam最大的不同有两点，一是支持事务，二是采用了行级锁
• Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定高一些，但在整体并发处理能力方面远远优于myisam的表级锁定。当系统并发量较高时，innodb整体性能有更明显的优势。

行锁支持事务

（复习事务）
脏读
对于事务t1,t2，t1读取了已经被t2更新但还没有提交的字段。之后，如果t2回滚，t1读取的内容就是无效的。

t1修改name 为 a ,此时t2查询的name为a，
t1回滚，t2查询数据与之前不同。

不可重复读
对于t1,t2，t1读取了一个字段，之后t2更新该字段，t1再次读取该字段，值不同

t2先查询，t1修改并提交，t2再查询，数值不一样了。

幻读
对于两个事务t1,t2,t1读取了一个字段，t2新插入一些数据，t1再次读取同一张表，数据就会多出几行。

对同一行进行修改会发生阻塞

对不同行进行修改，不会阻塞

间隙锁

• 使用范围条件而不是相等条件检索数据，并请求共享或排他锁时。innodb会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做间隙。
• innodb也会对这个间隙加锁，这种锁机制就是间隙锁。

危害

锁定一个范围键值后，不存在的值也会被锁定，此时无法插入范围内的任何数据。

#连接1
Update mylock set name = ‘pop’ where id >3 and id<8;
#连接2
Update mylock set name = ’pip’ where id = 4;

此时链接2阻塞

如何锁定一行

select ..... for update;

锁定一行后，其他操作会被阻塞，直到锁定行提交commit;

分析行锁

通过检查innodb_row_lock变量分析系统的行锁的争夺情况

show status like 'innodb_row_lock%';

• Innodb_row_lock_current_waits当前正在等待锁定的数量
• Innodb_row_lock_time从系统启动到现在锁定总时间长度
• Innodb_row_lock_time_avg每次等待所花平均时间
• Innodb_row_lock_time_max从系统启动到现在等待最长的一次所花的时间
• Innodb_row_lock_waits系统启动后到现在总共等待的次数

优化

• 尽可能让所有数据检索都通过索引完成，避免无索引行锁升级为表锁
• 合理设计索引，尽量缩小锁的范围
• 尽可能减少检索条件，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 尽可能低级别的事务隔离

页锁

开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定力度介于表锁行锁之间，并发度一般。