MySQL-锁研究

隔离级别研究: http://www.cnblogs.com/JohnABC/p/3521061.html

表级:引擎 MyISAM, 理解为锁住整个表, 锁定期间, 其它进程无法对该表进行写操作, 如果是读锁, 其他进程可以同时读, 如果是写锁, 则其它进程则读也不允许

行级:引擎 INNODB, 单独的一行记录加锁, 其它进程还是可以对同一个表中的其它记录进行操作

页级:引擎 BDB, 表级锁速度快, 但冲突多, 行级冲突少, 但速度慢, 页级折衷, 一次锁定相邻的一组记录

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

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

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

 

表锁

MyISAM在执行查询语句(SELECT)前, 会自动给涉及的所有表加读锁, 在执行更新操作(UPDATE、DELETE、INSERT等)前, 会自动给涉及的表加写锁, 这个过程并不需要用户干预, 因此, 用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁

表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)

对WRITE, MySQL使用的表锁定方法原理如下:如果在表上没有锁, 在它上面放一个写锁, 否则, 把锁定请求放在写锁定队列中

对READ, MySQL使用的表锁方法原理如下:如果在表上没有写锁定, 把一个读锁定放在它上面否则, 把锁请求放在读锁定队列中

LOCK TABLE `TNAME` READ|WRITE

 

行锁

在SQL语句处理期间, InnoDB自动获得行锁定和BDB获得页锁定, 而不是在事务启动时获得

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

行锁是建立在索引上的!!所以如果给一个unique列加锁, 先在unique对应的记录上加索引, 然后找到对应的主键索引记录, 加上锁

行级锁定的优点:

  当在许多线程中访问不同的行时只存在少量锁定冲突

  回滚时只有少量的更改

  可以长时间锁定单一的行

行级锁定的缺点:

  比页级或表级锁定占用更多的内存

  当在表的大部分中使用时, 比页级或表级锁定速度慢, 因为你必须获取更多的锁(比如UPDATE多条数据)

  如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表, 比其它锁定明显慢很多

  用高级别锁定, 通过支持不同的类型锁定, 你也可以很容易地调节应用程序, 因为其锁成本小于行级锁定

SELECT ... LOCK IN SHARE MODE 加S锁
SELECT ... FOR UPDATE 加X锁

快照读:简单的select操作, 属于快照读, 不加锁 (当然, 也有例外)

select * from table where ?;

当前读:特殊的读操作, 插入/更新/删除操作, 属于当前读, 需要加锁

select * from table where ? lock in share mode;

select * from table where ? for update;

insert into table values (…);

update table set ? where ?;

delete from table where ?;

可以看看加锁处理分析http://hedengcheng.com/?p=771或者百度网盘http://pan.baidu.com/s/1mgN00Og

在以下情况下, 表锁定优先于页级或行级锁定:

  表的大部分语句用于读取

  对严格的关键字进行读取和更新, 你可以更新或删除可以用单一的读取的关键字来提取的一行:

    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;

MyISAM

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

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

  执行LOCK TABLES后, 只能访问显式加锁的这些表, 不能访问未加锁的表

InnoDB

  由于InnoDB预设是Row-Level Lock, 所以只有「明确」的指定主键, MySQL才会执行Row lock (只锁住被选取的资料例), 否则MySQL将会执行Table Lock (将整个资料表单给锁住)

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

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

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

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

SELECT * FROM products WHERE id='-1' FOR UPDATE;

例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 found when trying to get lock; try restarting transaction然后释放锁, 此时A就获得了锁而插入成功

锁测试

  MyISAM 查询表级锁争用情况(Table_locks_waited的值比较高, 说明存在着较严重的表级锁争用情况)

mysql> show status liketable%’;
+-----------------------+----------+
| Variable_name | Value |
+-----------------------+----------+
| Table_locks_immediate | 76939364 |
| Table_locks_waited | 305089 |
+-----------------------+----------+
2 rows in set (0.00 sec)

  MyISAM长时间读, 测试写操作等待(客户端1进行一个比较长时间的读操作时, 分别用客户端2进行读和写操作)

  client 1

mysql>select count(*) from gz_phone group by ua;
75508 rows in set (3 min 15.87 sec) 

  client 2

    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=11111111111where id=1001;
    Query OK, 0 rows affected (2 min 57.88 sec)
    Rows matched: 1 Changed: 0 Warnings: 0

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

  MyISAM长时间写, 测试读操作等待(客户端1进行一个比较长时间的写操作时, 分别用客户端2, 3进行读和写操作)

  client 1

mysql> update gz_phone set phone=11111111111′;
Query OK, 1671823 rows affected (3 min 4.03 sec)
Rows matched: 2212070 Changed: 1671823 Warnings: 0 

  client 2

mysql> select id,phone,ua,day from gz_phone limit 10;
+----+-------+-------------------+------------+
| id | phone | ua | day |
+----+-------+-------------------+------------+
| 1 | 2222 | SonyEricssonK310c | 2007-12-19 |
| 2 | 2222 | SonyEricssonK750c | 2007-12-19 |
| 3 | 2222 | MAUI WAP Browser | 2007-12-19 |
| 4 | 2222 | Nokia3108 | 2007-12-19 |
| 5 | 2222 | LENOVO-I750 | 2007-12-19 |
| 6 | 2222 | BIRD_D636 | 2007-12-19 |
| 7 | 2222 | SonyEricssonS500c | 2007-12-19 |
| 8 | 2222 | SAMSUNG-SGH-E258 | 2007-12-19 |
| 9 | 2222 | NokiaN73-1 | 2007-12-19 |
| 10 | 2222 | Nokia2610 | 2007-12-19 |
+----+-------+-------------------+------------+
10 rows in set (2 min 58.56 sec) 

  client 3

mysql> update gz_phone set phone=55555where id=1;
Query OK, 1 row affected (3 min 50.16 sec)
Rows matched: 1 Changed: 1 Warnings: 0 

  说明当数据表有一个写锁时, 其它进程的读写操作都需等待读锁释放后才会执行

  InnoDB FOR UPDATE 测试(client1 执行select for update后不提交, client 2执行读写操作)

CREATE TABLE ·user`(
    id int primary key auto_increment,
    num int
) TYPE=INNODB;

INSERT INTO `user` values(1, 1);
INSERT INTO `user` values(2, 2);

  client 1

SET autocommit = 0;
SELECT * FROM `user` WHERE `id`=1 FOR UPDATE;

  client 2

mysql>SET autocommit = 0;
mysql>SELECT * FROM `user` WHERE `id`=1;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
+----+-----+
1 row in set (0.00 sec)
mysql>UDPATE `user` SET `num`=2 WHERE `id`=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>SELECT * FROM `user` WHERE `id`=1 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>UPDATE `user` SET num=3 WHERE `id`=2;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

  有此看出使用FOR UPDATE锁定一条记录时, 此记录另一个事务只可读, 不可修改, 其他记录可以任意进行读取和修改

 

posted on 2015-03-30 11:21  John_ABC  阅读(994)  评论(0编辑  收藏  举报

导航