表锁myisam

行锁innodb

页锁(MySQL特殊的锁定级别)

1.表锁

myisam

MySQL记录系统内部锁资源争用情况的参数

    Table_locks_immediate:产生表级锁定的次数

    Table_locks_waited:出现表级锁定争用而发生等待的次数

myisam 读写相互阻塞的表锁

应当尽量缩短select操作的时间

写锁的优先级高于读锁,即使是读请求先进入等锁队列,写请求后进入等锁队列,也是写请求先获得锁

可以设置读写的优先级

(1)执行命令SET LOW_PRIORITY_UPDATES=1,使该连接读比写的优先级高,该参数在select较多的情况下设置

(2)参数max_write_lock_count设置一个合适的值,当一个表的读锁达到这个值后,MySQL就暂时将写请求的优先级降低,给读进程一定获得锁的机会

 

2.行锁

innodb

共享锁、排它锁

意向锁

间隙锁

为了使行锁和表锁共存,意向锁(意向共享锁、意向排它锁)

锁之间的兼容性

排他锁和其他所有的锁不兼容

如果某一资源上已经有共享锁,可以再给它加共享锁,不能再加排他锁

如果某一资源上已被其他锁占用,可以给它再加意向锁

一个资源上可以加多个意向共享锁,只能加一个意向排他锁

  共享锁(s) 排他锁(x) 意向共享锁(is) 意向排他锁(ix)
共享锁(s) y n y n
排他锁(x) n n n n
意向共享锁(is) y n y y
意向排他锁(ix) n n y y

 

 

 

 

排他锁是存储引擎(innodb)自己加的, 不需要用户干预

对于update、insert、delete,innodb会自动给涉及的行加排他锁

对于select,innodb默认不会加任何锁,可以手动加锁

  select * from user where id = 1 for update(排他锁)

  select * from user where id =1 lock in share mode (共享锁)

InnoDB行锁是通过给索引上的索引项加锁来实现的

(1)innodb的行锁是加在索引项的,因此,只有通过索引检索数据的时候才会使用行锁,不使用索引检索数据使用表锁。

(2)由于innodb行锁是针对索引加锁,不是针对具体的记录,因此,如果在查询的时候,虽然查询地不是相同的行,要是使用了相同的索引,也会产生冲突(同一索引锁定不同的行,冲突)

(3)当表中有多个索引的时候,可以使用不同的索引锁定不同的行(不同的索引锁定不同的行,不冲突)

(4)一个sql在执行过程中,是否使用了索引,由sql的执行计划决定,如果没有使用索引,将使用表锁

间隙锁

使用范围进行查询,innodb不仅会给符合条件的已存在的记录加上共享锁或者排他锁,也会给在范围之内,但是并不存在的记录(间隙)加上间隙锁

select * from user where id > 2

间隙锁能够防止幻读