MySQL行級鎖測驗(yàn)

2024-07-24 12:31:46

字體：大中小

供稿：網(wǎng)友

MySQL innodb存儲引擎使用與Oracle相同的行鎖機(jī)制，對如何查看系統(tǒng)中存在的行鎖情況在下面的實(shí)驗(yàn)中將會看到。下面是測試過程:
session 1：更新記錄

mysql> set autocommit=off;

Query OK, 0 rows affected (0.01 sec)

mysql> update t1 set email='test@test.com' where id=0;

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4 Changed: 4 Warnings: 0

session 2：也更新相同的記錄,出現(xiàn)等待

mysql> set autocommit=off;

Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set email='abc' where id=0;

session 3：查看系統(tǒng)等待事件:

mysql> show status like '%lock%';

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

| Variable_name                 | Value   |

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

| Com_lock_tables               | 0       |

| Com_unlock_tables             | 0       |

| Innodb_row_lock_current_waits | 1       | --這里

| Innodb_row_lock_time          | 0       |

| Innodb_row_lock_time_avg      | 0       |

| Innodb_row_lock_time_max      | 0       |

| Innodb_row_lock_waits         | 1       |

| Key_blocks_not_flushed        | 0       |

| Key_blocks_unused             | 14497   |

| Key_blocks_used               | 0       |

| Qcache_free_blocks            | 1       |

| Qcache_total_blocks           | 1       |

| Table_locks_immediate         | 2070991 |

| Table_locks_waited            | 2       |

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

14 rows in set (0.01 sec)

session 1：提交記錄

mysql> commit;

Query OK, 0 rows affected (0.01 sec)

session 2：update立刻完成

mysql> update t1 set email='abc' where id=0;

Query OK, 4 rows affected (2 min 43.44 sec) --這么長時(shí)間完成更新操作

Rows matched: 4 Changed: 4 Warnings: 0

session 3：再次查看系統(tǒng)等待事件

mysql> show status like '%lock%';

查詢會話session 1,session 2的連接ID

session 1:

mysql> status;

--------------

mysql Ver 12.22 Distrib 4.0.24, for pc-solaris2.10 (i386)

Connection id:          15

session 2:

mysql> status;

---TRANSACTION 0 3852348, ACTIVE 391 sec, OS thread id 12

7 lock struct(s), heap size 1024, undo log entries 4

MySQL thread id 15, query id 2071117 localhost test

（編輯：武林網(wǎng)）

上一篇：一份超詳盡的MySQL高性能優(yōu)化實(shí)戰(zhàn)總結(jié)

下一篇：mysql教學(xué) 鎖