Mysql数据库锁-表级锁分析

1.锁的分类

按操作的类型分：

读锁(共享锁):针对同一份数据，多个读操作可以同时进行而不会相互影响。

写锁(排它锁):当前写操作没有完成，会阻断其它的写锁和读锁。

按对数据操作的粒度分:

表锁(偏读):整个表被锁，偏向于MyISAM引擎，开销小，加锁快，无死锁；

锁定粒度大，发生锁冲突的概率最高，并发度最低。

行锁

表级锁分析

1、建表语句：

CREATE TABLE `userlock` (
  `id` int(8) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `booklock` (
  `id` int(8) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `test`.`userlock` (`name`, `id`) VALUES ('ba', '1');

INSERT INTO `test`.`userlock` (`name`, `id`) VALUES ('ca', '2');

INSERT INTO `test`.`booklock` (`name`, `id`) VALUES ('ba', '1');

INSERT INTO `test`.`booklock` (`name`, `id`) VALUES ('ca', '2');

2、手动增加表锁

LOCK TABLE 表名字1 READ(WRITE),表名字2 READ(WRITE)。。。

3、查看表上加过的锁

SHOW OPEN TABLES

In_use=0说明表没有加锁

4、给 booklock表加读锁，给userlock加写锁

LOCK TABLE booklock READ,userlock WRITE;

5.释放表锁

UNLOCK TABLES;

6.表锁的分析比较

读锁(共享锁)

写锁(排它锁)

给userlock添加写锁

(1)当前session对锁定表进行查询、更新、插入都不会出错

当前session对其它没有锁定的表查询，更新、插入都会出错

(2)其它session对锁定的表的查询(读、写都会被阻塞)被阻塞，需要等待锁被释放

其它session对没有被锁定的表进行增删改查都不会出错。

总结

读锁会阻塞写(其它session不能写被锁定的表)，但是不会阻塞读；而写锁则会把读和写都阻塞(其它session不能读写被锁定的表)。

7.使用SHOW STATUS LIKE 'table%';

通过检查Table_locks_immediate和Table_locks_waited状态量来分析系统上的表锁定:

Table_locks_immediate:产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁一次值加1；

Table_locks_waited:出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值高则说明存在着较严重的表级锁争用的情况。

MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的引擎。因为写锁后，其它线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。