Mysql 鎖總結

標籤：evel   create   oca   ssl   tab   引擎   str   lock   local   

鎖

MyISAM 只支援表鎖

show create table crm_member; ##查看引擎alter table crm_member engine = MyISAM; ##更改表引擎

讀鎖示範

Session 1 讀鎖 其它session可以讀不可寫

lock table crm_member read;

Session 1 鎖等待時間設定

select @@global.lock_wait_timeout; ##查看鎖等待時間設定set @@global.lock_wait_timeout = 20; ##設定鎖等待時間為20秒

Session 2 查詢資料 不影響

MariaDB [member]> select name from crm_member where id = 4;+------+| name |+------+| 222  |+------+1 row in set (0.00 sec)

Session 2 更新資料

MariaDB [member]> MariaDB [member]> update crm_member set name = ‘222‘ where id = 4;## 進入鎖等待

Session 1 查看進程

show processlist;| 97 | root | localhost           | member | Query   |   120 | Waiting for table level lock | update crm_member set name = ‘222‘ where id = 4 |    0.000 |## 等待一個表層級的鎖

Session 1 釋放鎖

MariaDB [member]> unlock tables;Query OK, 0 rows affected (0.00 sec)

Session 2 獲得鎖 立即更新資料

MariaDB [member]> update crm_member set name = ‘222‘ where id = 4;Query OK, 0 rows affected (3 min 9.38 sec)Rows matched: 1  Changed: 0  Warnings: 0

Session 2 鎖等待逾時

MariaDB [member]> update crm_member set name = ‘222‘ where id = 4;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

寫鎖示範

Session 1 添加寫鎖

MariaDB [member]> lock table crm_member write;Query OK, 0 rows affected (0.00 sec)

Session 1 添加寫鎖

MariaDB [member]> lock table crm_member write;Query OK, 0 rows affected (0.00 sec)

Session 2 讀資料

MariaDB [member]> select name from crm_member where id = 4;

Session 1 查看進程

MariaDB [member]> show processlist;| 109 | root | localhost           | member | Query   |     2 | Waiting for table metadata lock | select name from crm_member where id = 4 |    0.000 |## 等待一個中繼資料的鎖

Session 2 讀資料 鎖等待逾時

MariaDB [member]> select name from crm_member where id = 4;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

InnoDB 鎖 既支援表鎖（與MyISAM相同） 也支援 行鎖。排它鎖示範

方式1 Session 1 直接update 其它session不可以讀不可寫 除非快照讀

MariaDB [member]> begin;Query OK, 0 rows affected (0.00 sec)MariaDB [member]> update crm_member set name = ‘aaaaaa‘ where id = 4;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0

方式2 Session 1 for-update 其它session不可以讀不可寫 除非快照讀

MariaDB [member]> begin;Query OK, 0 rows affected (0.00 sec)MariaDB [member]> select name from crm_member where id = 4 for update;

Session 2 方式1 事務方式 開始更新操作相同的記錄

MariaDB [member]> begin;Query OK, 0 rows affected (0.00 sec)MariaDB [member]> update crm_member set name = ‘bbb‘ where id = 4;## 進入鎖等待

Session 2 方式2 普通更新操作

MariaDB [member]> update crm_member set name = ‘bbb‘ where id = 4;

Session 1 查看進程

MariaDB [member]> show processlist;+-----+------+---------------------+--------+---------+-------+----------+-------------------------------------------------+----------+| Id  | User | Host                | db     | Command | Time  | State    | Info                                            | Progress |+-----+------+---------------------+--------+---------+-------+----------+-------------------------------------------------+----------+| 109 | root | localhost           | member | Query   |     2 | updating | update crm_member set name = ‘ccc‘ where id = 4 |    0.000 |+-----+------+---------------------+--------+---------+-------+----------+-------------------------------------------------+----------+## 有一個更新等待

Session 2 鎖等待逾時

MariaDB [member]> update crm_member set name = ‘bbb‘ where id = 4;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Session 1 查看資料 已經更改

MariaDB [member]> select name from crm_member where id = 4;+------+| name |+------+| ccc  |+------+

Session 2 讀取到的資料沒有更改 只能讀取未提交版本的資料

MariaDB [member]> select name from crm_member where id = 4;+------+| name |+------+| bbb  |+------+

Mysql 鎖總結