mysql怎么实现锁,mysql如何上锁

用 MySQL 实现分布式锁，你听过吗？

以前参加过一个库存系统，由于其业务复杂性，搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。

创新互联公司是一家专注于网站建设、成都做网站和服务器托管的网络公司，有着丰富的建站经验和案例。

比如说，有定时任务域xx.cron，和SystemA域和SystemB域这几个JAVA应用，可能同时修改同一份库存数据。如果不做协调的话，就会有脏数据出现。

对于跨JAVA进程的线程协调，可以借助外部环境，例如DB或者Redis。下文介绍一下如何使用DB来实现分布式锁。

本文设计的分布式锁的交互方式如下：

在使用synchronized关键字的时候，必须指定一个锁对象。

进程内的线程可以基于obj来实现同步。obj在这里可以理解为一个锁对象。如果线程要进入synchronized代码块里，必须先持有obj对象上的锁。这种锁是JAVA里面的内置锁，创建的过程是线程安全的。那么借助DB，如何保证创建锁的过程是线程安全的呢？

可以利用DB中的UNIQUE KEY特性，一旦出现了重复的key，由于UNIQUE KEY的唯一性，会抛出异常的。在JAVA里面，是 SQLIntegrityConstraintViolationException 异常。

transaction_id是事务Id，比如说，可以用

来组装一个transaction_id，表示某仓库某销售模式下的某个条码资源。不同条码，当然就有不同的transaction_id。如果有两个应用，拿着相同的transaction_id来创建锁资源的时候，只能有一个应用创建成功。

在写操作频繁的业务系统中，通常会进行分库，以降低单数据库写入的压力，并提高写操作的吞吐量。如果使用了分库，那么业务数据自然也都分配到各个数据库上了。

在这种水平切分的多数据库上使用DB分布式锁，可以自定义一个DataSouce列表。并暴露一个 getConnection(String transactionId) 方法，按照transactionId找到对应的Connection。

实现代码如下：

首先编写一个initDataSourceList方法，并利用Spring的PostConstruct注解初始化一个DataSource 列表。相关的DB配置从db.properties读取。

DataSource使用阿里的DruidDataSource。

接着最重要的一个实现getConnection(String transactionId)方法。实现原理很简单，获取transactionId的hashcode，并对DataSource的长度取模即可。

连接池列表设计好后，就可以实现往distributed_lock表插入数据了。

接下来利用DB的 select for update 特性来锁住线程。当多个线程根据相同的transactionId并发同时操作 select for update 的时候，只有一个线程能成功，其他线程都block住，直到 select for update 成功的线程使用commit操作后，block住的所有线程的其中一个线程才能开始干活。

我们在上面的DistributedLock类中创建一个lock方法。

当线程执行完任务后，必须手动的执行解锁操作，之前被锁住的线程才能继续干活。在我们上面的实现中，其实就是获取到当时 select for update 成功的线程对应的Connection，并实行commit操作即可。

那么如何获取到呢？我们可以利用ThreadLocal。首先在DistributedLock类中定义

每次调用lock方法的时候，把Connection放置到ThreadLocal里面。我们修改lock方法。

这样子，当获取到Connection后，将其设置到ThreadLocal中，如果lock方法出现异常，则将其从ThreadLocal中移除掉。

有了这几步后，我们可以来实现解锁操作了。我们在DistributedLock添加一个unlock方法。

毕竟是利用DB来实现分布式锁，对DB还是造成一定的压力。当时考虑使用DB做分布式的一个重要原因是，我们的应用是后端应用，平时流量不大的，反而关键的是要保证库存数据的正确性。对于像前端库存系统，比如添加购物车占用库存等操作，最好别使用DB来实现分布式锁了。

如果想锁住多份数据该怎么实现？比如说，某个库存操作，既要修改物理库存，又要修改虚拟库存，想锁住物理库存的同时，又锁住虚拟库存。其实也不是很难，参考lock方法，写一个multiLock方法，提供多个transactionId的入参，for循环处理就可以了。这个后续有时间再补上。

MySQL数据库表锁定的几种方法实现

如果两个程序都向表中写数据显然会造成很大的麻烦，甚至会有意外情况发生。如果表正由一个程序写入，同时进行读取的另一个程序也会产生混乱的结果。

锁定表的方法

防止客户机的请求互相干扰或者服务器与维护程序相互干扰的方法主要有多种。如果你关闭数据库，就可以保证服务器

和myisamchk和isamchk之间没有交互作用。但是停止服务器的运行并不是一个好注意，因为这样做会使得没有故障的数据库和表也不可用。本节主

要讨论的过程，是避免服务器和myisamchk或isamchk之间的交互作用。实现这种功能的方法是对表进行锁定。

服务器由两种表的锁定方法：

1.内部锁定

内部锁定可以避免客户机的请求相互干扰——例如，避免客户机的SELECT查询被另一个客户机的UPDATE查询所干扰。也可以利用内部锁定机制防止服务器在利用myisamchk或isamchk检查或修复表时对表的访问。

语法：锁定表：LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…]

解锁表：UNLOCK TABLES

LOCK TABLES为当前线程锁定表。UNLOCK TABLES释放被当前线程持有的任何锁。当线程发出另外一个LOCK TABLES时，或当服务器的连接被关闭时，当前线程锁定的所有表自动被解锁。

如果一个线程获得在一个表上的一个READ锁，该线程(和所有其他线程)只能从表中读。如果一个线程获得一个表上的一个WRITE锁，那么只有持锁的线程READ或WRITE表，其他线程被阻止。

每个线程等待(没有超时)直到它获得它请求的所有锁。

WRITE锁通常比READ锁有更高的优先级，以确保更改尽快被处理。这意味着，如果一个线程获得READ锁，并且然后另外一个线程请求一个WRITE锁, 随后的READ锁请求将等待直到WRITE线程得到了锁并且释放了它。

显然对于检查，你只需要获得读锁。再者钟情跨下，只能读取表，但不能修改它，因此他也允许其它客户机读取表。对于修复，你必须获得些所以防止任何客户机在你对表进行操作时修改它。

2.外部锁定

服务器还可以使用外部锁定(文件级锁)来防止其它程序在服务器使用表时修改文件。通常，在表的检查操作中服务器

将外部锁定与myisamchk或isamchk作合使用。但是，外部锁定在某些系统中是禁用的，因为他不能可靠的进行工作。对运行myisamchk或

isamchk所选择的过程取决于服务器是否能使用外部锁定。如果不使用，则必修使用内部锁定协议。

如果服务器用--skip-locking选项运行，则外部锁定禁用。该选项在某些系统中是缺省的，如Linux。可以通过运行mysqladmin variables命令确定服务器是否能够使用外部锁定。检查skip_locking变量的值并按以下方法进行：◆如果skip_locking为off，则外部锁定有效您可以继续并运行人和一个实用程序来检查表。服务器和实用程序将合作对表进行访问。但是，运行任何

一个实用程序之前，应该使用mysqladmin flush-tables。为了修复表，应该使用表的修复锁定协议。

◆如果skip_locaking为on，则禁用外部锁定，所以在myisamchk或isamchk检查修复表示服务器并不知道，最好关闭服务器。如果坚

持是服务器保持开启状态，月确保在您使用此表示没有客户机来访问它。

MySQL从入门到精通(九) MySQL锁，各种锁

锁是计算机协调多个进程或线程并发访问某一资源的机制，在数据库中，除传统的计算资源（CPU、RAM、I/O）争用外，数据也是一种供许多用户共享的资源，如何保证数据并发访问的一致性，有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素，从这个角度来说，锁对数据库而言是尤其重要，也更加复杂。MySQL中的锁，按照锁的粒度分为:1、全局锁，就锁定数据库中的所有表。2、表级锁，每次操作锁住整张表。3、行级锁，每次操作锁住对应的行数据。

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将阻塞。其典型的使用场景就是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。但是对数据库加全局锁是有弊端的，如在主库上备份，那么在备份期间都不能执行更新，业务会受影响，第二如果是在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志，会导致主从延迟。

解决办法是在innodb引擎中，备份时加上--single-transaction参数来完成不加锁的一致性数据备份。

添加全局锁: flush tables with read lock; 解锁 unlock tables。

表级锁,每次操作会锁住整张表.锁定粒度大,发送锁冲突的概率最高,并发读最低,应用在myisam、innodb、BOB等存储引擎中。表级锁分为: 表锁、元数据锁(meta data lock, MDL)和意向锁。

表锁又分为: 表共享读锁 read lock、表独占写锁write lock

语法: 1、加锁 lock tables 表名 ... read/write

2、释放锁 unlock tables 或者关闭客户端连接

注意: 读锁不会阻塞其它客户端的读，但是会阻塞其它客户端的写，写锁既会阻塞其它客户端的读，又会阻塞其它客户端的写。大家可以拿一张表来测试看看。

元数据锁,在加锁过程中是系统自动控制的，无需显示使用，在访问一张表的时候会自动加上，MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML和DDL冲突，保证读写的正确性。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作时，加MDL写锁(排他).

查看元数据锁:

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema_metadata_locks;

意向锁，为了避免DML在执行时，加的行锁与表锁的冲突，在innodb中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。意向锁分为,意向共享锁is由语句select ... lock in share mode添加。意向排他锁ix,由insert，update，delete，select。。。for update 添加。

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_lock;

行级锁，每次操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最高，并发读最高，应用在innodb存储引擎中。

innodb的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁，对于行级锁，主要分为以下三类:

1、行锁或者叫record lock记录锁，锁定单个行记录的锁，防止其他事物对次行进行update和delete操作，在RC,RR隔离级别下都支持。

2、间隙锁Gap lock,锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事物在这个间隙进行insert操作，产生幻读，在RR隔离级别下都支持。

3、临键锁Next-key-lock,行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap,在RR隔离级别下支持。

innodb实现了以下两种类型的行锁

1、共享锁 S: 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

2、排他锁 X: 允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

insert 语句 排他锁 自动添加的

update语句 排他锁 自动添加

delete 语句 排他锁 自动添加

select 正常查询语句 不加锁 。。。

select 。。。lock in share mode 共享锁 需要手动在select 之后加lock in share mode

select 。。。for update 排他锁 需要手动在select之后添加for update

默认情况下，innodb在repeatable read事务隔离级别运行，innodb使用next-key锁进行搜索和索引扫描，以防止幻读。

间隙锁唯一目的是防止其它事务插入间隙，间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用的间隙锁。

一文详解-MySQL 事务和锁

当多个用户访问同一份数据时，一个用户在更改数据的过程中，可能有其他用户同时发起更改请求，为保证数据库记录的更新从一个一致性状态变为另外一个一致性状态，使用事务处理是非常必要的，事务具有以下四个特性：

MySQL 提供了多种事务型存储引擎，如 InnoDB 和 BDB 等，而 MyISAM 不支持事务。为了支持事务，InnoDB 存储引擎引入了与事务处理相关的 REDO 日志和 UNDO 日志，同时事务依赖于 MySQL 提供的锁机制

事务执行时需要将执行的事务日志写入日志文件，对应的文件为 REDO 日志。当每条 SQL 进行数据更新操作时，首先将 REDO 日志写进日志缓冲区。当客户端执行 COMMIT 命令提交时，日志缓冲区的内容将被刷新到磁盘，日志缓冲区的刷新方式或者时间间隔可以通过参数 innodb_flush_log_at_trx_commit 控制

REDO 日志对应磁盘上的 ib_logifleN 文件，该文件默认为 5MB，建议设置为 512MB，以便容纳较大的事务。MySQL 崩溃恢复时会重新执行 REDO 日志的记录，恢复最新数据，保证已提交事务的持久性

与 REDO 日志相反，UNDO 日志主要用于事务异常时的数据回滚，具体内容就是记录数据被修改前的信息到 UNDO 缓冲区，然后在合适的时间将内容刷新到磁盘

假如由于系统错误或者 rollback 操作而导致事务回滚，可以根据 undo 日志回滚到没修改前的状态，保证未提交事务的原子性

与 REDO 日志不同的是，磁盘上不存在单独的 UNDO 日志文件，所有的 UNDO 日志均存在表空间对应的 .ibd 数据文件中，即使 MySQL 服务启动了独立表空间

在 MySQL 中，可以使用 BEGIN 开始事务，使用 COMMIT 结束事务，中间可以使用 ROLLBACK 回滚事务。MySQL 通过 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等语句支持本地事务

MySQL 定义了四种隔离级别，指定事务中哪些数据改变其他事务可见、哪些数据该表其他事务不可见。低级别的隔离级别可以支持更高的并发处理，同时占用的系统资源更少

InnoDB 系统级事务隔离级别可以使用以下语句设置：

查看系统级事务隔离级别：

InnoDB 会话级事务隔离级别可以使用以下语句设置：

查看会话级事务隔离级别：

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。读取未提交的数据称为脏读（Dirty Read），即是：首先开启 A 和 B 两个事务，在 B 事务更新但未提交之前，A 事务读取到了更新后的数据，但由于 B 事务回滚，导致 A 事务出现了脏读现象

所有事务只能看见已经提交事务所做的改变，此级别可以解决脏读，但也会导致不可重复读（Nonrepeatable Read）：首先开启 A 和 B 两个事务，A事务读取了 B 事务的数据，在 B 事务更新并提交后，A 事务又读取到了更新后的数据，此时就出现了同一 A 事务中的查询出现了不同的查询结果

MySQL 默认的事务隔离级别，能确保同一事务的多个实例在并发读取数据时看到同样的数据行，理论上会导致一个问题，幻读（Phontom Read）。例如，第一个事务对一个表中的数据做了修改，这种修改会涉及表中的全部数据行，同时第二个事务也修改这个表中的数据，这次的修改是向表中插入一行新数据，此时就会发生操作第一个事务的用户发现表中还有没有修改的数据行

InnoDB 通过多版本并发控制机制（MVCC）解决了该问题：InnoDB 通过为每个数据行增加两个隐含值的方式来实现，这两个隐含值记录了行的创建时间、过期时间以及每一行存储时间发生时的系统版本号，每个查询根据事务的版本号来查询结果

通过强制事务排序，使其不可能相互冲突，从而解决幻读问题。简而言之，就是在每个读的数据行上加上共享锁实现，这个级别会导致大量的超时现象和锁竞争，一般不推荐使用

为了解决数据库并发控制问题，如走到同一时刻客户端对同一张表做更新或者查询操作，需要对并发操作进行控制，因此产生了锁

共享锁的粒度是行或者元组（多个行），一个事务获取了共享锁以后，可以对锁定范围内的数据执行读操作

排他锁的粒度与共享锁相同，一个事务获取排他锁以后，可以对锁定范围内的数据执行写操作

有两个事务 A 和 B，如果事务 A 获取了一个元组的共享锁，事务 B 还可以立即获取这个元组的共享锁，但不能获取这个元组的排他锁，必须等到事务 A 释放共享锁之后。如果事务 A 获取了一个元组的排他锁，事务 B 不能立即获取这个元组的共享锁，也不能立即获取这个元组的排他锁，必须等到 A 释放排他锁之后

意向锁是一种表锁，锁定的粒度是整张表，分为意向共享锁和意向排他锁。意向共享锁表示一个事务有意对数据上共享锁或者排他锁。有意表示事务想执行操作但还没真正执行

锁的粒度主要分为表锁和行锁

表锁的开销最小，同时允许的并发量也是最小。MyISAM 存储引擎使用该锁机制。当要写入数据时，整个表记录被锁，此时其他读/写动作一律等待。一些特定的动作，如 ALTER TABLE 执行时使用的也是表锁

行锁可以支持最大的并发，InnoDB 存储引擎使用该锁机制。如果要支持并发读/写，建议采用 InnoDB 存储引擎

Mysql中锁的类型有哪些呢？

mysql锁分为共享锁和排他锁，也叫做读锁和写锁。

读锁是共享的，可以通过lock in share mode实现，这时候只能读不能写。

写锁是排他的，它会阻塞其他的写锁和读锁。从颗粒度来区分，可以分为表锁和⾏锁两种。

表锁会锁定整张表并且阻塞其他⽤户对该表的所有读写操作，⽐如alter修改表结构的时候会锁表。

⾏锁⼜可以分为乐观锁和悲观锁，悲观锁可以通过for update实现，乐观锁则通过版本号实现。

mysql读数据时怎么加写锁

加锁情况与死锁原因分析

为方便大家复现，完整表结构和数据如下：

CREATE TABLE `t3` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` int(11) DEFAULT NULL,

PRIMARY KEY (`c1`),

UNIQUE KEY `c2` (`c2`)

) ENGINE=InnoDB

insert into t3 values(1,1),(15,15),(20,20);

在 session1 执行 commit 的瞬间，我们会看到 session2、session3 的其中一个报死锁。这个死锁是这样产生的：

1. session1 执行 delete  会在唯一索引 c2 的 c2 = 15 这一记录上加 X lock（也就是在MySQL 内部观测到的：X Lock but not gap）；

2. session2 和 session3 在执行 insert 的时候，由于唯一约束检测发生唯一冲突，会加 S Next-Key Lock，即对 (1,15] 这个区间加锁包括间隙，并且被 seesion1 的 X Lock 阻塞，进入等待；

3. session1 在执行 commit 后，会释放 X Lock，session2 和 session3 都获得 S Next-Key Lock；

4. session2 和 session3 继续执行插入操作，这个时候 INSERT INTENTION LOCK（插入意向锁）出现了，并且由于插入意向锁会被 gap 锁阻塞，所以 session2 和 session3 互相等待，造成死锁。

死锁日志如下：

请点击输入图片描述

INSERT INTENTION LOCK

在之前的死锁分析第四点，如果不分析插入意向锁，也是会造成死锁的，因为插入最终还是要对记录加 X Lock 的，session2 和 session3 还是会互相阻塞互相等待。

但是插入意向锁是客观存在的，我们可以在官方手册中查到，不可忽略：

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

插入意向锁其实是一种特殊的 gap lock，但是它不会阻塞其他锁。假设存在值为 4 和 7 的索引记录，尝试插入值 5 和 6 的两个事务在获取插入行上的排它锁之前使用插入意向锁锁定间隙，即在（4，7）上加 gap lock，但是这两个事务不会互相冲突等待。

当插入一条记录时，会去检查当前插入位置的下一条记录上是否存在锁对象，如果下一条记录上存在锁对象，就需要判断该锁对象是否锁住了 gap。如果 gap 被锁住了，则插入意向锁与之冲突，进入等待状态（插入意向锁之间并不互斥）。总结一下这把锁的属性：

1. 它不会阻塞其他任何锁；

2. 它本身仅会被 gap lock 阻塞。

在学习 MySQL 过程中，一般只有在它被阻塞的时候才能观察到，所以这也是它常常被忽略的原因吧...

GAP LOCK

在此例中，另外一个重要的点就是 gap lock，通常情况下我们说到 gap lock 都只会联想到 REPEATABLE-READ 隔离级别利用其解决幻读。但实际上在 READ-COMMITTED 隔离级别，也会存在 gap lock ，只发生在：唯一约束检查到有唯一冲突的时候，会加 S Next-key Lock，即对记录以及与和上一条记录之间的间隙加共享锁。

通过下面这个例子就能验证：

请点击输入图片描述

这里 session1 插入数据遇到唯一冲突，虽然报错，但是对 (15,20] 加的 S Next-Key Lock 并不会马上释放，所以 session2 被阻塞。另外一种情况就是本文开始的例子，当 session2 插入遇到唯一冲突但是因为被 X Lock 阻塞，并不会立刻报错 “Duplicate key”，但是依然要等待获取 S Next-Key Lock 。

有个困惑很久的疑问：出现唯一冲突需要加 S Next-Key Lock 是事实，但是加锁的意义是什么？还是说是通过 S Next-Key Lock 来实现的唯一约束检查，但是这样意味着在插入没有遇到唯一冲突的时候，这个锁会立刻释放，这不符合二阶段锁原则。这点希望能与大家一起讨论得到好的解释。

如果是在 REPEATABLE-READ，除以上所说的唯一约束冲突外，gap lock 的存在是这样的：

普通索引（非唯一索引）的S/X Lock，都带 gap 属性，会锁住记录以及前1条记录到后1条记录的左闭右开区间，比如有[4,6,8]记录，delete 6，则会锁住[4,8）整个区间。

对于 gap lock，相信 DBA 们的心情是一样一样的，所以我的建议是：

1. 在绝大部分的业务场景下，都可以把 MySQL 的隔离界别设置为 READ-COMMITTED；

2. 在业务方便控制字段值唯一的情况下，尽量减少表中唯一索引的数量。

锁冲突矩阵

前面我们说的 GAP LOCK 其实是锁的属性，另外我们知道 InnoDB 常规锁模式有：S 和 X，即共享锁和排他锁。锁模式和锁属性是可以随意组合的，组合之后的冲突矩阵如下，这对我们分析死锁很有帮助：

请点击输入图片描述