mysql原理和优化使用 > mysql事务
乐观锁,悲观锁, 行锁,表锁 ,死锁

乐观锁

乐观锁不是数据库自带的,需要我们自己去实现。乐观锁是指操作数据库时(更新操作),想法很乐观,认为这次的操作不会导致冲突,在操作数据时,并不进行任何其他的特殊处理(也就是不加锁),而在进行更新后,再去判断是否有冲突了。


通常实现是这样的:在表中的数据进行操作时(更新),先给数据表加一个版本(version)字段,每操作一次,将那条记录的版本号加1。也就是先查询出那条记录,获取出version字段,如果要对那条记录进行操作(更新),则先判断此刻version的值是否与刚刚查询出来时的version的值相等,如果相等,则说明这段期间,没有其他程序对其进行操作,则可以执行更新,将version字段的值加1;如果更新时发现此刻的version值与刚刚获取出来的version的值不相等,则说明这段期间已经有其他程序对其进行操作了,则不进行更新操作。


举例:


下单操作包括3步骤:


1.查询出商品信息


select (status,status,version) from t_goods where id=#{id}


2.根据商品信息生成订单


3.修改商品status为2


update t_goods 


set status=2,version=version+1


where id=#{id} and version=#{version};

悲观锁

与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时,认为此操作会出现数据冲突,所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作,这点跟java中的synchronized很相似,所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的,悲观锁是由数据库自己实现了的,要用的时候,我们直接调用数据库的相关语句就可以了。说到这里,由悲观锁涉及到的另外两个锁概念就出来了,它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现,它俩都属于悲观锁的范畴。

共享锁

又称为读锁,简称S锁,顾名思义,共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改。

排他锁

又称为写锁,简称X锁,顾名思义,排他锁就是不能与其他所并存,如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁,但是获取排他锁的事务是可以对数据就行读取和修改。

    对于共享锁大家可能很好理解,就是多个事务只能读数据不能改数据,对于排他锁大家的理解可能就有些差别,我当初就犯了一个错误,以为排他锁锁住一行数据后,其他事务就不能读取和修改该行数据,其实不是这样的。排他锁指的是一个事务在一行数据加上排他锁后,其他事务不能再在其上加其他的锁。

    mysql InnoDB引擎默认的修改数据语句,update,delete,insert都会自动给涉及到的数据加上排他锁,select语句默认不会加任何锁类型,如果加排他锁可以使用select ...for update语句,加共享锁可以使用select ... lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的,也不能通过for update和lock in share mode锁的方式查询数据,但可以直接通过select ...from...查询数据,因为普通查询没有任何锁机制。

行锁

行锁,由字面意思理解,就是给某一行加上锁,也就是一条记录加上锁。

行级锁都是基于索引的,如果一条SQL语句用不到索引是不会使用行级锁的,会使用表级锁。行级锁的缺点是:由于需要请求大量的锁资源,所以速度慢,内存消耗大。

表锁

表锁,和行锁相对应,给这个表加上锁。

表级锁在MyISAM和innoDB中都有用到,创建锁的开销小,不会出现死锁,由于锁定的是整张表,所以并发度低。当需要频繁对大部分数据做 GROUP BY 操作或者需要频繁扫描整个表时,推荐使用表级锁。

死锁

死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等的进程称为死锁进程.


产生条件:

互斥条件:指进程对所分配到的资源进行排它性使用,即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源,则请求者只能等待,直至占有资源的进程用毕释放

请求和保持条件:指进程已经保持至少一个资源,但又提出了新的资源请求,而该资源已被其它进程占有,此时请求进程阻塞,但又对自己已获得的其它资源保持不放

不剥夺条件:指进程已获得的资源,在未使用完之前,不能被剥夺,只能在使用完时由自己释放

环路等待条件:指在发生死锁时,必然存在一个进程——资源的环形链,即进程集合{P0,P1,P2,···,Pn}中的P0正在等待一个P1占用的资源;P1正在等待P2占用的资源,……,Pn正在等待已被P0占用的资源

遇到死锁可以执行如下的查询语句观察等待的事务:


-- 查看当前的事务

select * from information_schema.innodb_trx;

*************************** 1. row ***************************
trx_id               : 50879
trx_state             : RUNNING
trx_started            :2019-10-05 16:00:53
trx_requested_lock_id     : NULL
trx_wait_started        : NULL
trx_weight            : 2
trx_mysql_thread_id      : 9
trx_query                   : NULL
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        : 1
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

-- 查看当前锁定的事务

select * from information_schema.innodb_locks;

mysql> select * from information_schema.innodb_locks \G;
*************************** 1. row ***************************
    lock_id: 50885:285:3:21
lock_trx_id: 50885
  lock_mode: X
  lock_type: RECORD
 lock_table: `dbname`.`tag`
 lock_index: PRIMARY
 lock_space: 285
  lock_page: 3
   lock_rec: 21
  lock_data: 20
*************************** 2. row ***************************
    lock_id: 50879:285:3:21
lock_trx_id: 50879
  lock_mode: X
  lock_type: RECORD
 lock_table: `dbname`.`tag`
 lock_index: PRIMARY
 lock_space: 285
  lock_page: 3
   lock_rec: 21
  lock_data: 20
2 rows in set, 1 warning (0.00 sec)

ERROR: 
No query specified

-- 查看当前等锁的事务

select * from information_schema.innodb_lock_waits;

mysql> select * from information_schema.innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 50886
requested_lock_id: 50886:285:3:21
 blocking_trx_id: 50879
blocking_lock_id: 50879:285:3:21
1 row in set, 1 warning (0.00 sec)

show engine innodb status

mysql> show engine innodb status \G;
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2019-10-05 16:33:20 0x7f8a840ec700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 12 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 47 srv_active, 0 srv_shutdown, 12781 srv_idle
srv_master_thread log flush and writes: 12824
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 71
OS WAIT ARRAY INFO: signal count 68
RW-shared spins 0, rounds 130, OS waits 60
RW-excl spins 0, rounds 59, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 130.00 RW-shared, 59.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 50888
Purge done for trx's n:o < 50877 undo n:o < 0 state: running but idle
History list length 63
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421708201807504, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 50887, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 19, OS thread handle 140232897980160, query id 887 localhost gw updating
update tag set title = "golang" where id =  20
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 285 page no 3 n bits 96 index PRIMARY of table `laravelschool`.`tag` trx id 50887 lock_mode X locks rec but not gap waiting
Record lock, heap no 21 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 4; hex 00000014; asc     ;;
 1: len 6; hex 00000000c6bb; asc       ;;
 2: len 7; hex 270000018405ec; asc '      ;;
 3: len 2; hex 676f; asc go;;
 4: len 1; hex 31; asc 1;;
 5: SQL NULL;
 6: SQL NULL;
 7: len 4; hex 80000028; asc    (;;


共享锁和排他锁的演示:

数据表结构:

CREATE TABLE `tag` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(198) COLLATE utf8mb4_unicode_ci NOT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 


下面是在共享锁,排他锁中演示修改 id 为20的数据,

初始数据 id 为20  title 为 go , 有 T1 和T2 两个数据库会话

(1)  加上共享锁的情况:

T1终端 为id 为20数据加上共享锁

mysql> begin;
mysql> select id,title from tag where id = 20 lock in share mode;

T2终端 尝试获取 共享锁:

mysql> select id,title from tag where id = 20 lock in share mode;
+----+-------+
| id | title |
+----+-------+
| 20 | go    |
+----+-------+
1 row in set (0.00 sec)

获取成功

T2终端 尝试获取 排他锁:

mysql> select id,title from tag where id = 20 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql client 终端发生阻塞 ,说明共享锁和共享锁不互斥, , 共享锁和排他锁是互斥的


(2) 加上排他锁的情况

T1终端执行下面命令为 id=20数据加上排他锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select id,title from tag where id = 20 for update;
+----+-------+
| id | title |
+----+-------+
| 20 | go    |
+----+-------+
1 row in set (0.00 sec)

T2终端: 尝试获取 共享锁

mysql> select id,title from tag where id = 20 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

T2终端: 尝试获取排他锁:

mysql> select id,title from tag where id = 20 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql client 终端发生阻塞 ,说明排他锁 和排他锁,共享锁都是互斥的,

(3) 直接执行sql 查询不会阻塞

mysql> select id,title from tag where id = 20;
+----+-------+
| id | title |
+----+-------+
| 20 | go    |
+----+-------+
1 row in set (0.00 sec)

(4) insert,update delete都会发生阻塞, 因为mysql 默认都会给这些语句加上排他锁

mysql> update tag  set title="golang" where id = 20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> delete from tag  where id = 20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tag(`id`,`title`) value(20,"cgo");
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction