Bagaimana untuk menyelesaikan masalah kebuntuan MySQL (contoh terperinci)

Artikel ini membawa anda pengetahuan yang berkaitan tentang mysql Ia terutamanya memperkenalkan analisis dan perbincangan kes kebuntuan biasa, serta cara mengelakkan kebuntuan sebanyak mungkin dan memberikan beberapa Cadangan, harap ia membantu semua orang. .

Pembelajaran yang disyorkan: tutorial mysql

1. Apakah kebuntuan

Kebuntuan ialah sistem serentak Masalah biasa juga akan muncul dalam senario permintaan baca dan tulis serentak pangkalan data MySQL. Apabila dua atau lebih urus niaga sedang menunggu antara satu sama lain untuk melepaskan kunci yang telah mereka pegang, atau susunan penguncian tidak konsisten dan menyebabkan kitaran menunggu sumber kunci, "kebuntuan" akan berlaku. Mesej ralat biasa ialah Deadlock found when trying to get lock....

Sebagai contoh, transaksi A memegang kunci X1 dan terpakai untuk kunci X2, transaksi B memegang kunci X2 dan menggunakan kunci X1. Transaksi A dan B memegang kunci dan memohon kunci yang dipegang oleh pihak yang satu lagi dan menunggu dalam gelung, menyebabkan kebuntuan.

Seperti yang ditunjukkan dalam gambar di atas, permintaan sumber bagi empat buah kereta di sebelah kanan menyebabkan fenomena gelung, iaitu gelung tak terhingga, mengakibatkan jalan buntu.

Daripada definisi kebuntuan, beberapa faktor kebuntuan dalam MySQL ialah:

1. Dua atau lebih transaksi

2. Setiap transaksi sudah memegang kunci dan memohon kunci baharu

3. Sumber kunci hanya boleh dipegang oleh transaksi yang sama pada masa yang sama atau tidak serasi

4. Transaksi menunggu antara satu sama lain dalam gelung kerana memegang kunci dan memohon kunci

2 jenis kunci InnoDB

Untuk menganalisis kebuntuan, kami mempunyai Ia adalah perlu untuk memahami jenis kunci InnoDB.

Enjin MySQL InnoDB melaksanakan standard 行级别锁：共享锁( S lock ) 和排他锁 ( X lock )

1. Transaksi yang berbeza boleh menambah S pada baris yang sama rekod pada masa yang sama Kunci.

2. Jika transaksi menambahkan kunci X pada baris rekod tertentu, transaksi lain tidak boleh menambah kunci S atau kunci X, mengakibatkan kunci menunggu.

Jika transaksi T1 memegang kunci S baris r, maka apabila transaksi lain T2 meminta kunci r, pemprosesan berikut akan dilakukan:

1. Permintaan T2 untuk kunci S dibenarkan serta-merta, dan akibatnya, T1 dan T2 kedua-duanya memegang kunci S untuk baris r

2. Permintaan T2 untuk kunci X tidak boleh dibenarkan serta-merta

Jika T1 memegang kunci X r, maka permintaan T2 untuk kunci X dan S r tidak boleh dibenarkan serta-merta untuk melepaskan Kunci tidak serasi. Keserasian kunci kongsi dan kunci eksklusif adalah seperti berikut:

2.1 Kunci celah (kunci celah)

Kunci celah mengunci celah untuk mengelakkan pemasukan . Anggapkan bahawa lajur indeks mempunyai tiga nilai 2, 4, dan 8. Jika 4 dikunci, dua jurang (2,4) dan (4,8) juga akan dikunci pada masa yang sama. Urus niaga lain tidak boleh memasukkan rekod dengan nilai indeks di antara dua jurang ini. Walau bagaimanapun, terdapat pengecualian untuk mengunci jurang:

1. Jika lajur indeks ialah indeks unik, maka hanya rekod ini akan dikunci (hanya kunci baris akan ditambah), bukan kunci.

2. Untuk indeks bersama dan ia adalah indeks unik, jika keadaan di mana hanya termasuk sebahagian daripada indeks sendi, kunci celah masih akan ditambah.

2.2, kunci kekunci seterusnya

kunci kekunci seterusnya sebenarnya merupakan gabungan kunci baris dan kunci celah di hadapan rekod ini. Dengan mengandaikan bahawa terdapat nilai indeks 10, 11, 13 dan 20, maka kunci kekunci seterusnya yang mungkin termasuk:

(infiniti negatif, 10], (10, 11], (11, 13], ( 13,20], (20, infiniti positif)

Di bawah tahap pengasingan RR, InnoDB menggunakan kunci kekunci seterusnya terutamanya untuk mengelakkan masalah 幻读 daripada berlaku. 2.3. Niat. Kunci (Kunci niat)

Untuk menyokong penguncian berbilang butiran, InnoDB membenarkan kunci baris dan kunci meja wujud pada masa yang sama Untuk menyokong operasi penguncian pada butiran yang berbeza, InnoDB menyokong kaedah penguncian tambahan . Ia dipanggil Kunci Niat membahagikan objek yang dikunci ke dalam berbilang tahap kunci niat yang ingin dikunci pada butiran yang lebih halus:

Kunci Dikongsi Niat (IS): Transaksi bercadang untuk menambah kunci kongsi pada baris tertentu dalam jadual

1. Kunci Eksklusif Niat (IX): Niat Transaksi untuk menambah kunci eksklusif pada baris tertentu dalam jadual

2. Memandangkan enjin storan InnoDB menyokong kunci peringkat baris, kunci niat sebenarnya tidak akan menyekat imbasan jadual penuh Sebarang permintaan selain daripada kunci niat peringkat jadual dan kunci peringkat baris adalah serasi seperti berikut:

2.4. Kunci Insert Intention (Masukkan kunci Niat)

Kunci Insert Intention ialah kunci celah yang ditetapkan sebelum memasukkan barisan rekod Kunci ini mengeluarkan isyarat kaedah pemasukan, iaitu berbilang Apabila urus niaga dimasukkan ke dalam jurang indeks yang sama, mereka tidak perlu menunggu antara satu sama lain melainkan mereka dimasukkan ke dalam kedudukan yang sama dalam jurang. Andaikan lajur mempunyai nilai indeks 2 dan 6. Selagi kedudukan sisipan kedua-dua transaksi adalah berbeza (contohnya, transaksi A sisipan 3 dan transaksi B sisipan 4), maka ia boleh dimasukkan pada masa yang sama.

2.5. Matriks keserasian mod kunci

kunci dipegang secara mendatar, dan kunci diminta secara menegak:

3. Membaca Log Kebuntuan

Sebelum menganalisis kes tertentu, mari kita fahami dahulu cara membaca log kebuntuan dan gunakan maklumat dalam log kebuntuan sebanyak mungkin untuk membantu kami menyelesaikan masalah kebuntuan.

Senario pangkalan data bagi kes ujian berikut adalah seperti berikut:MySQL 5.7 事务隔离级别为 RR

Struktur jadual dan data adalah seperti berikut:

Kes ujian adalah seperti berikut:

Anda boleh melihat log kebuntuan terkini dengan melaksanakan status innodb enjin paparan.

3.1. Analisis log adalah seperti berikut:

1.***** (1) TRANSAKSI: TRANSAKSI 2322, AKTIF 6 saat indeks permulaan dibaca

Transaksi nombor ialah 2322, Aktif selama 6 saat, mula membaca indeks menunjukkan bahawa status transaksi sedang membaca data mengikut indeks. Status biasa yang lain ialah:

mysql tables in use 1 menunjukkan bahawa transaksi semasa menggunakan jadual.

locked 1 bermakna terdapat kunci jadual di atas meja Untuk pernyataan DML, ia adalah LOCK_IX Panjangnya ialah 2, dan setiap nod senarai terpaut mewakili struktur kunci yang dipegang oleh transaksi, termasuk kunci meja, rekod. kunci, dan kunci kenaikan automatik. Dalam kes penggunaan ini, 2kunci mewakili kunci IX dan mod_kunci X (Kunci kunci seterusnya)

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

mewakili bilangan kunci rekod baris/kunci jurang yang dipegang oleh transaksi semasa. LOCK WAIT2 lock struct(s)

Menunjukkan bahawa ID benang yang melaksanakan transaksi ialah 37 (iaitu tunjukkan senarai proses; ID yang dipaparkan) 1 row lock(s)

MySQL thread id 37, OS thread handle 140445500716800, query id 1234 127.0.0.1 root updating

Menunjukkan SQL yang transaksi 1 sedang dilaksanakan, yang agak tidak selesa Perkaranya ialah

tidak dapat melihat sql yang lengkap Ia biasanya memaparkan sql yang sedang menunggu kunci. MySQL thread id 37

KUNCI REKOD mewakili kunci rekod Kandungan ini menunjukkan bahawa transaksi 1 sedang menunggu kunci X idx_stuno pada pelajar meja. delete from student where stuno=5show engine innodb statusLog transaksi 2 adalah serupa dengan analisis di atas:

 ***** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2322 lock_mode X waiting

Ia menunjukkan bahawa sisipan transaksi 2 ke dalam nilai pelajar(stuno,skor)(2,10) memegang Kunci mod a=5 Sukar untuk menganalisis punca masalah kebuntuan.

3.***** (2) MENUNGGU LOCK INI DIBERIKAN:

2.***** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X

REKOD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw**** .****student trx id 2321 lock_mode >

4. Analisis kes klasik

4.1. Transaksi serentak memasukkan konflik kunci unik

Struktur jadual dan data adalah seperti berikut:

Contoh ujian adalah seperti berikut:

Analisis log adalah seperti berikut :

Transaksi T2 masukkan ke dalam nilai t7(id,a) ​​(26,10) Sisipan penyata berjaya, memegang a=10

transaksi T1 masukkan ke dalam nilai t7(id,a) ​​(30 ,10), Kerana sisipan pertama T2 telah pun memasukkan rekod a=10 dan transaksi T1 memasukkan a=10, a konflik kunci unik berlaku dan anda perlu memohon indeks unik konflik ditambah S Kunci kekunci seterusnya (iaitu, kunci mod S menunggu ) Ini ialah

yang akan digunakan untuk mengunci kawasan jurang antara (,10 ], (10,20].

Transaksi T2 masukkan ke dalam nilai t7(id,a) (40, 9) Nilai a=9 yang dimasukkan oleh penyata ini digunakan oleh transaksi T1, jadi transaksi kedua transaksi T2 diperlukan. Penyata sisipan perlu menunggu gap 锁4-10之间 keluaran transaksi T1, dan lock_mode X mengunci jurang sebelum niat memasukkan semula dipaparkan dalam log. S-Next-key Lock 锁

4.2. Masalah kebuntuan Concurrency kemas kini dahulu dan kemudian masukkan

Struktur jadual adalah seperti berikut, tiada data:

Contoh ujian adalah seperti berikut:

Analisis kebuntuan:

Anda dapat melihat bahawa dua rekod kemas kini transaksi yang tidak wujud telah memperoleh
satu selepas yang lain, dan kunci jurang adalah antara Serasi supaya ia tidak akan disekat semasa proses kemas kini. Kedua-duanya memegang kunci celah dan kemudian bersaing untuk memasukkan 间隙锁( gap 锁). Apabila terdapat sesi lain yang memegang kunci jurang, sesi semasa tidak boleh memohon untuk kunci niat sisipan, mengakibatkan kebuntuan. 意向锁

5. Bagaimana untuk mengelakkan kebuntuan sebanyak mungkin

1. Reka bentuk indeks dengan munasabah, dan letakkan lajur dengan pembezaan tinggi di hadapan indeks gabungan, supaya perniagaan SQL boleh melalui indeks sebanyak mungkin

   . 定位更少的行，减少锁竞争

2. Laraskan susunan pelaksanaan SQL logik perniagaan untuk mengelakkan kemas kini/padam SQL yang memegang kunci untuk masa yang lama di hadapan transaksi.

3. Elakkan

   dan cuba bahagikan urus niaga besar kepada berbilang transaksi kecil untuk diproses. Kebarangkalian konflik kunci dalam transaksi kecil juga lebih kecil. 大事务

4. Akses jadual dan baris dengan

   . Contohnya, untuk dua transaksi yang mengemas kini data, transaksi A mengemas kini data dalam urutan 1, 2 transaksi B mengemas kini data dalam pesanan 2, 1; Ini lebih berkemungkinan menyebabkan kebuntuan. 固定的顺序

5. Dalam sistem dengan konkurensi yang agak tinggi, jangan kunci secara eksplisit, terutamanya dalam transaksi. Contohnya, pilih ... untuk penyata kemas kini, jika ia dalam transaksi

   , maka rekod yang ditemui akan dikunci. （运行了 start transaction 或设置了autocommit 等于0）

6. Cuba cari rekod dengan

   Carian julat meningkatkan kemungkinan konflik kunci Jangan gunakan pangkalan data untuk membuat pengiraan kuota tambahan. Sebagai contoh, sesetengah atur cara akan menggunakan pernyataan seperti "pilih ... di mana ... perintah mengikut rand();". 主键/索引

7. Optimumkan SQL dan reka bentuk jadual untuk mengurangkan situasi menduduki terlalu banyak sumber pada masa yang sama. Contohnya,

   , tukarkan SQL kompleks 减少连接的表 kepada berbilang SQL mudah. 分解

Pembelajaran yang disyorkan:

tutorial pembelajaran mysql

Atas ialah kandungan terperinci Bagaimana untuk menyelesaikan masalah kebuntuan MySQL (contoh terperinci). Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!