了解mysql数据库的锁机制

mysql数据库的锁机制。

分类

• 操作类型：

  1. 读锁(共享锁)：对同一个数据，多个读操作可以同时进行，互不干扰
  2. 写锁(互斥锁)：如果当前写操作没完毕，则无法进行其他的读操作、写操作操作

• 操作范围：

  1. 表锁：一次性对一张表整体加锁。如MyISAM存储引擎使用表锁，开销小，加锁块，无死锁，但锁范围大，容易发生锁冲突，并发效率低
  2. 行锁：一次性对一条数据加锁。如InnoDB存储引擎使用行锁，开销大，加锁慢；容易出现死锁，但锁的范围小，不易发生锁冲突，并发度高(很小概率 发生高并发问题：脏读，幻读，不可重复读)
  3. 页锁

• 示例：

  表锁：

  ​ create table tablelock(id int primary key auto_increment,name varchar(20))engine myisam;

  添加数据： insert into tablelock(name) values(‘a1’);

  insert into tablelock(name) values(‘a2’);

  insert into tablelock(name) values(‘a3’);

  insert into tablelock(name) values(‘a4’);

  insert into tablelock(name) values(‘a5’);

  commit;

  在表锁范围内加锁语法： lock table 表1 read /write , 表2 read/write ,…

  查看加锁的表：

  ​ show open tables;
  
  都没有加锁。

开启两个会话 都登录mysql 使用同一个库，同一张表 测试

加读锁：

​ 在第一个 会话加锁 lock table tablelock read;

测试读锁：对同一个数据，多个读操作可以同时进行，互不干扰

测试查询

会话1(加锁的会话)：

对于加锁的表：

select * from tablelock ;–读，可以

delete from tablelock where id=1;–写操作，不可以

出现了错误:ERROR 1099 (HY000): Table ‘tablelock’ was locked with a READ lock and can’t be updated
对于其他表：

select * from emp;-读，不可以；

出现错误：Table ‘emp’ was not locked with LOCK TABLES；

delete from emp where eid=1; 写，不可以

出现同样的错误；

会话2(非加锁会话)：

对于加锁的表：

select * from tablelock ;–读，可以

delete from tablelock where id=1;–写操作，陷于阻塞等待

对于其他的表：

select * from dept;–读操作，可以

delete from dept where dno=10;-写操作，可以

总结：

如果某个会话对A表加了read锁，则该会话可以对该表进行读操作、不能进行写操作，且该会话不能对其他表进行读操作、写操作；

对于其他会话，可以对加read锁的表读操作，但对于该表的写操作陷于阻塞等待，，对于其他非加锁的表可以进行读操作、写操作

释放锁语法：unlock tables;

释放锁后 其他会话陷于等待的写操作就可以进行了

发现该会话对加锁表陷于阻塞等待了14分钟直到锁被释放才可以完成操作

加写锁：lock table tableName write;

会话1:

加写锁 ：lock table tablelock write;

测试：select * from tablelock ;

delete from tablelock where id=1;

对于其他表：
select * from dept;

结论：当前会话（会话1）可以对加了写锁的表，进行任何操作（增删改查）；但不能（读操作，写操作）操作其他表；

其他会话：

测试：select * from tablelock ;

进行读操作 陷于阻塞等待

delete from tablelock where id=1;

进行写操作 陷于阻塞等待

对于其他表：

select * from dept;

可以正常读操作；

delet from dept where dno=10;

写操作可以正常使用；

结论：非加锁会话对于其他会话加写锁的表进行读写操作均陷于阻塞等待，可以读其他非加写锁的表进行读操作、写操作；

MySQL表级锁的锁模式：

MyISAM在执行查询语句前，会自读给涉及到的所有表进行加读锁，在执行更新操作(DML)前，会自动给涉及到的表加写锁；所以对MyISAM表进行操作，会有以下情况：

a.对于MyISAM表的读操作(加读锁),不会阻塞其他进程(会话)对同一表的读请求，但会阻塞对同一表的写请求，只有当读锁释放后，才会执行其他进程的对该表写操作

b.对于MyISAM表的写操作(写锁)，会阻塞其他进程(会话)对同一表的读和写操作，只要当写锁被释放后，才会执行其他进程对盖表的读写操作。

分析表锁定：

查看那些表加了锁：show open tables; 1代表被加了锁

分析表锁定的严重程度：show status like ‘table%’;

可以看到刚刚进行测试还没释放写锁，有两个进程(会话)被陷于阻塞等待；

​ Table_locks_immediate：即可能获取到的锁数

​ Table_locks_waited：需要等待的表锁数(如果该值越大，说明存中越大的竞争)

当Table_locks_immediate/ Table_locks_waited>5000，采用innodb引擎，否则采用MyISAM引擎