Mysqlの事務を再認識すること

4897 ワード
事務管理 事務 データベース mysql最適化 mysql
(一)事務とは何ですか.
原子的なSQLクエリーのセット、または独立した作業ユニットです.トランザクション内の文は、すべて実行するか、すべて実行しません.
シーンシミュレーション:
ユーザーAはユーザーBに1000元振り込む
  A - 1000 
  B + 1000

以上の操作は、全ての実行に成功するか、全ての実行に失敗し、ユーザAが金を引く、ユーザBが金を加えるという現象が発生する.
(二)事務の四大特性
  • 原子性(Atomicity)
    •                     ，        ，                       ，                  。
  • コンシステンシ(Consistency)
    •                ，
  • 隔離性(Isolation)
    •                  ，         ，              ，             ，             。

          ：           T1 T2，   T1  ，T2   T1         ，   T1       ，                      。

    4.持続性(Durability)
                   ，                   ，                             。

    (三)トランザクションの独立性レベル
    データベース・トランザクションの独立性レベルは、Read uncommitted(コミットされていない読み取り)、Read committed(読み取りコミット)、Repeatable read(繰り返し読み取り)、Serializable(シーケンス化)の4つです.
    しかし、トランザクションの同時操作で汚れた読み取り、繰り返し不可、幻読みが発生する可能性があります.
    1.Read uncommitted(未読)
    誘発現象:==汚い読み==
    トランザクションBは、トランザクションAがコミットしていないデータを読み出すことができる.
    シーンシミュレーション:
    社員Aさんの給料は3000元ですが、給料を出す時、社長は数字を間違えて30000元になりました.
    シーン解析:
    この状況は汚い読みで、Aさんが見た給料は30000万ですが、実際には3000元です.彼が見たのはボスがまだ提出していないデータだからです.
    ソリューション:
    Read committed、汚れた読みの問題を解決
    2.Read committed(リードコミット)
    トランザクションBは、トランザクションAのコミットを待つ必要があり、1つのトランザクションが開始するからコミットが終了するまで、他のトランザクションには変更が表示されません.再読み取り不可とも呼ばれます
    シーンシミュレーション:
    職員のAさんはデパートへ買い物に行くつもりです(カードの残高は3000元).彼が請求書を買う準備をしているとき(Aさんの事務が開いています)、料金システムはAさんのカードの持ち主が3000元で支払うことができることを検出しました.その時、Aさんの彼女はお金を全部家庭用に振り替えて提出しなければなりません(彼女の事務はAさんの前にあります)、料金システムが引き落としの準備をしているとき、カードの金額を検出して、もうお金がないことに気づきました.結局Aさんは買い物に失敗しました....
    シーン分析:これがリードコミットです.トランザクションAがデータの更新操作を行う場合、トランザクションBのリード操作はトランザクションAの更新操作のコミットが完了するまで待たなければ、データを読み取ることができません.ダーティリードの問題を解決できます.
    しかし、このシーンでは、課金システム内の1つのトランザクション範囲で、同じクエリーが2回行われているのに、異なる結果が得られます.これは==繰り返して読むことはできません(同じクエリーを2回実行しても、結果は異なります)==
    ソリューション:
    Repeatable read(繰り返し読み)で解決する.
    3.Repeatable read(繰り返し読み)
    ≪繰返し読取り｜Repeat Read｜oem_src≫:トランザクションがデータの読取りを開始すると、変更操作は許可されません.
    補足:Mysqlデフォルトのトランザクション独立性レベル:Repeatable read
    シーンシミュレーション:
    2ヶ月目、職員のAさんはまた買い物に出かけました(カードの残高3000元)、彼が請求書を買う準備をしているとき(事務が開いて、他の事務の更新操作が許されません)、料金システムは彼のカードの中の残高3000元を検査して、彼の彼女はまたこの3000元を家庭用に転出したいと思っていますが、転出に失敗して(更新できません)、料金システムの引き落としに成功して、Aさんは順調に買い物を完成しました!
    シーン解析:
    繰り返し読みは、再読不可の問題を解決することができるが、繰り返し読みは修正(Update)操作に対応するのか、途中で挿入(Insert)操作が発生し、幻読みの問題が発生する可能性がある.
    幻のシーンシミュレーション:
    職員Aさん、消費記録を調べに行って(事務が開いて)、1000元を消費したことを発見して、カードの中でまだ2000元残って、消費リストを印刷し始めました(事務が提出します)、しかしリストを印刷したばかりで、ボスは給料を出して、3000元(insertは1本の記録を追加しました)、リストの上の残高が5000元であることを発見して、前の照会の記録と間違って、これは幻読です.
    幻読:あるトランザクションがある範囲のレコードを読み取ると、別のトランザクションがその範囲に新しいレコードを挿入し、前のトランザクションがその範囲のレコードを再び読み取ると、幻行(phantom row)が発生します.
    ポイント:Mysql InnoDBストレージエンジンがNext-Key Lockingで幻読みの問題を解決
    ソリューション:
    非InnoDBエンジン幻読ソリューションSerializable(シーケンス化)
    Serializable(シーケンス化)
    Serializableは最も高いトランザクション独立性レベルであり、このレベルではトランザクションがシリアル化されて順次実行され、汚れた読み取り、重複しない読み取り、幻の読み取りを回避できます.しかし、このトランザクション・独立性レベルは効率が低下し、データベースのパフォーマンスが消費され、一般的には使用されません.Serializableは、読み出す各行のデータにロックをかけている.
    (四、事務操作命令及び実戦)
    1.データベース・エンジンがトランザクションをサポートしているかどうかを確認します(Innodbがサポートしていますか?)
    show engines

    2.Mysqlデフォルトストレージエンジンの表示
    show variables like '%storage_engine%';

    3.取引の作成
    //    
start transaction

//    
begin

//

    4.トランザクションのコミット
    COMMITはトランザクションをコミットし、データベースに対するすべての変更を永続化します.
    //  
commit 

//  
commit work

//    

//          
commit and chain

    5.取引のロールバック
    ロールバックすると、ユーザーのトランザクションが終了し、コミットされていないすべての変更が取り消されます.
    //  
rollback

//  
rollback work

//

    6.Mysqlオートコミットモード
    //        
show variables like 'autocommit'

//      
SET AUTOCOMMIT=0  

//      
SET AUTOCOMMIT=1

    7.トランザクションのリストアポイント
    //           
savepoint identifier

//         
rollback to identifier

    実戦事例:
    //    
begin;

//  
update users set name = '        ' where id = 2;

//   ts1
savepoint ts1;

//  
update users set name = '        ' where id = 2;

//   ts2
savepoint ts2;

//  
select * from users where id =2;

//   ts1
rollback to ts1;

    8.InnoDBロックの問題
    InnoDBストレージエンジンでstart transactionがトランザクションを開くと、暗黙的なunlock tablesが実行されます.
    実戦事例:
    ClientA:
    //  
    lock table users write;

ClientB:
    //  ,       
    select name from users where id = 2 ;
    
ClientA:
    //    
    update users set name = '17ns' where id = 2;
    
ClientB:
    //     
    
ClientA:
    //    ,     unlock tables
    begin;
    
ClientB:
    //

    実戦図
    末尾:
    以上がトランザクション操作の基本的な性質の主な特徴であるACID(原子性,一貫性,隔離性,持続性)であり,いくつかの重要なシーンでトランザクションを使用するとプログラムの信頼性が向上し,データがデータベースに正確にコミットされるようになる.
    mysql(InnoDB)トランザクション独立性レベル(REPEATABLE READ)とロック、MVCC
    MySQL応答時間モニタリング