mysqlでinsert、update、deleteロック

5623 ワード

テーブルのロックの探索
複数のクライアントインタフェースを開く

DROP TABLE IF EXISTS `m_user`;
CREATE TABLE `m_user` (
  `i_id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `i_name` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `is_delete` varchar(1) DEFAULT NULL,
  `i_type` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`i_id`),
  UNIQUE KEY `index_i_id` (`i_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

テストデータ:


INSERT INTO `dragsun_db`.`m_user` (`i_id`, `i_name`, `create_time`, `update_time`, `is_delete`, `i_type`) VALUES ('0000000001', 'ajason', '2017-06-13 09:03:08', '2017-07-24 09:03:11', '1', '1');
INSERT INTO `dragsun_db`.`m_user` (`i_id`, `i_name`, `create_time`, `update_time`, `is_delete`, `i_type`) VALUES ('0000000002', 'tom', '2017-06-13 09:03:08', '2017-07-24 09:03:11', '1', '2');
INSERT INTO `dragsun_db`.`m_user` (`i_id`, `i_name`, `create_time`, `update_time`, `is_delete`, `i_type`) VALUES ('0000000003', 'jane', '2017-06-13 09:03:08', '2017-07-24 09:03:11', '1', '3');
INSERT INTO `dragsun_db`.`m_user` (`i_id`, `i_name`, `create_time`, `update_time`, `is_delete`, `i_type`) VALUES ('0000000004', 'jenny', '2017-06-13 09:03:08', '2017-07-24 09:03:11', '1', '4');

前提i_type通常フィールド、インデックスなし
フィールド条件(where条件)の値がテーブルに記録されていない場合、ロックはトリガーされません(つまり、ロー数0に影響します).
alter table操作は、トランザクションを使用する必要がなく(役に立たない)、テーブルにコミットされていないトランザクションに遭遇するとブロックされます.
トランザクションオープン:begin;
トランザクション回帰:rollback;
トランザクションコミット:commit;
updateロック

  A    update  (    commit )，       ，      
1. update m_user set i_name = 'ajason' where i_id = 1;
2. update m_user set i_name = 'ajason' where i_id in (1  , 2);

  B  
   insert delete update   1.    i_id = 1       ，     
   insert delete update   2.    i_id = 1   2        ，

プライマリ・キー以外のクエリを使用すると、テーブル全体がロックされ、トランザクションがコミットされていない場合、挿入削除(delete)操作がブロックされて待機します.
結果:デッドロックになりやすい:

  A    (     )：update m_user set i_name = 'ajason' where i_type in ( 1 )
              insert , update , delete

同じ理屈:
deleteロック
トランザクションで実行される非プライマリ・キー・クエリー・ロック・テーブル、プライマリ・キー・クエリー・ロック・ロー

delete from  m_user  where i_type in ( 1 )
delete from  m_user where i_id = 1;

テーブルにトランザクションがコミットされていない場合
alter tableテーブルを変更するとデッドロックになります
このときi_typeがuniqueのbtreeインデックスに変更された場合

index_i_type	i_type	Unique

操作(update,delete同)

  A             :
update m_user set i_name = 'ajason' where i_type in ( 1 )
update m_user set i_name = 'ajason' where i_type = 1 
     B    
update m_user set i_name = 'ajason' where i_type = 4 (     ，       )
     B

Insertロック
トランザクションAが次の文を実行する場合(i_idが5で元のデータベースにないフィールド)

INSERT INTO `dragsun_db`.`m_user` (`i_id`, `i_name`, `create_time`, `update_time`, `is_delete`, `i_type`) VALUES ('0000000005', 'jenny', '2017-06-13 09:03:08', '2017-07-24 09:03:11', '1', '5');

トランザクションAはコミットされていません
1.その他の取引対i_idが5の操作は続行できません(ブロック待ち)
例えば実行(同理delete)

  ：update m_user set i_name = 'ajason' where i_id = 5;
  ：update m_user set i_name = 'ajason' where i_type = 1  (  i_type   )

2.その他の取引対i_idが5でない操作はブロックされません
例えば実行(同理delete)

  ：update m_user set i_name = 'ajason' where i_id = 6;

またはinsert操作を実行するには、3つの状況分析があります.
1.プライマリ・キーとuniqueインデックスは同じ

  B   :
INSERT INTO `dragsun_db`.`m_user` (`i_id`, `i_name`, `create_time`, `update_time`, `is_delete`, `i_type`) VALUES ('0000000005', 'jenny', '2017-06-13 09:03:08', '2017-07-24 09:03:11', '1', '5');
       ，

2.プライマリ・キーが同じで、インデックスが異なる

INSERT INTO `dragsun_db`.`m_user` (`i_id`, `i_name`, `create_time`, `update_time`, `is_delete`, `i_type`) VALUES ('0000000005', 'jenny', '2017-06-13 09:03:08', '2017-07-24 09:03:11', '1', '6');
       ，

3.プライマリ・キーが異なり、インデックスが同じ

INSERT INTO `dragsun_db`.`m_user` (`i_id`, `i_name`, `create_time`, `update_time`, `is_delete`, `i_type`) VALUES ('0000000006', 'jenny', '2017-06-13 09:03:08', '2017-07-24 09:03:11', '1', '5');
       ，

4.プライマリ・キーとインデックスが異なる相互に影響しない
結論:
説明ロー・ロックはプライマリ・キーを介しており、ユニークなインデックスはローをロックできません(通常のフィールドと同じ)、テーブルのみがロックされます.
ロックテーブル:

   insert , update , delete        ，    
  ：

テーブルとローをロックする場合:
通常のselectクエリーではブロックされません
selectロック

 ：
select * from  m_user (  ) for update
select * from  m_user (  ) lock in share mode
               select    
  ，        unique

データベースによるタイムアウト・ロックの解放
データベースロックのタイムアウトエラー:

[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

操作:
alter tableのテーブル操作に遭遇した場合、直接応答またはデッドロックはありません.

select * from information_schema.INNODB_TRX
select * from information_schema.INNODB_LOCKS
select * from information_schema.INNODB_LOCK_WAITS
show PROCESSLIST

    
kill 45(processlist   id )
            ，

Timerタイミング監視データベースについて

手書きフル配列(再帰|非再帰)