mysql查询更新时的锁表机制分析

欢迎进入Linux社区论坛，与200万技术人员互动交流 >>进入 为了给高并发情况下的mysql进行更好的优化，有必要了解一下mysql查询更新时的锁表机制。 一、概述 MySQL有三种锁的级别：页级、表级、行级。 MyISAM和MEMORY存储引擎采用的是表级锁（table-level loc

欢迎进入Linux社区论坛，与200万技术人员互动交流 >>进入

　　为了给高并发情况下的mysql进行更好的优化，有必要了解一下mysql查询更新时的锁表机制。

　　一、概述

　　MySQL有三种锁的级别：页级、表级、行级。

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

　　MySQL这3种锁的特性可大致归纳如下：

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

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

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

　　二、MyISAM表锁

　　MyISAM存储引擎只支持表锁，是现在用得最多的存储引擎。

　　1、查询表级锁争用情况

　　可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

　　mysql> show status like ‘table%’;

　　+-----------------------+----------+

　　| Variable_name | Value |

　　+-----------------------+----------+

　　| Table_locks_immediate | 76939364 |

　　| Table_locks_waited | 305089 |

　　+-----------------------+----------+

　　2 rows in set (0.00 sec)

　　Table_locks_waited的值比较高，说明存在着较严重的表级锁争用情况。

　　2、MySQL表级锁的锁模式

　　MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁。

　　所以对MyISAM表进行操作，会有以下情况：

　　a、对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。

　　b、对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

　　下面通过例子来进行验证以上观点。数据表gz_phone里有二百多万数据，字段id,phone,ua,day。现在同时用多个客户端同时对该表进行操作分析。

　　a、当我用客户端1进行一个比较长时间的读操作时，分别用客户端2进行读和写操作：

　　client1:

　　mysql>select count(*) from gz_phone group by ua;

　　75508 rows in set (3 min 15.87 sec)

　　client2:

　　select id,phone from gz_phone limit 1000,10;

　　+------+-------+

　　| id | phone |

　　+------+-------+

　　| 1001 | 2222 |

　　| 1002 | 2222 |

　　| 1003 | 2222 |

　　| 1004 | 2222 |

　　| 1005 | 2222 |

　　| 1006 | 2222 |

　　| 1007 | 2222 |

　　| 1008 | 2222 |

　　| 1009 | 2222 |

　　| 1010 | 2222 |

　　+------+-------+

　　10 rows in set (0.01 sec)

　　mysql> update gz_phone set phone=’11111111111′ where id=1001;

　　Query OK, 0 rows affected (2 min 57.88 sec)

　　Rows matched: 1 Changed: 0 Warnings: 0

　　说明当数据表有一个读锁时，其它进程的查询操作可以马上执行，但更新操作需等待读锁释放后才会执行。

[1] [2]