MYSQL锁

1. 锁的类型

    共享锁（S Lock）：允许事务读一行数据

    排他锁（X Lcok）：允许事务删除或者更新一行数据

    兼容性：XX、XS、SX冲突；SS兼容

    InnoDB意向锁：表级别的锁，是为了在一个事务中揭示下一行将被请求的锁的类型

    意向共享锁（IS Lock）：事务想要获得一个表中某几行的共享锁

    意向排它锁（IX Lock）：事务想要获得一个表中某几行的排它锁

 

2. 查看请求锁的信息（为了显示一个锁信息，开启两个会话，设置非自动提交，同时更新同一行数据）

mysql> use db2;
Database changed
mysql> set session autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set name='mike' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
//在另一个会话上也执行ID为1更新name的操作，会出去等待状态
mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
...//假装省略号//...
------------
TRANSACTIONS
------------
Trx id counter 6959    
Purge done for trx's n:o < 6957 undo n:o < 0 state: running but idle
History list length 24
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 6958, ACTIVE 75 sec starting index read        //显示锁的事务ID
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 113, OS thread handle 29820, query id 4297 localhost ::1 root updating
update user set name='captain' where id=1   
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 360 page no 4 n bits 80 index GEN_CLUST_INDEX of table `db2`.`user` trx id 6958 loc
k_mode X waiting
Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000000556; asc      V;;
 1: len 6; hex 000000001b2d; asc      -;;
 2: len 7; hex 01000001420eb5; asc     B  ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 6d696b65; asc mike;;

...//假装省略号//...
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 6958    //事务ID
                 trx_state: LOCK WAIT    //当前事务状态
               trx_started: 2019-07-16 10:48:23
     trx_requested_lock_id: 6958:360:4:10
          trx_wait_started: 2019-07-16 10:49:35
                trx_weight: 2    //事务的权重，反映了一个事务修改和锁住的行数
       trx_mysql_thread_id: 113    //mysql中的线程ID
                 trx_query: update user set name='captain' where id=1
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 2
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 6957
                 trx_state: RUNNING
               trx_started: 2019-07-16 10:48:12
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 112
                 trx_query: select * from information_schema.innodb_trx
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 6
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

mysql>

3. select ... for update $ select ... lock in share mode

    select ..for update 对读取的行记录加一个X锁，其他事务想在这些行上加任何锁都会被阻塞

    select ...lock in share mode 对读取的行记录加一个S锁，其他事务可以向被锁定的记录加S锁，但对于X锁则会被阻塞

 

4. 自增长和锁

    （1） 自增长插入分类

         insert-like：指所有插入语句，如insert、replace、insert...select、replace...select、load data等

        simple insert：指插入前就确定插入行数的语句，如insert、replace等

        bulk insert：指插入前不能确定插入行数的语句、如insert...select、replace.. select、load data

        mixed-mode insert：指插入中有一部分的值是自增长的，有一部分是确定的

    （2）参数innodb_autoinc_loc三个可选值

        0： 通过表锁的auto_inc locking方式，原来的自增长方式

        1： 默认值，对于simple insert，该值会用互斥量去对内存中的计数进行累加的操作，对于bulk inserts还是使用传统的auto_inc locking方式

        2： 对于所有insert-like自增长值得产生都是通过互斥量，而不是auto-inc locking的方式

        注：innodb存储引擎下，自增长的列必须是索引，并且是索引的第一个列值

 

5. 外键和锁

    innodb引擎会为外键列自动添加一个索引

 

6. 锁问题

    （1）丢失更新

        session1：A-->C-->D waiting -->代表更新

        session2：B-->C-->E

        由于session1为完成情况下，session2更新最后完成，导致session1更新结果丢失，最后的结果变为E。

        解决方法是：在两个会话要更新的记录上加一个排它锁

    （2）脏读

        概念：指的是在不同的事务下，可以读到另外事务未提交的数据

    （3）不可重复读

        在两个事务在事务隔离级别为read commited下，一个事务访问另一个事务提交前后所得的数据不同，一般可接受