mysql postgresql 锁_MySQL 锁机制之一：初识行锁、表锁

今天看了 MySQL InnoDB 锁相关文档，内容挺多，锁的原理和机制比起 PostgreSQL 也稍显复杂，今天学习到行锁、表锁相关内容，了解到了一个奇特的特性，即“InnoDB 表根据无索引字段更新时，即使更新不同的数据行也会发生阻塞”。

InnoDB 行锁是通过索引上的索引项来实现的，也就是说InnoDB只有通过索引条件检索数据时才使用行级锁，否则将使用表级锁，举例如下：

一、不使用索引的场景

创建测试表

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16francs@localhost:francs>create table test_lock1(id int4,name varchar(32));

Query OK, 0 rows affected (0.17 sec)

francs@localhost:francs>insert into test_lock1(id,name) values (1,'a'),(2,'b'),(3,'c');

Query OK, 3 rows affected (0.04 sec)

Records: 3 Duplicates: 0 Warnings: 0

francs@localhost:francs>select * from test_lock1;

+------+------+

| id | name |

+------+------+

| 1 | a|

| 2 | b|

| 3 | c|

+------+------+

3 rows in set (0.00 sec)

开启会话一

1

2

3

4

5

6

7

8

9

10francs@localhost:francs>begin;

Query OK, 0 rows affected (0.00 sec)

francs@localhost:francs>select * from test_lock1 where id=1 for update;

+------+------+

| id | name |

+------+------+

| 1 | a|

+------+------+

1 row in set (0.00 sec)

开启会话二

1

2

3

4

5francs@localhost:francs>begin;

Query OK, 0 rows affected (0.00 sec)

francs@localhost:francs>select * from test_lock1 where id=2 for update;

此SQL处于等待状态

备注：更新表上不同的数据行也会产生等待，这很令人费解，PostgreSQL、Oracle 都不会出现这种情况。

开启另一会话查询 INNODB_TRX

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52root@localhost:information_schema>select * from INNODB_TRXG

* 1. row *

trx_id: 511689

trx_state: LOCK WAIT

trx_started: 2016-08-28 16:09:14

trx_requested_lock_id: 511689:42574:3:2

trx_wait_started: 2016-08-28 16:10:27

trx_weight: 2

trx_mysql_thread_id: 57

trx_query: select * from test_lock1 where id=2 for update

trx_operation_state: starting index read

trx_tables_in_use: 1

trx_tables_locked: 1

trx_lock_structs: 2

trx_lock_memory_bytes: 1096

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: 511688

trx_state: RUNNING

trx_started: 2016-08-28 16:07:15

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 2

trx_mysql_thread_id: 53

trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 1

trx_lock_structs: 2

trx_lock_memory_bytes: 1096

trx_rows_locked: 4

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 rows in set (0.01 sec)

备注：“trx_state”字段有 RUNNING, LOCK WAIT, ROLLING BACK or COMMITTING值， RUNNING表示运行中，LOCK WAIT 表示等待；从上面看出，事务511689处于等待状态。

二、使用索引场景

给表 test_lock1 加上主键

1

2

3francs@localhost:francs>alter table test_lock1 add primary key (id);

Query OK, 0 rows affected (0.12 sec)

Records: 0 Duplicates: 0 Warnings: 0

开启会话一

1

2

3

4

5

6

7

8

9

10francs@localhost:francs>begin;

Query OK, 0 rows affected (0.00 sec)

francs@localhost:francs>select * from test_lock1 where id=1 for update;

+------+------+

| id | name |

+------+------+

| 1 | a|

+------+------+

1 row in set (0.00 sec)

开启会话二

1

2

3

4

5

6

7

8

9

10francs@localhost:francs>begin;

Query OK, 0 rows affected (0.00 sec)

francs@localhost:francs>select * from test_lock1 where id=2 for update;

+----+------+

| id | name |

+----+------+

| 2 | b|

+----+------+

1 row in set (0.00 sec)

备注：此事务没有等待，执行成功。

开启另一会话查询 INNODB_TRX

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52root@localhost:information_schema>select * from INNODB_TRXG

* 1. row *

trx_id: 511707

trx_state: RUNNING

trx_started: 2016-08-28 16:18:07

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 2

trx_mysql_thread_id: 57

trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 1

trx_lock_structs: 2

trx_lock_memory_bytes: 1096

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

* 2. row *

trx_id: 511706

trx_state: RUNNING

trx_started: 2016-08-28 16:17:43

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 2

trx_mysql_thread_id: 53

trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 1

trx_lock_structs: 2

trx_lock_memory_bytes: 1096

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

2 rows in set (0.01 sec)

备注：无处于LOCK WAIT 状态的事务。

三、总结

MySQL 通过索引项实现数据行加锁，具体原理机制现在还不是很清楚，后续学习补充。

四、参考