MySQL锁之 MyISAM锁——06

你的名字 2022-05-19 00:07 222阅读 0赞

**介绍MySQL两种引擎下的锁。**

### 1.两种引擎的锁介绍 ###

<table> 
 <tbody> 
  <tr> 
   <td style="width:131px;">MyISAM的锁</td> 
   <td style="width:661px;">MyISAM存储引擎采用的是表级锁，</td> 
  </tr> 
  <tr> 
   <td style="width:131px;">InnoDB的锁</td> 
   <td style="width:661px;">InnoDB既支持表级锁，有支持行锁，默认情况下是行锁。</td> 
  </tr> 
 </tbody> 
</table>

InnoDB还支持事务特性

### 2.具体锁的特性 ###

<table> 
 <tbody> 
  <tr> 
   <td style="width:130px;">表级锁</td> 
   <td style="width:663px;">开销小，加锁快，不会出现死锁，锁定粒度大，发生锁的冲突概率高，开发度最低，并发度低</td> 
  </tr> 
  <tr> 
   <td style="width:130px;">行级锁</td> 
   <td style="width:663px;">开销大，加锁慢，会出现死锁，锁定粒度小，发生锁的冲突概率低，开发度高，并发度一般</td> 
  </tr> 
 </tbody> 
</table>

**总结：**两种锁各有优缺点。表级锁更适合用于查询为主，只有少量按索引更新数据的应用，例如Web应用。而行级锁则更适合有大量按照索引条件并发更新少量不同数据，同时又有并发处理查询的引用。如一些在线事务处理的系统。

### 3.MyISAM表锁介绍 ###

**3.1 锁分类**

MyISAM的表级锁有两种模式：表共享读锁、表独占锁，他们的兼容性如下表格所示

<table> 
 <tbody> 
  <tr> 
   <td style="width:167px;">当前锁模式/请求模式</td> 
   <td style="width:99px;">none</td> 
   <td style="width:129px;">读锁</td> 
   <td style="width:451px;">写锁</td> 
  </tr> 
  <tr> 
   <td style="width:167px;">读锁（当前模式）</td> 
   <td style="width:99px;">兼容</td> 
   <td style="width:129px;">兼容</td> 
   <td style="width:451px;">不兼容</td> 
  </tr> 
  <tr> 
   <td style="width:167px;">写锁（当前模式）</td> 
   <td style="width:99px;">兼容</td> 
   <td style="width:129px;">不兼容</td> 
   <td style="width:451px;">不兼容</td> 
  </tr> 
 </tbody> 
</table>

从上可以看出，MyISAM的表读操作不会阻塞其他用户对同一表的读操作，但是会阻塞对同一表的写操作；而写操作则会阻塞其他用户对同一表的读和写操作。写操作和读操作之间，以及写操作之间是串行的。

**3.2 如何加表锁**

1. 默认情况下MyISAM在执行Select语句(查询)时，自动会加表读锁；而在执行update,delete,insert语句时，会自动给涉及到的表加上写锁，这个过程用户完全不用做任何干预。

2. 显示加表锁。例如在执行sql语句前 lock table user write (给user表加表写锁)，用处一般是为了模拟事务存在，例如有一个订单表，还有一个订单明细表，为了检查这两个表的金额是否相符，那么就需要执行如下

select sum(total) from orders;

select sum(subtotal) from order\_detail;

这两个表如果不给其加上锁，有可能某一个表会添加修改数据，造成数据读取不一致。所以需要在执行这两句前执行

lock tables orders read local,order\_detail read local;  //这里的local表示允许在表尾并发插入

.....

unlock tables;  //解锁

**3.3 MyISAM加锁的注意事项**

1、显示加锁，如上文中如果加了某一种锁，那么只能执行相应的操作，加了读锁，只能读取select,不能update,delete,insert.反之，隐式锁同理。

2、 执行了local table 后，当前会话只能访问当前加锁的表，不能访问其他表，否则报错。

3、对当前的表显示加锁后，如果当前表用了别名，用了多次也会报错,隐式不会有这样的情况。如下：

lock  table actor read;

selelct a.name b.name from actor a, actor b where a.name = b.name and a.name="vison"; //报错

需要修改加锁条件为：lock table actor as a read,actor as b read;

4、MyISAM总是一次获取SQL语句所需要的全部锁，这也是MyISAM不会出现死锁的原因。

**3.4 MyISAM 并发插入**

总体而言，MyISAM表的读和写是串行的，但是在一定条件下也是支持查询和插入并发进行。这个存储引擎又给系统变量concurrent\_insert专门用于控制并发插入的行为。

<table> 
 <tbody> 
  <tr> 
   <td>concurrent_insert的值</td> 
   <td>并发<em><u>插入</u></em>可行性</td> 
  </tr> 
  <tr> 
   <td>concurrent_insert=0</td> 
   <td>不允许并发插入</td> 
  </tr> 
  <tr> 
   <td>concurrent_insert=1</td> 
   <td>如果MyISAM表没有空洞(即表中间没有删除行)，则允许插入在表尾，(通过optimize table 可以去除空洞,自行百度)</td> 
  </tr> 
  <tr> 
   <td>concurrent_insert=2</td> 
   <td>无论有没有空洞，都允许在表尾并发插入数据</td> 
  </tr> 
 </tbody> 
</table>

**3.5 MyISAM锁的调度**

问题：如果一个读请求和一个写请求同时到达，那么优先让哪一个进程的请求获取到锁呢？

答案：写进程获取，就算是读请求先到达进入排队，也会先让写请求插队，这是由于MySQL默认认为写请求比读请求重要，这也是MyISAM表不太适合有大量更新操作和查询操作的原因，大量的更新操作会让查询很难获取到锁。不过可以通过启动参数调节

set low\_priority\_updates  = 1 表示降低更新优先级,当然还有 insert,update，delete 。当前也可以设置max\_write\_lock\_count设置一个合适的值，当表的写锁到达一个值后，就将写锁优先级降低。