mysql学習記録(19)--Innodbテーブルロック
14266 ワード
一、理論:
1.トランザクションとそのacidプロパティ:
a.原子性:トランザクションによるデータの変更はすべて実行するか、すべて実行しないか.
b.コンシステンシ:トランザクションの開始と完了時に、データはコンシステンシ状態を保証する必要があります.
c.隔離性:取引の過程で対外的に「見えない」.
d.永続性:トランザクションが完了した後、データの変更は永続的です.
2.同時処理の問題:
a.更新喪失
b.ダーティリード:複数のトランザクションが同時に同じレコードを修正する
c.繰り返し不可:一度データを読んでから同じレコードを読むと、このレコードが変わる場合があります
d.幻読:1つのトランザクションが同じ条件でクエリーする前に取得したデータは、元の実行結果とは異なる
3.取引独立性レベル:
a.データを読み込む前にロックする
b.ある時点で「スナップショット」を生成し、データベースの複数のバージョンを提供することができる.
4.iso/ansi sql 92定義の4つのトランザクション独立性レベル:
a.未読
b.コミットされたリード
c.繰り返し読み可能
d.直列化可能
5.innodbのロックモード及びロック方法
a.共有ロック:1つのトランザクションが1行を読むことを許可し、他のトランザクションが同じデータセットの排他的ピンを取得することを阻止する
b.排他ロック:排他ロックを取得したトランザクションがデータを更新することを許可し、他のトランザクションが同じデータセットの共有リードロックと排他ライトロックを取得することを阻止する
6.トランザクションは次のように共有ロックまたは排他ロックを追加できます.
a.共有ロック:select*from testTable where...lock in share mode
b.排他ロック:select*from testTable where...for update
7.innodb行ロックの実現方式:
a.record lock:インデックス・アイテムにロックをかける
b.gap lock:インデックス項目間のギャップをロックする
c.next-key lock:記録およびギャップにロック、
8.ロック競合を分析する場合、sql実行計画を確認して、インデックスが本当に使用されているかどうかを確認する必要がある場合があります.
9.表ロック関連:
a.トランザクションは大部分または全部のデータを更新する必要がある
b.トランザクションは複数のテーブルに関連してデッドロックを引き起こす可能性があり、大量のトランザクションがロールバックする
c.innodbテーブルロックは「システムレベル」ロック
10.その他:
a.なるべく低い隔離レベルを使用する
b.インデックスを丹念に設計し、できるだけインデックスを使用してデータにアクセスし、ロックをより正確にし、ロック衝突の機会を減らす
c.合理的なトランザクションサイズを選択し、小さなトランザクションがロック競合する確率がより小さい
d.レコードセットに明示的にロックをかける場合は、十分なレベルのロックを一度に要求することが望ましい.デッドロックを避けることができます
e.異なるプログラムが一組のテーブルにアクセスする場合、できるだけ同じ順序で各テーブルにアクセスすることを約束する.1つのテーブルに対して、テーブル内のローにできるだけ一定の順序でアクセスします.
f.できるだけ等しい条件でデータにアクセスすることでnext-keyロックが同時挿入に与える影響を回避できる
g.必要がない限り、クエリー時に明示的にロックしないで、実際に必要なロックレベルを超えたロックを申請しないでください.
h.特定のトランザクションについては、テーブルロックを使用して処理速度を向上させたり、デッドロックの発生確率を低減したりすることができます.
二、実践:
1.トランザクションとそのacidプロパティ:
a.原子性:トランザクションによるデータの変更はすべて実行するか、すべて実行しないか.
b.コンシステンシ:トランザクションの開始と完了時に、データはコンシステンシ状態を保証する必要があります.
c.隔離性:取引の過程で対外的に「見えない」.
d.永続性:トランザクションが完了した後、データの変更は永続的です.
2.同時処理の問題:
a.更新喪失
b.ダーティリード:複数のトランザクションが同時に同じレコードを修正する
c.繰り返し不可:一度データを読んでから同じレコードを読むと、このレコードが変わる場合があります
d.幻読:1つのトランザクションが同じ条件でクエリーする前に取得したデータは、元の実行結果とは異なる
3.取引独立性レベル:
a.データを読み込む前にロックする
b.ある時点で「スナップショット」を生成し、データベースの複数のバージョンを提供することができる.
4.iso/ansi sql 92定義の4つのトランザクション独立性レベル:
a.未読
b.コミットされたリード
c.繰り返し読み可能
d.直列化可能
5.innodbのロックモード及びロック方法
a.共有ロック:1つのトランザクションが1行を読むことを許可し、他のトランザクションが同じデータセットの排他的ピンを取得することを阻止する
b.排他ロック:排他ロックを取得したトランザクションがデータを更新することを許可し、他のトランザクションが同じデータセットの共有リードロックと排他ライトロックを取得することを阻止する
6.トランザクションは次のように共有ロックまたは排他ロックを追加できます.
a.共有ロック:select*from testTable where...lock in share mode
b.排他ロック:select*from testTable where...for update
7.innodb行ロックの実現方式:
a.record lock:インデックス・アイテムにロックをかける
b.gap lock:インデックス項目間のギャップをロックする
c.next-key lock:記録およびギャップにロック、
8.ロック競合を分析する場合、sql実行計画を確認して、インデックスが本当に使用されているかどうかを確認する必要がある場合があります.
9.表ロック関連:
a.トランザクションは大部分または全部のデータを更新する必要がある
b.トランザクションは複数のテーブルに関連してデッドロックを引き起こす可能性があり、大量のトランザクションがロールバックする
c.innodbテーブルロックは「システムレベル」ロック
10.その他:
a.なるべく低い隔離レベルを使用する
b.インデックスを丹念に設計し、できるだけインデックスを使用してデータにアクセスし、ロックをより正確にし、ロック衝突の機会を減らす
c.合理的なトランザクションサイズを選択し、小さなトランザクションがロック競合する確率がより小さい
d.レコードセットに明示的にロックをかける場合は、十分なレベルのロックを一度に要求することが望ましい.デッドロックを避けることができます
e.異なるプログラムが一組のテーブルにアクセスする場合、できるだけ同じ順序で各テーブルにアクセスすることを約束する.1つのテーブルに対して、テーブル内のローにできるだけ一定の順序でアクセスします.
f.できるだけ等しい条件でデータにアクセスすることでnext-keyロックが同時挿入に与える影響を回避できる
g.必要がない限り、クエリー時に明示的にロックしないで、実際に必要なロックレベルを超えたロックを申請しないでください.
h.特定のトランザクションについては、テーブルロックを使用して処理速度を向上させたり、デッドロックの発生確率を低減したりすることができます.
二、実践:
1.
session_1:
mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 30735 |
| Innodb_row_lock_time_avg | 2561 |
| Innodb_row_lock_time_max | 7558 |
| Innodb_row_lock_waits | 12 |
+-------------------------------+-------+
5 rows in set (0.00 sec)
mysql> use sakila;
Database changed
mysql> select * from information_schema.innodb_locks \G;
Empty set (0.01 sec)
ERROR:
No query specified
mysql> create table innodb_monitor(a int) engine = innodb;
Query OK, 0 rows affected (0.04 sec)
mysql> show engine innodb status \G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
151030 22:39:24 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 110 1_second, 110 sleeps, 7 10_second, 42 background, 42 flush
srv_master_thread log flush and writes: 110
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 21, signal count 21
Mutex spin waits 15, rounds 450, OS waits 15
RW-shared spins 6, rounds 180, OS waits 6
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 910
Purge done for trx's n:o < 904 undo n:o < 0
History list length 101
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 32, OS thread handle 0x7f292436c700, query id 704 localhost root
show engine innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
574 OS file reads, 45 OS file writes, 30 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.75 writes/s, 0.50 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 553229, node heap has 1 buffer(s)
0.00 hash searches/s, 0.44 non-hash searches/s
---
LOG
---
Log sequence number 13464743
Log flushed up to 13464743
Last checkpoint at 13464743
0 pending log writes, 0 pending chkp writes
20 log i/o's done, 0.19 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 274726912; in additional pool allocated 0
Dictionary memory allocated 176080
Buffer pool size 16383
Free buffers 15831
Database pages 551
Old database pages 221
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 543, created 8, written 31
0.00 reads/s, 0.25 creates/s, 0.62 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 551, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 45085, id 139814181046016, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 23
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.01 sec)
ERROR:
No query specified
mysql> drop table innodb_monitor;
Query OK, 0 rows affected (0.01 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select actor_id,first_name,last_name from actor where actor_id = 1 lock in share mode;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
| 1 | PENELOPE | GUINESS |
+----------+------------+-----------+
1 row in set (0.01 sec)
mysql> update actor set last_name = 'lastNameTexst' where actor_id = 1;
^CCtrl-C -- sending "KILL QUERY 32" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> update actor set last_name = 'lastNameTexst' where actor_id = 1;
^CCtrl-C -- sending "KILL QUERY 32" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> update actor set last_name = 'lastNameTexst' where actor_id = 1;
^CCtrl-C -- sending "KILL QUERY 32" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
session_2:
mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select film_id,title from film_text where film_id = 1 for update;
+---------+-------+
| film_id | title |
+---------+-------+
| 1 | test |
+---------+-------+
1 row in set (0.01 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select actor_id,first_name,last_name from actor where actor_id = 1;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
| 1 | PENELOPE | GUINESS |
+----------+------------+-----------+
1 row in set (0.00 sec)
mysql> select actor_id,first_name,last_name from actor where actor_id = 1 lock in share mode;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
| 1 | PENELOPE | GUINESS |
+----------+------------+-----------+
1 row in set (0.00 sec)
mysql> update actor set last_name = "test" where actor_id = 1;
^CCtrl-C -- sending "KILL QUERY 33" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
2.
session_1:
mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select actor_id,first_name,last_name from actor where actor_id = 1;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
| 1 | PENELOPE | GUINESS |
+----------+------------+-----------+
1 row in set (0.00 sec)
mysql> select actor_id,first_name,last_name from actor where actor_id = 1 for update;
^CCtrl-C -- sending "KILL QUERY 38" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select actor_id,first_name,last_name from actor where actor_id = 1 for update;
^CCtrl-C -- sending "KILL QUERY 38" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> Ctrl-C -- exit!
Aborted
mysql> create table tab_no_index(id int,name varchar(10)) engine = innodb;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into tab_no_index values (1,'1'),(2,'2'),(3,'3'),(4,'4');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab_no_index where id = 1;
+------+------+
| id | name |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.01 sec)
mysql> select * from tab_no_index where id = 1 for update;
+------+------+
| id | name |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)
session_2:
mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab_no_index where id = 2;
+------+------+
| id | name |
+------+------+
| 2 | 2 |
+------+------+
1 row in set (0.00 sec)
mysql> select * from tab_no_index where id = 2 for update;
^CCtrl-C -- sending "KILL QUERY 42" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
3.
session_1:
mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from actor limit 10;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+--------------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 |
| 3 | ED | CHASE | 2006-02-15 04:34:33 |
| 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 |
| 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
| 6 | BETTE | NICHOLSON | 2006-02-15 04:34:33 |
| 7 | GRACE | MOSTEL | 2006-02-15 04:34:33 |
| 8 | MATTHEW | JOHANSSON | 2006-02-15 04:34:33 |
| 9 | JOE | SWANK | 2006-02-15 04:34:33 |
| 10 | CHRISTIAN | GABLE | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
10 rows in set (0.00 sec)
mysql> select * from actor where actor_id = 1 for update;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)
mysql> select * from actor where actor_id = 30920 for update;
Empty set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
session_2:
mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select max(actor_id) from actor;
+---------------+
| max(actor_id) |
+---------------+
| 200 |
+---------------+
1 row in set (0.02 sec)
mysql> insert into actor (actor_id) values (202);
^CCtrl-C -- sending "KILL QUERY 47" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into actor (actor_id) values (202);
Query OK, 1 row affected, 2 warnings (0.01 sec)
mysql> select max(actor_id) from actor;
+---------------+
| max(actor_id) |
+---------------+
| 202 |
+---------------+
1 row in set (0.00 sec)