mysql的锁机制简介

简介

     锁是计算机协调多个进程或线程并发访问某一资源的机制。不同的数据库，锁机制的原理和实现都大同小异。由于数据库资源是供多业务模块共享的资源，如何保证数据并发访问的一致性、有效性和访问效率，是所有数据库必须解决的问题。锁冲突，是影响数据库并发访问性能的一个重要因素。了解锁机制不仅可以使我们更有效的开发利用数据库资源，也使我们能够更好地维护数据库，从而提高数据库的性能。这篇文章主要是对MySQL的三级锁(表级锁、页级锁和行级锁)及其应用场景进行简要介绍。

 

表级锁: 顾名思义，锁住整个表。加锁后，其它进程无法对该表进行写操作。对应的数据库引擎是MyISAM。

行级锁: 给单独的一行记录加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。支持行级锁的数据库引擎是 INNODB。  

页级锁:对相关联的几行数据加锁。支持页级锁的数据库引擎是 BDB。表级锁速度快，但冲突多；行级锁冲突少，但速度慢。页级锁是一种折中的方案，一次锁定相邻的一组记录。

 

 

实现原理

MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

例如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level-locking）；BDB存储引擎采用的是页级锁（page-level-locking），同时也支持表级锁；InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下是采用行级锁。

和操作系统中的锁一样，数据库中的锁也可以按照能否共享，分为“排它写”锁和”共享读”锁。
对于“排它写”锁，MySQL的表级锁原理如下：
如果在表上没有锁，在它上面放一个写锁；否则，把锁定请求放在写锁定队列中。

对于“共享读"锁，MySQL使用的锁定方法原理如下：如果在表上没有写锁，把一个读锁定放在它上面；否则，把锁请求放在读锁定队列中。进程访问数据库时，如果表上有读锁，则可以读。

 

行级锁和页级锁都可能存在死锁。这是因为，这两种锁是在SQL语句处理期间——而不是在事务启动时——获得的。 

行级锁的优点：
·         当在许多线程中访问不同的行时，会产生较少的冲突。
·         事务回滚时，只有少量数据发生更改。
·         可以长时间锁定单一的行。

行级锁的缺点：
·         因为粒度更小，锁的数量更过，因此比页级和表级锁占用更多的内存。
·         在同一个表中大量使用行级锁时，性能上比页级或表级锁速度慢很多，因为会频繁地加、解锁。
·         如果你在大部分数据上使用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操作，没有任何写操作。

 

如果想要在一个表上做大量的 INSERT 和 SELECT 操作，但是并行的插入却不可能时，可以将记录插入到临时表中，然后定期将临时表中的数据更新到实际的表里。可以用以下命令实现：

复制代码代码如下:

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;

 

 

应用场景

上述三种锁的特性可大致归纳如下：
1） 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
2） 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
3） 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

     三种锁各有各的特点，若仅从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如WEB应用；行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

   

  MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。什么意思呢，就是说对表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞 对同一表的写操作；而对表的写操作，则会阻塞其他用户对同一表的读和写操作。

    表的读和写是串行的，即在进行读操作时不能进行写操作，反之也是一样。但在一定条件下表也支持查询和插入的操作的并发进行，其机制是通过控制一个系统变量（concurrent_insert）来进行的，当其值设置为0时，不允许并发插入；当其值设置为1 时，如果表中没有空洞（即表中没有被删除的行），允许在一个进程读表的同时，另一个进程从表尾插入记录；当其值设置为2时，无论表中有没有空洞，都允许在表尾并发插入记录。

表级锁调度是如何实现的呢，这也是一个很关键的问题。例如，当一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，此时MySQL将会如优先处理进程呢？通过研究表明，写进程将先获得锁（即使读请求先到锁等待队列）。但这也造成一个很大的缺陷，即大量的写操作会造成查询操作很难获得读锁，从而可能造成永远阻塞。所幸我们可以通过一些设置来调节MyISAM的调度行为。我们可通过指定参数low-priority-updates，使MyISAM默认引擎给予读请求以优先的权利，设置其值为1（set low_priority_updates=1),使优先级降低。

 

有如下两种模式的行级锁：

1）共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
    ( Select * from table_name where ......lock in share mode)

2）排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和  排他写锁。(select * from table_name where.....for update)
    为了允许行锁和表锁共存，实现多粒度锁机制；同时还有两种内部使用的意向锁（都是表锁），分别为意向共享锁和意向排他锁。

InnoDB引擎的行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁！

另外：插入，更新性能优化的几个重要参数

代码如下:

bulk_insert_buffer_size
批量插入缓存大小, 这个参数是针对MyISAM存储引擎来说的.适用于在一次性插入100-1000+条记录时, 提高效率.默认值是8M.可以针对数据量的大小,翻倍增加.

concurrent_insert
并发插入, 当表没有空洞(删除过记录), 在某进程获取读锁的情况下,其他进程可以在表尾部进行插入.

值可以设0不允许并发插入, 1当表没有空洞时, 执行并发插入, 2不管是否有空洞都执行并发插入.
默认是1 针对表的删除频率来设置.

delay_key_write
针对MyISAM存储引擎,延迟更新索引.意思是说,update记录时,先将数据up到磁盘,但不up索引,将索引存在内存里,当表关闭时,将内存索引,写到磁盘. 值为 0不开启, 1开启. 默认开启.

delayed_insert_limit,delayed_insert_timeout, delayed_queue_size
延迟插入, 将数据先交给内存队列, 然后慢慢地插入.但是这些配置,不是所有的存储引擎都支持, 目前来看, 常用的InnoDB不支持, MyISAM支持. 根据实际情况调大, 一般默认够用了

 

举个例子: 假设有个表单products ，里面有id跟name二个栏位，id是主键。

例1: (明确指定主键，并且有此笔资料，row lock)

复制代码代码如下:

SELECT * FROM products WHERE id='3' FORUPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

例2: (明确指定主键，若查无此笔资料，无lock)

复制代码代码如下:

SELECT * FROM products WHERE id='-1' FORUPDATE;

例3: (无主键，table lock)

复制代码代码如下:

SELECT * FROM products WHERE name='Mouse'FOR UPDATE;

例4: (主键不明确，table lock)

复制代码代码如下:

SELECT * FROM products WHERE id<>'3'FOR UPDATE;

例5: (主键不明确，table lock)

复制代码代码如下:

SELECT * FROM products WHERE id LIKE '3'FOR UPDATE;

注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。
注2: 要测试锁定的状况，可以利用MySQL的Command Mode ，开二个视窗来做测试。

在MySql 5.0中测试确实是这样的

是表级锁时，不管是否查询到记录，都会锁定表
此外，如果A与B都对表id进行查询但查询不到记录，则A与B在查询上不会进行row锁，但A与B都会获取排它锁，此时A再插入一条记录的话则会因为B已经有锁而处于等待中，此时B再插入一条同样的数据则会抛出Deadlock foundwhen trying to get lock; try restarting transaction然后释放锁，此时A就获得了锁而插入成功。