mysql锁分类和机制

mysql高级

三个日志

乐观锁

悲观苏

按使用方式

共享锁

排它锁

意向锁

间隙锁（Next-Key锁）

按锁级别

Subtopic

按锁粒度

三锁2

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

定义

生活购物打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？这里肯定要用到事务，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

举个栗子

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

读锁（共享锁）

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

写锁（排它锁）

从对数据操作类型（读/写）分

表锁

行锁

从对数据操作的粒度分

锁的分类

概述

偏向MyISAM存储引擎，开销小，加锁块。锁定粒度大，发生锁冲突的概率最高，并发读最低。

特点

建表SQL

我们为mylock表加read锁(读阻塞写例子)session1 session2获得表mylock的READ锁定连接终端当前session可以查询该表记录其他session也可以查询该表的记录...

加读锁

mylockwrite(MyISAM)session1 session2获得表mylock的WRITE锁定待Session1开启写锁后，session2再连接终端当前session对锁定表的查询+更新+插入操作都可以执行：其他session对锁定表的查询被阻塞，需要等待锁被释放：在锁表前，如果session2有数据缓存，锁表以后，在锁住的表不发生改变的情况下session2可以读出缓存数据，一旦数据发生改变，缓存将失效，操作将被阻塞...住

加写锁

案例分析

结论

表锁（偏读）

偏向InonoDB存储引擎，开销大，加锁慢。会出现死锁。锁定粒度最小，发生锁冲突的概率最低，并发读最大。

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。l 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。l一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。l隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。l 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

事务及其ACID属性

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。

更新丢失（Lost Update）

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。一句话：事务A读取到了事务B已修改但尚未提交的的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，【不符合一致性要求】。

脏读（Dirty Reads）

在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据。那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。  一句话：一个事务范围内两个相同的查询却返回了不同数据。

不可重复度（Non-Repeateble Reads）

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。一句话：事务A 读取到了事务B提交的新增数据，【不符合隔离性】。

幻读（Phantom Reads）

并发事务处理带来的问题

脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。常看当前数据库的事务隔离级别：show variables like 'tx_isolation';

事务隔离级别

行锁支持事务

session1

update   `testinnodblock` set b = '3-2' where a = 3;

解决办法：https://blog.csdn.net/weixin_34166472/article/details/88805074

session2阻塞一段时间后，会报错：[SQL]update   `testinnodblock` set b = '3-2' where a = 3;[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

connect_timeout             10delayed_insert_timeout        300have_statement_timeout       YESinnodb_flush_log_at_timeout          1innodb_lock_wait_timeout           20innodb_print_lock_wait_timeout_info OFFinnodb_rollback_on_timeout          OFFinteractive_timeout             28800lock_wait_timeout            5net_read_timeout          150net_write_timeout                   300rpl_semi_sync_master_timeout 10000rpl_stop_slave_timeout 31536000slave_net_timeout               3600thread_pool_idle_timeout         60wait_timeout             28800

show variables like '%timeout%';

session2

行锁定基本演示Session1 Session2更新但是不提交，没有手写commit;Session_2被阻塞，只能等待提交更新解除阻塞，更新正常进行

总结

行锁定演示

Session1 Session2正常情况，各自锁定各自的行，互相不影响，一个2000另一个3000由于在column字段b上面建了索引，如果没有正常使用，会导致行锁变表锁比如没加单引号导致索引失效，行锁变表锁被阻塞，等待。只到Session_1提交后才阻塞解除，完成更新

无索引行锁升级为表锁

共享锁(Share Lock)共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。用法SELECT ... LOCK IN SHARE MODE;在查询语句后面增加 LOCK IN SHARE MODE ，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享...锁

select .. lock in share mode

select * from `testinnodblock` LOCK IN SHARE MODE ;-- COMMIT;

update   `testinnodblock` set b = '3-3' where a = 3;

select * from `testinnodblock`  for UPDATE;

读锁

select也可以加锁

行锁（偏写）

三锁

mysql锁快乐家族

mysql快乐家族