锐客网

  1. 首页 > 睿知 > it技术 > >

mysql怎么实现锁的 mysql怎么锁住数据

锁定IT技术

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 实现分布式锁，你听过吗？以前参加过一个库存系统，由于其业务复杂性，搞了很多个应用来支撑 。这样的话一份库存数据就有可能同时有多个应用来修改库存数据 。
比如说，有定时任务域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 - for update 行锁 表锁for update 的作用是在查询的时候为行加上排它锁 ， 当一个事务的操作未完成时候，其他事务可以读取但是不能写入或更新 。
它的典型使用场景是 高并发并且对于数据的准确性有很高要求，比如金钱、库存等 ， 一般这种操作都是很长一串并且开启事务的，假如现在要对库存进行操作，在刚开始读的时候是1，然后马上另外一个进程将库存更新为0了 ， 但事务还没结束，会一直用1进行后续的逻辑，就会有问题，所以需要用for upate 加锁防止出错 。
行锁的具体实现算法有三种：record lock、gap lock以及next-key lock 。
只在可重复读或以上隔离级别下的特定操作才会取得 gap lock 或 next-key lock，在 Select、Update 和 Delete 时，除了基于唯一索引的查询之外，其它索引查询时都会获取 gap lock 或 next-key lock，即锁住其扫描的范围 。主键索引也属于唯一索引，所以主键索引是不会使用 gap lock 或 next-key lock
for update 仅适用于InnoDB，并且必须开启事务 ， 在begin与commit之间才生效 。
select 语句默认不获取任何锁 ， 所以是可以读被其它事务持有排它锁的数据的！
InnoDB 既实现了行锁，也实现了表锁 。
当有明确指定的主键/索引时候 ， 是行级锁 ， 否则是表级锁
假设表 user，存在有id跟name字段，id是主键，有5条数据 。
明确指定主键 ， 并且有此记录，行级锁
无主键/索引，表级锁
主键/索引不明确，表级锁
明确指定主键/索引，若查无此记录，无锁
参考博文：
轻松掌握MySQL数据库锁机制的相关原理[1] 在一个update和insert操作频繁的表中 少量数据测试的时候运行良好 在实际运营中 因数据量比较大( 万条记录) 会出现死锁现象 用show processlist查看 可以看到一个update语句状态是Locked 一个delete语句状态是Sending data 查看了一下参考手册 把锁定相关的资料整理下来 以便自己记录和追踪该问题的解决情况
MySQL 支持对MyISAM和MEMORY表进行表级锁定 对BDB表进行页级锁定 对InnoDB 表进行行级锁定 在许多情况下 可以根据培训猜测应用程序使用哪类锁定类型最好 但一般很难说出某个给出的锁类型就比另一个好 一切取决于应用程序 应用程序的不同部分可能需要不同的锁类型 为了确定是否想要使用行级锁定的存储引擎 应看看应用程序做什么并且混合使用什么样的选择和更新语句 例如 大多数Web应用程序执行许多选择 而很少进行删除 只对关键字的值进行更新 并且只插入少量具体的表 基本MySQL MyISAM设置已经调节得很好
在MySQL中对于使用表级锁定的存储引擎 表锁定时不会死锁的 这通过总是在一个查询开始时立即请求所有必要的锁定并且总是以同样的顺序锁定表来管理
对WRITE MySQL使用的表锁定方法原理如下
◆ 如果在表上没有锁 在它上面放一个写锁
◆否则 把锁定请求放在写锁定队列中
对READ MySQL使用的锁定方法原理如下
◆如果在表上没有写锁定 把一个读锁定放在它上面
◆否则 把锁请求放在读锁定队列中
当一个锁定被释放时 锁定可被写锁定队列中的线程得到 然后是读锁定队列中的线程
这意味着 如果你在一个表上有许多更新 SELECT语句将等待直到没有更多的更新
如果INSERT 语句不冲突 可以自由为MyISAM 表混合并行的INSERT 和SELECT 语句而不需要锁定
InnoDB 使用行锁定 BDB 使用页锁定 对于这两种存储引擎 都可能存在死锁 这是因为 在SQL语句处理期间 InnoDB 自动获得行锁定 BDB 获得页锁定 而不是在事务启动时获得
行级锁定的优点
· 当在许多线程中访问不同的行时只存在少量锁定冲突
· 回滚时只有少量的更改
· 可以长时间锁定单一的行
行级锁定的缺点
· 比页级或表级锁定占用更多的内存
· 当在表的大部分中使用时 比页级或表级锁定速度慢 因为你必须获取更多的锁
· 如果你在大部分数据上经常进行 GROUP BY 操作或者必须经常扫描整个表 比其它锁定明显慢很多
· 用高级别锁定 通过支持不同的类型锁定 你也可以很容易地调节应用程序 因为其锁成本小于行级锁定
在以下情况下 表锁定优先于页级或行级锁定
· 表的大部分语句用于读取
· 对严格的关键字进行读取和更新 你可以更新或删除可以用单一的读取的关键字来提取的一行
# ; UPDATE tbl_name SET column = value WHERE unique_key_col = key_value ;
# ; DELETE FROM tbl_name WHERE unique_key_col = key_value ;
· SELECT 结合并行的INSERT 语句 并且只有很少的UPDATE或 DELETE 语句
· 在整个表上有许多扫描或 GROUP BY 操作 没有任何写操作
lishixinzhi/Article/program/MySQL/201311/29594
面试你应该知道的 MySQL 的锁背景
数据库的锁是在多线程高并发的情况下用来保证数据稳定性和一致性的一种机制 。MySQL 根据底层存储引擎的不同，锁的支持粒度和实现机制也不同 。MyISAM 只支持表锁 ， InnoDB 支持行锁和表锁 。目前 MySQL 默认的存储引擎是 InnoDB，这里主要介绍 InnoDB 的锁 。
使用 InnoDB 的两大优点：一是支持事务；二是支持行锁 。
在高并发的情况下事务的并发处理会带来几个问题
由于高并发事务带来这几个问题 ， 所以就产生了事务的隔离级别
举个例子
按照上面 1，2 ， 3，4 的顺序执行会发现第 4 步被阻塞了，必须执行完第 5 步后才能插入成功 。这里我们会很奇怪明明锁住的是uid=6 的这一行，为什么不能插入 5 呢？原因就是这里采用了 next-key 的算法，锁住的是（3,10）整个区间 。感兴趣的可以试一下 。
今天给大家分享了一下 MySQL 的 InnoDB 的事务以及锁的一些知识 ， 通过自己的实际上手实践对这块更加熟悉了，希望大家在看的时候也可以动手试试 ， 这样更能体会，理解的更深刻 。
MySQL数据库表锁定的几种方法实现如果两个程序都向表中写数据显然会造成很大的麻烦 ， 甚至会有意外情况发生 。如果表正由一个程序写入，同时进行读取的另一个程序也会产生混乱的结果 。锁定表的方法防止客户机的请求互相干扰或者服务器与维护程序相互干扰的方法主要有多种 。如果你关闭数据库，就可以保证服务器和myisamchk和isamchk之间没有交互作用 。但是停止服务器的运行并不是一个好注意，因为这样做会使得没有故障的数据库和表也不可用 。本节主要讨论的过程，是避免服务器和myisamchk或isamchk之间的交互作用 。实现这种功能的方法是对表进行锁定 。服务器由两种表的锁定方法：1.内部锁定内部锁定可以避免客户机的请求相互干扰——例如，避免客户机的SELECT查询被另一个客户机的UPDATE查询所干扰 。也可以利用内部锁定机制防止服务器在利用myisamchk或isamchk检查或修复表时对表的访问 。语法：锁定表：LOCK TABLEStbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…]解锁表：UNLOCKTABLESLOCKTABLES为当前线程锁定表 。UNLOCK TABLES释放被当前线程持有的任何锁 。当线程发出另外一个LOCKTABLES时，或当服务器的连接被关闭时，当前线程锁定的所有表自动被解锁 。如果一个线程获得在一个表上的一个READ锁，该线程(和所有其他线程)只能从表中读 。如果一个线程获得一个表上的一个WRITE锁 ， 那么只有持锁的线程READ或WRITE表，其他线程被阻止 。每个线程等待(没有超时)直到它获得它请求的所有锁 。WRITE锁通常比READ锁有更高的优先级 ， 以确保更改尽快被处理 。这意味着，如果一个线程获得READ锁，并且然后另外一个线程请求一个WRITE锁,随后的READ锁请求将等待直到WRITE线程得到了锁并且释放了它 。显然对于检查，你只需要获得读锁 。再者钟情跨下，只能读取表，但不能修改它，因此他也允许其它客户机读取表 。对于修复，你必须获得些所以防止任何客户机在你对表进行操作时修改它 。2.外部锁定服务器还可以使用外部锁定(文件级锁)来防止其它程序在服务器使用表时修改文件 。通常，在表的检查操作中服务器将外部锁定与myisamchk或isamchk作合使用 。但是 ， 外部锁定在某些系统中是禁用的，因为他不能可靠的进行工作 。对运行myisamchk或isamchk所选择的过程取决于服务器是否能使用外部锁定 。如果不使用，则必修使用内部锁定协议 。如果服务器用--skip-locking选项运行 ， 则外部锁定禁用 。该选项在某些系统中是缺省的，如Linux 。可以通过运行mysqladminvariables命令确定服务器是否能够使用外部锁定 。检查skip_locking变量的值并按以下方法进行：◆如果skip_locking为off，则外部锁定有效您可以继续并运行人和一个实用程序来检查表 。服务器和实用程序将合作对表进行访问 。但是，运行任何一个实用程序之前，应该使用mysqladmin flush-tables 。为了修复表，应该使用表的修复锁定协议 。◆如果skip_locaking为on，则禁用外部锁定，所以在myisamchk或isamchk检查修复表示服务器并不知道，最好关闭服务器 。如果坚持是服务器保持开启状态，月确保在您使用此表示没有客户机来访问它 。
【mysql怎么实现锁的 mysql怎么锁住数据】关于mysql怎么实现锁的和mysql怎么锁住数据的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站 。

    推荐阅读


    上一篇：微信6ios系统升级，微信版本过低怎么升级至最新版本苹果6

    下一篇：英睿特服务器表现如何？ 英睿特服务器怎么样