mysql 数据库的锁机制

1，什么是所机制

      数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问访问变得有序所设计的一种规则。

2.为什么要使用锁？

     数据库是一个多用户使用的共享资源,比如一个用户表t_user,两个浏览器前面的人登录了同个一个账号，把电话号码改了。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性(脏读，不可重复读，幻读等)，可能产生死锁。为了解决这个问题，加锁是一个非常重要的技术，对实现数据库并发控制是一个好的方案。简单说，当一个执行sql语句的事务想要操作表记录之前，先向数据库发出请求，对你访问的记录集加锁，在这个事务释放这个锁之前，其他事务不能对这些数据进行更新操作。

3.有哪些锁？

锁包括行级锁、表级锁、悲观锁、乐观锁

4.各种锁的简介

   （1）行级锁（row-level）：

　　　　行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，

　　　　能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。

　　　　虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的

     　　 消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

   （2）表级锁（table level）：

　　　　和行级锁定相反，表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释

       　　放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。

　　　　当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。

　（3）悲观锁（Pessimistic Lock）：

　　　　它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守悲观态度，事务每次去操作数据的时候都假设有其他事务会修改需要访

　　　　问的数据，所以在访问之前都要求上锁，行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，因此，在整个数据处理过程中，将数据处于锁定状态。

　　　　

   （4）乐观锁（Optimistic Lock）：

　　　　和悲欢锁相反，事务每次去操作数据之前，都假设其他事务不会修改这些需要访问的数据 ，所以 在访问之前不要求上锁，只是在进行更新修改操作的时候判断一下在访

　　　　问的期间有没有其他人修改数据 了。它适用于多读的应用类型，冲突真的发生比较少的时候就比较好，这样省去了开销的开销，可以提高吞吐量。

　　　　但如果是真的经常要发生冲突的，那每次还要去判断进行retry,反倒降低的性能，这个时候悲欢锁比较好。数据库如果提供类似于write_condition机制的其实都是提供的乐

　　　　观锁。

         举例表明乐观锁：             

　　　　 1.利润表t_profit中有一个 version字段，当前值为1；而总资产余额字段（balance）为$10000

      　　 2.操作员A读出version=1,从总资产减除2000，10000-2000=8000.

      　　 3.A还没操作结束，此时操作员B也读出version=1,总资产减除5000，10000-5000=5000.

      　　 4.A操作完成，把version加1，修改为2，把总资产减2000后提交更新数据库，更新成功

     　　  5.B操作了，也加version加1，修改为2，把总资产减5000后提交更新数据库，此时发现version已经为2了，如B修改后加1的version一样，不满足乐观锁策略:"提交的版本

　　　　必有大于记录当前的版本才能执行"。因此B的操作请求被驳回，这样就避免了B就version=1的旧数据修改的结果覆盖了A操作的结果的可能。如没有乐观锁，那A减去

　　　　2000后剩余8000，但B操作的时候是用10000-5000剩余5000的，如果B的提交成功，总资产余额就是5000，但实际情况应该是8000-5000=3000的。出现总资产表记录

　　　　和实际支出不一致。

5，mysql数据库的锁机制

     　　（1） Mysql中不同的存储引擎支持不同的锁机制。

　　　　比如：

　　　　　　MyISAM和MEMORY存储引擎采用的表级锁，

　　　　　　BDB采用的是页面锁，也支持表级锁，

　　　　　　InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下采用行级锁。

　　　　（2）mysql中各种锁的特性

　　　　　　

　　　　　　表级锁：开销小，加锁块；不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。

　　　　　　行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发性也最高。

　　　　　　页面锁：开销和加锁界于表锁和行锁之间，会出现死锁；锁定粒度界与表锁和行锁之间，并发一般。

　　　　（3）基于innoDB引擎下的mysql锁机制

   　　　　    

　　　　　　1）.支持事务

 　　　　　   2）.采用行级锁

　　　　（4）innoDB行级锁模式 及 加锁方式

　　　　　　

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

　　　　　　共享锁（S）：允许一个事务去读一行，阻止其他事务获取相同数据集的排他锁。

　　　　　　排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

　　　　　　

　　　　　　先两种意向表锁：

　　　　　　意向同享锁(IS)

　　　　　　意向排他锁(IX)

　　　　　　下面看看这四种锁的兼容问题

                      

　　　　　　

　　　　　　如果一个事务请求的锁模式与当前的锁模式兼容，innodb就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。意向锁是Innodb自动加的，

　　　　　　不需要用户干预。对于UPDATE、DELETE、INSERT语句，Innodb会自动给涉及的数据集加排他锁（X）；对于普通SELECT语句，Innodb不会加任何锁。

　　　　　　

　　　　显示添加锁

　　　　　　共享锁（S） ： SELECT * FROM table_name WHERE .... LOCK IN SHARE MODE

　　　　　　排他锁（X）：  SELECT * FROM table_name WHERE .... FOR UPDATE.

　　　　　　使用select ... in share mode获取共享锁，主要用在需要数据依存关系时，确认某行记录是否存在，并确保没有人对这个记录进行update或者delete。

 　　　　（4）InnoDB行级锁实现方式

　　　　　　*InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过再数据块中，对相应数据行加锁来实现的。InnoDB这种行锁实现特点

　　　　　　意味着：只有通过索引条件检索数据，innoDB才使用行级锁，否则InnoDB将使用表锁，在实际开发中应当注意。

　　　　　　

　　　　　　因为没有创建索引，当给第一个会话添加索引时候，其实添加的是表索引，而非行索引，因为第二会话在查询其他信息时候，一直处于等待状态，最后超时，

　　　　　　直到第一个会话事务提交后，方可查询。（需要先设置 set autocommit=0）

　　　　　　

　　　　　　案列二：修改上面t1表中数据，数据如下

　　　　　　

　　　　　　给id1添加索引ALTER TABLE t1 ADD INDEX id1(id1);

　　　　　　

　　　　　　有此可以看出此时，mysql使用的是行索引。

　　　　　　但是还有一个需要我们注意的问题，先请看如下情况：

　　　　　　

　　　　　　很明显两个会话查询的不是同一行记录，为什么会话2仍然需要等待会话1提交之后才能查询呢？

　　　　　　答案：还是因为Mysql行锁是针对索引加的锁，不是针对记录加的锁，索引虽然访问不同的记录，但是他们的索引相同，是会出现冲突的，在设计数据库时候需要

　　　　　　　　　注意这一点。上面只有将字段id2，也添加上索引才能解决冲突问题。这也是mysql效率低的一个原因。

 

 

转载于:https://www.cnblogs.com/bb1008/p/7705059.html