Pembelajaran MySQL bercakap tentang situasi kunci dalam InnoDB

Artikel ini akan membincangkan tentang MySQL dan memperkenalkan situasi kunci dalam InnoDB. Ia mempunyai nilai rujukan tertentu Rakan-rakan yang memerlukan boleh merujuk kepadanya.

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.21    |
+-----------+
1 row in set (0.01 sec)

1. Pengenalan asas kepada kunci

Berbanding dengan pangkalan data lain, mekanisme penguncian MySQL adalah yang paling mudah Ciri yang ketara ialah enjin storan berbeza menyokong mekanisme penguncian yang berbeza. Sebagai contoh, enjin storan MyISAM dan MEMORY menggunakan penguncian peringkat jadual; enjin storan InnoDB menyokong penguncian peringkat baris (penguncian peringkat baris) dan penguncian peringkat meja, tetapi penguncian peringkat baris digunakan secara lalai.

Kunci peringkat meja: overhed rendah, penguncian pantas; tiada kebutiran penguncian yang besar, kebarangkalian konflik kunci yang paling tinggi dan konkurensi terendah.

Kunci peringkat baris: overhed tinggi, penguncian perlahan mungkin berlaku;

Jenis kunci peringkat baris:

Kunci Rekod: Bertindak sebagai kunci rekod (mengunci satu rekod)

Kunci rekod hanya akan mengunci rekod indeks Jika jadual storan InnoDB tidak mempunyai sebarang indeks apabila ia dibuat, maka kunci ini akan menggunakan kunci utama tersirat untuk mengunci, seperti yang ditunjukkan di bawah

Kunci Jurang: Kunci julat, tidak termasuk rekod itu sendiri (kunci GAP di hadapan data sahaja)

Kunci seperti yang ditunjukkan di bawah ialah kunci GAP, yang adalah Urus niaga lain tidak dibenarkan memasukkan rekod baharu dalam jurang sebelum lajur indeks 8, iaitu selang (3, 8). Peranan kunci celah hanyalah untuk menghalang pemasukan rekod hantu

Kunci Kekunci Seterusnya: kunci pada masa yang sama Rakam dan rekodkan GAP sebelumnya, iaitu Next-Key Lock = Rekod Kunci Jurang Kunci.

2 Klasifikasi kunci

Kunci Kongsi (dirujuk sebagai kunci S, iaitu kunci baris)

Kunci Eksklusif (kunci X untuk pendek, tergolong dalam kunci baris)

Kunci Kongsi Niat (kunci IS untuk pendek, milik kunci meja)

Kunci eksklusif niat niat Kunci Eksklusif (kunci IX untuk pendek, kunci meja untuk pendek)

Kunci AUTO-INC (kunci meja untuk pendek)

Berikut ialah pengenalan terperinci untuk setiap jenis kunci Mari kita bina satu Innodb yang pertama table, sql adalah seperti berikut

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

Kunci kongsi

Kunci kongsi bermakna berbilang transaksi boleh berkongsi kunci untuk data yang sama dan semua boleh mengakses pangkalan data. Tetapi ia hanya boleh dibaca dan tidak boleh diubah suai;

Transaksi A:

pilih * daripada tab_with_index lock dalam mod kongsi;

Transaksi B:

pilih * dari tab_with_index di mana id =1; // Anda boleh menanyakan data

kemas kini nama set tab_with_index = 'aa' di mana id = 1 ;

Nota: Pernyataan pengubahsuaian di sini akan disekat dan operasi tidak boleh berjaya sehingga transaksi A diserahkan.

Kunci eksklusif

Kunci eksklusif tidak boleh wujud bersama kunci lain Jika transaksi memperoleh kunci eksklusif pada baris data, urus niaga lain tidak boleh memperoleh kunci Baris yang sama , hanya transaksi yang memperoleh kunci eksklusif pada masa ini boleh mengubah suai data. (padam, kemas kini, cipta adalah kunci eksklusif secara lalai)

Transaksi A:

pilih * daripada tab_with_index di mana id =1 untuk kemas kini;

Transaksi B:

pilih * dari tab_with_index di mana id =1; //Hasilnya boleh diperolehi

pilih * dari tab_with_index di mana id =1 untuk kemas kini ; / / Menyekat

pilih * dari tab_with_index di mana id = 1 kunci untuk mod kongsi; disekat, Maksudnya, kunci kongsi mahupun kunci eksklusif tidak boleh diperoleh, dan operasi tidak boleh berjaya sehingga transaksi A diserahkan.

Kunci dikongsi niat dan kunci eksklusif niat

Kunci dikongsi niat: menunjukkan bahawa transaksi sedang bersedia untuk menambah kunci dikongsi pada baris data, iaitu, baris data sedang menambah kunci kongsi Kunci IS jadual mesti diperolehi terlebih dahulu.

Kunci eksklusif niat: Menunjukkan bahawa transaksi sedang bersedia untuk menambah kunci eksklusif pada baris data Maksudnya, sebelum menambah kunci eksklusif pada baris data, kunci IX jadual mesti diperolehi. pertama.

Kunci IS dan kunci IX ialah kunci peringkat meja hanya dicadangkan untuk menentukan dengan cepat sama ada rekod dalam jadual dikunci apabila menambah kunci S peringkat jadual dan Gunakan traversal untuk menyemak sama ada terdapat. rekod terkunci dalam jadual Dalam erti kata lain, kunci IS dan kunci IX adalah serasi, dan kunci IX dan kunci IX adalah serasi. "Cara MySQL berjalan"

Kunci kenaikan automatik

Kunci peringkat jadual khas untuk lajur kenaikan automatik.

show variables like 'innodb_autoinc_lock_mode'; 
-- 默认值1，代表连续，事务未提交则ID永久丢失

三，InnoDB锁

1、事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

2、并发事务带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多用户的并发操作，但与此同时，会带来一下问题：

脏读： 一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”

不可重复读：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。

幻读： 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”

上述出现的问题都是数据库读一致性的问题，可以通过事务的隔离机制来进行保证。

数据库的事务隔离越严格，并发副作用就越小，但付出的代价也就越大，因为事务隔离本质上就是使事务在一定程度上串行化，需要根据具体的业务需求来决定使用哪种隔离级别

	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed		√	√
repeatable read			√
serializable

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 18702 |
| Innodb_row_lock_time_avg      | 18702 |
| Innodb_row_lock_time_max      | 18702 |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
--如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

3、InnoDB的行锁模式及加锁方法

共享锁（S） ：又称读锁(lock in share mode)。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。  排他锁（X） ：又称写锁(for update)。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

4、InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

1、在不通过索引条件查询的时候，innodb使用的是表锁而不是行锁

create table tab_no_index(id int,name varchar(10)) engine=innodb;
insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

session1	session2
set autocommit=0 select * from tab_no_index where id = 1;	set autocommit=0 select * from tab_no_index where id =2
select * from tab_no_index where id = 1 for update
	select * from tab_no_index where id = 2 for update;

session1只给一行加了排他锁，但是session2在请求其他行的排他锁的时候，会出现锁等待。原因是在没有索引的情况下，innodb只能使用表锁。

2、创建带索引的表进行条件查询，innodb使用的是行锁

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

session1	session2
set autocommit=0 select * from tab_with_indexwhere id = 1;	set autocommit=0 select * from tab_with_indexwhere id =2
select * from tab_with_indexwhere id = 1 for update
	select * from tab_with_indexwhere id = 2 for update;

3、由于mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是依然无法访问到具体的数据（这里是表锁）

alter table tab_with_index drop index id;
insert into tab_with_index  values(1,'4');

session1	session2
set autocommit=0	set autocommit=0
select * from tab_with_index where id = 1 and name='1' for update
	select * from tab_with_index where id = 1 and name='4' for update 虽然session2访问的是和session1不同的记录，但是锁的是具体表，所以需要等待锁

Ringkasan

Untuk jadual InnoDB, artikel ini terutamanya membincangkan kandungan berikut: (1) Kunci baris InnoDB adalah berdasarkan indeks Jika data tidak diakses melalui indeks, InnoDB akan Kunci meja akan digunakan. (2) Di bawah tahap pengasingan yang berbeza, mekanisme penguncian InnoDB dan strategi bacaan yang konsisten adalah berbeza.

Setelah memahami ciri kunci InnoDB, pengguna boleh mengurangkan konflik kunci dan kebuntuan melalui reka bentuk dan pelarasan SQL, termasuk:

• Cuba gunakan tahap pengasingan yang lebih rendah dengan teliti Indeks, dan cuba gunakan indeks untuk mengakses data untuk menjadikan penguncian lebih tepat, dengan itu mengurangkan kemungkinan konflik kunci;
• Pilih saiz transaksi yang munasabah, dan kebarangkalian konflik kunci untuk transaksi kecil akan menjadi lebih kecil; >
Apabila mengunci set rekod secara eksplisit, sebaiknya minta tahap kunci yang mencukupi sekaligus. Sebagai contoh, jika anda ingin mengubah suai data, sebaiknya gunakan kunci eksklusif secara langsung daripada memohon kunci kongsi dahulu dan kemudian minta kunci eksklusif apabila mengubah suai Ini boleh menyebabkan kebuntuan dengan mudah program yang berbeza mengakses sekumpulan jadual, cuba untuk Dipersetujui untuk mengakses setiap jadual dalam susunan yang sama Untuk jadual, baris dalam jadual harus diakses dalam susunan tetap sebanyak mungkin. Ini boleh mengurangkan peluang kebuntuan dengan ketara; paparkan kunci tambahan apabila membuat pertanyaan kecuali perlu Kunci
• Untuk beberapa transaksi tertentu, kunci meja boleh digunakan untuk meningkatkan kelajuan pemprosesan atau mengurangkan kemungkinan kebuntuan.
• [Cadangan berkaitan:
tutorial video mysql
• ]

Atas ialah kandungan terperinci Pembelajaran MySQL bercakap tentang situasi kunci dalam InnoDB. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!