MySql锁

(数据库都是悲观锁,java、redis、zookeeper会有乐观锁)

锁的概念

MySQL中的锁

MyISAM

      MyISAM共享读锁

      MyISAM独占写锁

      MyISAM锁调度

InnoDb

      InnoDb行锁

      InnoDb表锁

 

锁的概念

  1. 锁是计算机协调多个进程或线程并发访问某一资源的机制。
  2. 在数据库中,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。
  3. 锁对数据库而言显得尤其重要,也更加复杂。

MySQL中的锁

  1. MySQL的锁机制比较简单
  2. 其最显著的特点是不同的存储引擎支持不同的锁机制
  3. 比如:
  4. MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);
  5. InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。

 

MySQL中的锁

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

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

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

 

仅从锁的角度来说:

表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如OLAP系统

行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。

很难笼统地说哪种锁更好,只能就具体应用的特点来说哪种锁更合适

MyISAM的表锁

MySQL的表级锁有两种模式:

表共享读锁(Table Read Lock)

表独占写锁(Table Write Lock)

读的时候可以读,读的时候不能写,写的时候不能读,写的时候不能写(读就是共享读,其他的可以读,不能写,写是独立写,其他的不能读也不能写)

 

MyISAM的表锁-共享读锁

1. lock table testmysam READ  启动另外一个session   select * from testmysam 可以查询

2. insert into testmysam value(2);

    update testmysam set id=2  where id=1; 报错

3.在另外一个session中

    insert into testmysam value(2);  等待

4.在同一个session中

   insert into testdemo  value(2,'2','3'); 报错

   select  * from testdemo  ; 报错

5.在另外一个session中

   insert into testdemo  value(2,'2','3'); 成功

6.加索在同一个session 中 select s.* from  testmysam s 报错 

    lock table 表名 as 别名 read;

查看 show status LIKE 'table_locks_waited'  表被锁过几次

 

MyISAM的表锁-独占写锁

 

1.lock table testmysam WRITE

 在同一个session中 

   insert testmysam value(3);

  delete from testmysam where id = 3

  select * from testmysam

2.对不同的表操作(报错)

select s.* from  testmysam s

insert into testdemo  value(2,'2','3');

3.在其他session中 (等待)

select * from testmysam                 

MyISAM的表锁

总结

  1. 对MyISAM表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求
  2. 对MyISAM表的读操作,不会阻塞当前session对表读,当对表进行修改会保存
  3. 一个session使用LOCK TABLE命令给表f加了读锁,这个session可以查询锁定表中的记录,但更新或访问其他表都会提示错误;
  4. 另外一个session可以查询表中的记录,但更新就会出现锁等待
  5. 对 MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作;
  6. 对 MyISAM表的写操作,当前session可以对本表做CRUD,但对其他表进行操作会报错

 

InnoDb行锁

在mysql 的 InnoDB引擎支持行锁

行锁:

  1. 共享锁又称:读锁。当一个事务对某几行上读锁时,允许其他事务对这几行进行读操作,但不允许其进行写操作,也不允许其他事务给这几行上排它锁,但允许上读锁。
  2. 排它锁又称:写锁。当一个事务对某几个上写锁时,不允许其他事务写,但允许读。更不允许其他事务给这几行上任何锁。包括写锁。

 

语法

      上共享锁的写法:lock in share mode

      例如: select  *  from where  条件  lock in share mode

      上排它锁的写法:for update

      例如:select *  from   where 条件 for update

1.

BEGIN

select * from testdemo where id =1 for update

在另外一个session中

update testdemo set c1 = '1' where id = 2 成功

update testdemo set c1 = '1' where id = 1 等待

2.

BEGIN

update testdemo set c1 = '1' where id = 1

在另外一个session中

update testdemo set c1 = '1' where id = 1 等待

3.

BEGIN

update testdemo set c1 = '1' where  c1 = '1'

在另外一个session中

update testdemo set c1 = '2' where  c1 = '2' 等待

 

注意:

1.两个事务不能锁同一个索引。

2.insert ,delete , update在事务中都会自动默认加上排它锁。

3.行锁必须有索引才能实现,否则会自动锁全表,那么就不是行锁了。

4. InnoDb表锁和MyISAM差别不大 。注意:开启一个新事务的时候会解锁表

innodb行级锁

写锁:写不同数据可以,同条数据等待

用commit和rollback

 

表锁不用begin,表锁不用commit和rollback释放

 

开启一个新事物的时候,自动引用unlock()

 

 

 

面试题:系统运行一段时间,数据量已经很大,这时候系统升级,有张表A需要增加个字段,并发量白天晚上都很大,请问怎么修改表结构

面试考点:(修改表结构会导致表锁,数据量大修改数据很长,导致大量用户阻塞,无法访问!)

1. 首先创建一个和你要执行的alter操作的表一样的空的表结构。

2. 执行我们赋予的表结构的修改,然后copy原表中的数据到新表里面。

3. 在原表上创建一个触发器在数据copy的过程中,将原表的更新数据的操作全部更新到新的表中来。

4. copy完成之后,用rename table 新表代替原表,默认删除原表。

Logo

权威|前沿|技术|干货|国内首个API全生命周期开发者社区

更多推荐