Apakah peraturan untuk mengunci kunci jurang mysql?

11 peraturan untuk penguncian kunci celah mysql

Kunci celah hanya akan berkuat kuasa di bawah tahap pengasingan bacaan boleh berulang: kunci kekunci seterusnya sebenarnya dilaksanakan oleh kunci celah ditambah kunci baris Jika anda mencapai tahap pengasingan komited baca (komited baca), ia akan mudah difahami Bahagian kunci jurang dikeluarkan dalam proses, iaitu, hanya bahagian kunci baris yang tinggal. Di bawah tahap pengasingan komit baca, tiada kunci jurang Untuk menyelesaikan kemungkinan ketidakkonsistenan antara data dan log, format binlog perlu ditetapkan kepada baris. Dalam erti kata lain, konfigurasi banyak syarikat ialah: baca tahap pengasingan komit ditambah binlog_format=row. Perniagaan tidak memerlukan jaminan bacaan berulang, jadi memandangkan julat kunci data operasi di bawah penyerahan bacaan adalah lebih kecil (tiada kunci jurang), pilihan ini adalah munasabah
.

Peraturan penguncian kunci kekunci seterusnya

Peraturan penguncian yang diringkaskan termasuk dua "prinsip", dua "pengoptimuman" dan "pepijat".
Prinsip 1: Unit asas penguncian ialah kunci kekunci seterusnya. kunci kekunci seterusnya ialah selang terbuka dan tertutup.
Prinsip 2: Hanya objek yang diakses semasa proses carian akan dikunci. Sebarang kunci pada indeks sekunder, atau kunci pada lajur tidak diindeks, akhirnya akan dikesan kembali ke kunci utama dan kunci juga akan ditambahkan pada kunci utama.
Pengoptimuman 1: Untuk pertanyaan setara pada indeks, apabila mengunci indeks unik, kunci kekunci seterusnya merosot menjadi kunci baris. Maksudnya, jika InnoDB mengimbas kunci utama atau indeks unik, InnoDB hanya akan menggunakan kunci baris untuk mengunci
Pengoptimuman 2: Pertanyaan setara pada indeks (tidak semestinya indeks unik), ke Semasa traversal kanan dan apabila nilai terakhir tidak memenuhi syarat kesamaan, kunci kekunci seterusnya merosot menjadi kunci jurang.
Pepijat: pertanyaan julat pada indeks unik akan mengakses nilai pertama yang tidak memenuhi syarat.

Analisis kes

Kami mengambil ujian jadual sebagai contoh Penyataan penciptaan jadual dan penyataan permulaan adalah seperti berikut: id ialah indeks kunci utama

CREATE TABLE `test` (
id` int(11) NOT NULL,
col1` int(11) DEFAULT NULL,
col2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`col1`)
) ENGINE=InnoDB;
insert into test values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

Kes 1: Unik. kunci jurang pertanyaan setara indeks

Memandangkan tiada rekod dengan id=7 dalam ujian jadual

Mengikut prinsip 1, unit pengunci adalah kunci seterusnya kunci, sesi A julat penguncian Ia adalah (5,10];

Pada masa yang sama, mengikut pengoptimuman 2, ini ialah pertanyaan setara (id=7), dan id=10 tidak memenuhi pertanyaan keadaan, dan kunci kekunci seterusnya merosot menjadi kunci celah, jadi pada akhirnya Julat penguncian ialah (5,10)

Kes 2: Kunci pertanyaan setara indeks bukan unik

Di sini sesi A ingin menambah col1 pada indeks col1 =5 menambah kunci baca pada baris ini

Mengikut prinsip 1, unit pengunci ialah kunci kekunci seterusnya, dibiarkan terbuka. dan kanan ditutup, 5 ditutup, jadi (0,5] akan ditambah kunci kekunci seterusnya

Perlu diingat bahawa c ialah indeks biasa, jadi hanya mengakses rekod c=5 tidak boleh berhenti serta-merta ( mungkin terdapat rekod lain dengan col1=5),

diperlukan Traverse ke kanan dan menyerah hanya apabila c=10 ditemui Menurut prinsip kedua, semua akses perlu dikunci, jadi seterusnya-. kunci kekunci perlu ditambah pada selang (5,10).

Tetapi pada masa yang sama Ini mematuhi pengoptimuman 2: pertimbangan kesamarataan, melintasi ke kanan, nilai terakhir tidak memenuhi syarat kesamaan col1=5, jadi ia merosot kepada kunci celah (5,10, mengikut prinsip 2, hanya objek yang akan dikunci

Oleh itu, tiada kunci ditambahkan pada indeks kunci utama, yang menerangkan mengapa pernyataan kemas kini sesi B boleh dilaksanakan dengan jayanya

Tetapi jika sesi C ingin memasukkan rekod (7,7,. 7), ia akan dikunci oleh kunci jurang sesi A (5,10). Contoh ini menunjukkan bahawa kunci itu ditambahkan pada indeks. data seterusnya, jadi ia akan menambah kunci baris pada baris yang memenuhi syarat pada indeks kunci utama

Jika anda ingin menggunakan kunci dalam mod kongsi Jika anda menambah kunci baca pada baris untuk menghalang data daripada dikemas kini, anda mesti memintas pengoptimuman indeks penutup, kerana indeks penutup tidak akan mengakses indeks kunci utama dan tidak akan mengunci indeks kunci utama

Kes 3: Julat indeks kunci utama Kunci pertanyaan

Apabila memulakan pelaksanaan, anda perlu mencari baris pertama dengan id=10, jadi ia sepatutnya menjadi kunci kekunci seterusnya(5,10]. Mengikut pengoptimuman 1, keadaan kesetaraan pada kunci utama

id merosot menjadi kunci baris dan hanya kunci baris untuk baris id=10 ditambah.

Ini adalah pertanyaan julat Untuk carian julat, teruskan carian sehingga anda menemui baris id=15 dan berhenti syaratnya, jadi anda perlu menambah kunci kekunci seterusnya(10,15].

Sesi A Pada masa ini, skop kunci ialah indeks kunci utama, kunci baris id=10 dan kunci kunci seterusnya(10,15]. Apabila sesi A mencari baris dengan

id=10 untuk kali pertama, ia dinilai sebagai pertanyaan setara , dan apabila mengimbas ke kanan ke id=15, pertimbangan pertanyaan julat digunakan.

Kes 4: Kunci pertanyaan julat indeks bukan unik

Apabila col1=10 digunakan untuk mencari rekod buat kali pertama, (5,10] ditambah kepada indeks c Selepas kunci kekunci seterusnya ini, memandangkan indeks col1 ialah indeks bukan unik, tiada peraturan pengoptimuman, yang bermaksud bahawa ia tidak akan berubah menjadi kunci baris Oleh itu, kunci terakhir yang ditambahkan pada sesi A ialah (5 ,10] pada indeks c. dan
(10,15] dua kunci kekunci seterusnya ini.
Adalah munasabah untuk mengimbas ke col1=15 sebelum menghentikan imbasan, kerana InnoDB perlu mengimbas ke col1=15 sebelum mengetahui bahawa tidak ada keperluan untuk meneruskan. Menurut prinsip 1, ia sepatutnya Hanya kunci kekunci seterusnya (10,15] ditambahkan pada id indeks, dan kerana
ialah kunci unik, gelung harus berhenti apabila ia mencapai garis id=15.
Tetapi dari segi pelaksanaan, InnoDB akan Imbas ke hadapan ke tingkah laku pertama yang tidak memenuhi syarat, iaitu, id=20 Dan kerana ini adalah imbasan julat, kunci kekunci seterusnya pada id indeks (15,. 20] juga akan dikunci secara logiknya, kelakuan mengunci baris id=20 di sini sebenarnya tidak diperlukan, kerana jika anda mengimbas id=15, anda boleh yakin bahawa tidak perlu mencari

kes. Enam: Contoh " " Setara " " pada indeks bukan unik

Di sini, saya memasukkan rekod baharu ke dalam jadual t: masukkan ke dalam nilai t(30,10,30); kini jadual Terdapat dua baris dengan c=10 , tetapi nilai kunci utamanya adalah berbeza (masing-masing 10 dan 30), jadi terdapat jurang antara dua rekod dengan c=10

. artikel. Mari kita mulakan dengan dua "prinsip", dua "pengoptimuman" dan satu "pepijat". apa yang ditambahkan di sini ialah kunci kekunci seterusnya daripada

(col1=5,id=5) hingga (col1=10,id=10 Memandangkan c ialah indeks biasa, teruskan mencari ke kanan sehingga ia mencecah . Gelung berakhir pada baris (col1=15,id=15). Menurut Pengoptimuman 2, ini ialah
pertanyaan setara yang tidak memenuhi syarat ditemui di sebelah kanan, jadi ia akan merosot menjadi jurang daripada (col1=10,id=10) kepada (col1=15, id=15)
Kunci.

Julat penguncian kenyataan padam ini pada indeks c ialah bahagian yang diliputi oleh kawasan biru dalam gambar di atas. Bahagian kiri dan kanan kawasan biru ini ialah

garis putus-putus, menunjukkan selang terbuka, iaitu (col1=5,id=5) dan (col1=15,id=15 tiada kunci pada dua baris ini
Kes 7: Kenyataan had dikunci

Kenyataan padam sesi A menambah had 2. Anda tahu bahawa sebenarnya terdapat hanya dua rekod dengan c=10 dalam jadual t, jadi kesan menambah atau memadam had 2 adalah sama. Tetapi kesan penguncian adalah berbeza
Ini kerana kenyataan padam dalam Kes 7 dengan jelas menambah had had 2, jadi selepas melintasi garisan (col1=10, id=30),
memenuhi syarat Terdapat sudah dua kenyataan, dan gelung berakhir. Oleh itu, julat penguncian pada indeks col1 menjadi julat buka depan dan tutup belakang daripada (col1=5,id=5)
hingga (col1=10,id=30), seperti yang ditunjukkan dalam rajah berikut:

Kepentingan panduan contoh ini kepada amalan kami ialah cuba menambah had semasa memadam data. Ini bukan sahaja mengawal bilangan data yang dipadam, menjadikan operasi lebih selamat, tetapi juga mengurangkan skop penguncian.

Kes 8: Contoh kebuntuan

Sesi A melaksanakan pernyataan pertanyaan selepas memulakan transaksi dan menambah kunci dalam mod kongsi, dan menambah di sebelah index col1 -keylock(5,10] dan gap lock (10,15) (index traversal to the right degenerate to gap lock);

Kenyataan kemas kini sesi B juga perlu menambah next-key lock( 5) untuk mengindeks c ,10], masukkan kunci menunggu; ia sebenarnya terbahagi kepada dua langkah,

pertama tambahkan kunci jurang (5,10), kunci berjaya kemudian tambahkan kunci baris col1= 10, kerana baris ini telah ditambah dalam sesiA Kunci
baca dikunci Pada masa ini, aplikasi kebuntuan akan disekat
Kemudian sesi A mahu memasukkan baris (8,8,8) sekali lagi. yang dikunci oleh kunci celah sesi B. Disebabkan jalan buntu , InnoDB membenarkan
sesi B rollback

Kes 9: Susun mengikut indeks pengisihan kunci jurang 1

Seperti yang ditunjukkan dalam pernyataan berikut
Rajah berikut ialah rajah skematik id indeks jadual ini.
mulakan;
pilih * daripada ujian di mana id>9 dan id

Pertama sekali, semantik ini pernyataan pertanyaan adalah tertib mengikut id desc, untuk mendapatkan semua baris yang memenuhi syarat, pengoptimum mesti mencari "nilai
id Proses ini diperolehi melalui proses carian pokok indeks Di dalam enjin, kita sebenarnya ingin mencari nilai id=12, tetapi pada akhirnya
tidak dijumpai, tetapi jurang (10,15. ) ditemui. (id=15 tidak memenuhi syarat, jadi kunci kekunci seterusnya merosot kepada kunci celah (10,
15).)
Kemudian lintas ke kiri Semasa proses traversal, ia bukan lagi pertanyaan setara , dan akan mengimbas Dalam baris id=5, dan kerana selang dibuka di sebelah kiri dan ditutup di sebelah kanan, kunci kekunci seterusnya (0,5] akan ditambah. Dalam erti kata lain, semasa proses pelaksanaan, rekod terletak melalui carian pokok
Apabila 🎜>, kaedah "pertanyaan setara" digunakan mengikut col1 desc, yang pertama terletak ialah baris "paling kanan" dengan col1=20 pada indeks col1 pertanyaan setara dengan indeks bukan unik:

mula-mula bergabung dalam selang kekunci terbuka kiri, membentuk selang (15,20]. Lintas ke kanan, col1=25 tidak memenuhi syarat. , dan merosot menjadi kunci celah, jadi kunci celah (20,25) dan kunci kunci seterusnya (15,20] akan ditambah.

Berhenti mengimbas indeks ke kiri apabila melintasi ke col1=10. Kunci kekunci seterusnya (kunci kekunci seterusnya) akan digunakan pada selang buka kanan dan kiri tertutup (5,10)

Inilah yang sebenarnya berlaku sebab menyekat pernyataan sisipan sesi B. Semasa proses pengimbasan, terdapat nilai dalam tiga baris col1=20, col1=15, col1=10 Memandangkan ia adalah pilih *, kunci utama

dkan kunci tiga baris , julat kunci penyata pilihan sesi A ialah:

pada (5, 25); pada dua baris kunci id=15 dan 20 pada indeks kunci utama >Kes 11: Contoh kemas kini mengubah suai data - masukkan dahulu dan kemudian padam

Nota: Rekod pertama yang ditemui mengikut col1>5 ialah col1=10, jadi ia bukan Kunci kekunci seterusnya bagi ( 0,5] akan ditambah

Julat kunci sesi A ialah (5,10], (10,15], (15,20], (20,25] pada indeks kol1 dan (25, tertinggi] . Pernyataan kemas kini pertama sesi B selepas

ialah menukar col1=5 kepada col1=1 Anda boleh memahaminya sebagai dua langkah:
Masukkan (col1=1, id=5 ) rekod ini; Padam (col1=5, id=5) rekod ini.
Melalui operasi ini, julat penguncian sesi A menjadi seperti yang ditunjukkan dalam Rajah 7:

Okay, sesi B seterusnya perlu melaksanakan kemas kini t set col1 = 5 di mana col1 = 1 Pernyataan ini boleh dibahagikan kepada dua langkah:

Masukkan rekod (col1=5, id=5

Padam rekod (col1=1, id=5). Langkah pertama ialah cuba memasukkan data ke dalam (1,10) di mana kunci jurang telah ditambahkan, jadi ia disekat
　.

Atas ialah kandungan terperinci Apakah peraturan untuk mengunci kunci jurang mysql?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!