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下,一个事务访问另一个事务提交前后所得的数据不同,一般可接受