Apakah maksud kunci peringkat baris Mysql?

Perkara pertama yang perlu kita ketahui ialah kunci mysql dilaksanakan oleh enjin storan tertentu. Oleh itu, terdapat perbezaan dalam mekanisme pelaksanaan kunci enjin lalai MySQL MyISAM dan enjin pemalam pihak ketiga InnoDB.

Mysql mempunyai tiga peringkat penguncian: penguncian peringkat jadual, penguncian peringkat halaman, penguncian peringkat baris

Definisi

Setiap kali Mekanisme penguncian yang mengunci baris data ialah penguncian peringkat baris (peringkat baris). Penguncian peringkat baris bukanlah kaedah penguncian yang dilaksanakan oleh MySQL sendiri, tetapi dilaksanakan oleh enjin storan lain sendiri

2. Kelebihan dan Kekurangan

Kebaikan

• Disebabkan butiran kunci yang kecil, kadar pertikaian adalah rendah dan konkurensi adalah tinggi.

2. Kelemahan

• Ia rumit untuk dilaksanakan dan mahal.

• Penguncian adalah perlahan dan kebuntuan mudah berlaku

3 Sokongan enjin storan

• Gunakan baris-. penguncian tahap Yang utama ialah enjin storan InnoDB dan enjin storan teragih MySQL NDBCluster

4 Jenis kunci peringkat baris

Kunci peringkat baris InnoDB juga dibahagikan kepada dua jenis. : kunci kongsi dan kunci eksklusif Untuk membolehkan kunci peringkat baris dan kunci peringkat meja wujud bersama semasa pelaksanaan mekanisme penguncian, InnoDB juga menggunakan konsep kunci niat (kunci peringkat meja), dan terdapat niat dikongsi. kunci dan niat kunci eksklusif Dua jenis.

Fungsi kunci niat ialah apabila transaksi perlu mendapatkan kunci sumber, jika sumber yang diperlukannya sudah diduduki oleh kunci eksklusif, transaksi boleh menambah baris yang sesuai pada jadual yang perlu dikunci kunci niat. Jika anda memerlukan kunci kongsi, tambahkan kunci kongsi niat di atas meja. Jika anda perlu menambah kunci eksklusif pada baris tertentu (atau beberapa baris), tambahkan kunci eksklusif niat di atas meja dahulu.

Kunci perkongsian niat berbilang boleh wujud pada masa yang sama, tetapi hanya satu kunci eksklusif niat boleh wujud pada masa yang sama. Oleh itu, boleh dikatakan bahawa mod penguncian InnoDB sebenarnya boleh dibahagikan kepada empat jenis: kunci kongsi (S), kunci eksklusif (X), kunci kongsi niat (IS) dan kunci eksklusif niat (IX)

kunci mod Keserasian:

5. Pelaksanaan penguncian peringkat baris

Kunci baris InnoDB dilaksanakan dengan mengunci item indeks pada indeks. Oleh itu, InnoDB menggunakan kunci peringkat baris hanya apabila data diambil melalui keadaan indeks Jika tidak, InnoDB akan menggunakan kunci jadual. Nota lain:

• Apabila membuat pertanyaan tanpa syarat indeks, InnoDB menggunakan kunci meja dan bukannya kunci baris.

• Memandangkan kunci baris MySQL adalah untuk indeks, bukan untuk rekod, walaupun anda mengakses rekod dalam baris yang berbeza, jika kunci indeks yang sama digunakan, konflik kunci akan berlaku.

• Apabila jadual mempunyai berbilang indeks, transaksi yang berbeza boleh menggunakan indeks yang berbeza untuk mengunci baris yang berbeza Selain itu, sama ada menggunakan indeks kunci utama, indeks unik atau indeks biasa, InnoDB akan Menggunakan kunci baris untuk mengunci data.

• Walaupun medan indeks digunakan dalam keadaan, sama ada untuk menggunakan indeks untuk mendapatkan data ditentukan oleh MySQL dengan menilai kos pelan pelaksanaan yang berbeza Jika MySQL mempertimbangkan jadual penuh kecekapan imbasan Lebih tinggi, sebagai contoh, untuk beberapa jadual yang sangat kecil, ia tidak akan menggunakan indeks Dalam kes ini, InnoDB akan menggunakan kunci meja dan bukannya kunci baris. Oleh itu, apabila menganalisis konflik kunci, jangan lupa untuk menyemak pelan pelaksanaan SQL untuk mengesahkan sama ada indeks itu benar-benar digunakan.

Penguncian tersirat:

• InnoDB menambah kunci niat secara automatik.

• Untuk penyataan KEMASKINI, PADAM dan INSERT, InnoDB akan menambah kunci eksklusif (X) secara automatik pada set data yang terlibat

• Untuk SELECT biasa kenyataan , InnoDB tidak akan menambah sebarang kunci;

Kunci paparan:

• Kunci kongsi (S): PILIH * DARI nama_jadual DI MANA ... KUNCI DALAM MOD KONGSI

• Kunci eksklusif (X): PILIH * DARI table_name DI MANA ... UNTUK KEMASKINI

Gunakan PILIH … DALAM MOD KONGSI Mendapatkan kunci kongsi digunakan terutamanya untuk mengesahkan sama ada baris rekod tertentu wujud apabila kebergantungan data diperlukan dan untuk memastikan tiada sesiapa yang melakukan operasi KEMASKINI atau PADAM pada rekod ini.

Namun, jika transaksi semasa juga perlu mengemas kini rekod, ia berkemungkinan akan menyebabkan kebuntuan Bagi aplikasi yang perlu mengemas kini rekod baris selepas menguncinya, anda harus menggunakan SELECT... FOR UPDATE kepada dapatkan kunci eksklusif.

Cara mengunci jadual dalam InnoDB:

Apabila menggunakan LOCK TABLES untuk mengunci jadual InnoDB, berhati-hati untuk menetapkan AUTOCOMMIT kepada 0, jika tidak MySQL tidak akan mengunci jadual sebelum transaksi tamat , jangan gunakan UNLOCK TABLES untuk melepaskan kunci meja, kerana UNLOCK TABLES secara tersirat akan melakukan transaksi COMMIT atau ROLLBACK tidak boleh melepaskan kunci peringkat jadual yang ditambahkan dengan LOCK TABLES dan UNLOCK TABLES mesti digunakan untuk melepaskan kunci meja.

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

Memandangkan kita semua menggunakan kunci meja, apa kata pilih enjin MyISAM!

六、间隙锁（Next-Key锁）

1. 间隙锁定义：

Innodb的锁定规则是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。 Innodb的这种锁定实现方式被称为“ NEXT-KEY locking” （间隙锁），因为Query执行过程中通过范围查找的话，它会锁定整个范围内所有的索引键值，即使这个键值并不存在。

例：假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

mysql> select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

2. 间隙锁的缺点：

• 间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

• 当Query无法利用索引的时候， Innodb会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；

• 当Quuery使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所指向的数据可能有部分并不属于该Query的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；

• 当Query在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定

3 . 间隙锁的作用：

• 防止幻读，以满足相关隔离级别的要求。

• 为了数据恢复和复制的需要。

4. 注意

• 在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

• InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁。

七、查看行级锁争用情况

执行SQL:mysql> show status like 'InnoDB_row_lock%';

mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+| Variable_name                 | Value |
+-------------------------------+-------+| InnoDB_row_lock_current_waits | 0     |
| InnoDB_row_lock_time          | 0     |
| InnoDB_row_lock_time_avg      | 0     |
| InnoDB_row_lock_time_max      | 0     |
| InnoDB_row_lock_waits         | 0     |+-------------------------------+-------+

如果发现锁争用比较严重，还可以通过设置InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。如：

设置监视器：mysql> create table InnoDB_monitor(a INT) engine=InnoDB;

查看：mysql> show engine InnoDB status;

停止查看：mysql> drop table InnoDB_monitor;

具体参考：InnoDB Monitor

八、死锁

什么是死锁：你等我释放锁，我等你释放锁就会形成死锁。

如何发现死锁： 在InnoDB的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在

解决办法：

• 回滚较小的那个事务

• 在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。

判断事务大小：事务各自插入、更新或者删除的数据量

注意：

• 当产生死锁的场景中涉及到不止InnoDB存储引擎的时候，InnoDB是没办法检测到该死锁的，这时候就只能通过锁定超时限制参数InnoDB_lock_wait_timeout来解决。

九、优化行级锁定

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

（1）要想合理利用InnoDB的行级锁定，做到扬长避短，我们必须做好以下工作：

• Benarkan semua pengambilan data dilakukan melalui indeks sebanyak mungkin, dengan itu menghalang InnoDB daripada ditingkatkan kepada penguncian peringkat jadual kerana ia tidak boleh dikunci melalui kekunci indeks

• Reka bentuk indeks dengan munasabah supaya InnoDB boleh setepat mungkin semasa mengunci kunci indeks, mengecilkan skop penguncian sebanyak mungkin dan mengelakkan penguncian yang tidak perlu yang menjejaskan pelaksanaan pertanyaan lain; >

Kurangkan keadaan penapis pengambilan data berasaskan julat sebanyak mungkin untuk mengelakkan rekod kunci yang tidak sepatutnya dikunci kerana kesan negatif kunci jurang; untuk mengawal saiz urus niaga dan mengurangkan kunci Jumlah sumber dan tempoh masa penguncian
Apabila persekitaran perniagaan membenarkan, cuba gunakan tahap pengasingan transaksi yang lebih rendah untuk mengurangkan kos tambahan; MySQL melaksanakan tahap pengasingan transaksi.
(2) Disebabkan penguncian peringkat baris dan sifat transaksional InnoDB, kebuntuan pasti akan berlaku Berikut ialah beberapa petua yang biasa digunakan untuk mengurangkan kebarangkalian kebuntuan:
Dalam modul perniagaan yang serupa, akses dalam jujukan akses yang sama sebanyak mungkin untuk mengelakkan kebuntuan; diperlukan untuk satu kunci untuk mengurangkan kebarangkalian jalan buntu; kebarangkalian kebuntuan.

Cadangan berkaitan: "
• tutorial mysql

  "

Atas ialah kandungan terperinci Apakah maksud kunci peringkat baris Mysql?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!