Analisis ringkas tahap pengasingan transaksi dalam MySQL dan perbincangan tentang prinsip pelaksanaannya

Artikel ini akan membawa anda memahami urus niaga dalam MySQL dan bercakap tentang prinsip pelaksanaan pengasingan transaksi. Saya harap ia dapat membantu anda!

Bercakap tentang urus niaga pangkalan data, banyak pengetahuan berkaitan urus niaga mesti mudah timbul dalam fikiran semua orang, seperti ciri ACID urus niaga, tahap pengasingan dan masalah yang diselesaikan ( bacaan kotor , bacaan tidak boleh berulang, bacaan hantu), dsb., tetapi sebilangan kecil orang mungkin benar-benar mengetahui cara ciri urus niaga ini dilaksanakan dan sebab terdapat empat tahap pengasingan.

Hari ini kita akan bercakap tentang prinsip pelaksanaan pengasingan transaksi dalam MySQL Kami akan terus menerbitkan artikel untuk menganalisis prinsip pelaksanaan ciri lain pada masa hadapan.

Sudah tentu MySQL adalah luas dan mendalam, dan peninggalan dalam artikel tidak dapat dielakkan.

Penjelasan

Logik pelaksanaan transaksi MySQL terletak pada lapisan enjin, dan bukan semua enjin menyokong transaksi.

Definisi

Pengasingan merujuk kepada fakta bahawa selepas transaksi berbeza diserahkan dan dilaksanakan satu demi satu, kesan terakhir adalah bersiri Maksudnya, untuk transaksi, ia adalah Semasa pelaksanaan proses, perubahan data yang dilihat seharusnya hanya disebabkan oleh operasi anda sendiri, dan tidak sepatutnya ada perubahan data yang disebabkan oleh transaksi lain.

Pengasingan menyelesaikan masalah urus niaga serentak.

Tahap pengasingan SQL standard

Cara paling mudah untuk mencapai pengasingan adalah dengan melaksanakan setiap transaksi secara bersiri Jika transaksi sebelumnya belum dilaksanakan lagi, transaksi berikutnya akan menunggu. Walau bagaimanapun, kaedah pelaksanaan ini jelas tidak begitu cekap secara serentak dan tidak sesuai untuk digunakan dalam persekitaran sebenar.

Untuk menyelesaikan masalah di atas dan mencapai tahap kawalan konkurensi yang berbeza, pembuat standard SQL telah mencadangkan tahap pengasingan yang berbeza: baca tanpa komitmen (baca tanpa komitmen), baca komited (baca komited), baca berulang (boleh berulang) baca ), baca bersiri (boleh bersiri). Tahap pengasingan yang paling maju ialah bacaan bersiri, dan dalam tahap pengasingan lain, memandangkan urus niaga dilaksanakan serentak, beberapa masalah lebih kurang dibenarkan. Lihat jadual matriks berikut:

tr>

隔离级别（ :允许出现，-:不允许出现）	脏读	不可重复读	幻读
未提交读
提交读	-
可重复读	-	-
序列化读	-	-	-

Tahap pengasingan (: dibenarkan muncul, -: tidak dibenarkan muncul)

Bacaan kotor

Bacaan tidak boleh berulang

Baca hantu

Bacaan tanpa komitmen                                                                                                                                      ">                                                  ">                                                                                                                                                                                                                                                                                                                                                             left">-

Bacaan berulang

-

-

Bacaan bersiri

-

-

-

Perhatikan bahawa enjin InnoDB MySQL menyelesaikan masalah bacaan hantu melalui kunci celah pada tahap bacaan boleh berulang, dan menyelesaikan masalah bacaan tidak boleh berulang melalui MVCC Lihat analisis di bawah untuk mendapatkan butiran.

Prinsip pelaksanaan

Prinsip pelaksanaan tahap pengasingan transaksi SQL standard

Masalah yang kami hadapi di atas sebenarnya ialah masalah kawalan di bawah urus niaga serentak urus niaga serentak ialah kawalan serentak pesimis (iaitu, kunci dalam pangkalan data). Pelaksanaan tahap pengasingan transaksi SQL standard bergantung pada kunci Mari kita lihat cara ia dilaksanakan:

Tahap pengasingan transaksi

Bacaan belum dibuka (RU)

urus niaga tidak dikunci dalam data yang sedang dibaca

Apabila transaksi mengemas kini data tertentu (iaitu, saat kemas kini berlaku), ia mesti menambah

事务隔离级别	实现方式
未提交读（RU）	事务对当前被读取的数据不加锁； 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级共享锁，直到事务结束才释放。
提交读（RC）	事务对当前被读取的数据加行级共享锁（当读到时才加锁），一旦读完该行，立即释放该行级共享锁； 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。
可重复读（RR）	事务在读取某数据的瞬间（就是开始读取的瞬间），必须先对其加行级共享锁，直到事务结束才释放； 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。
序列化读（S）	事务在读取数据时，必须先对其加表级共享锁 ，直到事务结束才释放； 事务在更新数据时，必须先对其加表级排他锁 ，直到事务结束才释放。

kunci kongsi peringkat baris padanya dahulu dan tidak melepaskannya sehingga penghujung transaksi.

Komit baca (RC)

Transaksi menambah kunci kongsi peringkat baris pada data yang sedang dibaca (kunci hanya ditambah apabila ia dibaca) ), sebaik sahaja baris dibaca, kunci kongsi peringkat baris dilepaskan serta-merta;

Apabila transaksi mengemas kini data tertentu (iaitu, saat kemas kini berlaku), ia mesti terlebih dahulu menambahkan padanya Kunci eksklusif peringkat baris tidak dikeluarkan sehingga akhir transaksi.

Bacaan Boleh Berulang (RR)

Apabila transaksi membaca data tertentu (iaitu, saat ia mula membaca), ia mesti ditambah dahulu Kunci kongsi peringkat baris tidak dikeluarkan sehingga akhir transaksi;

Apabila transaksi mengemas kini data tertentu (iaitu, pada saat kemas kini berlaku), ia mesti menambah eksklusif peringkat baris Kunci tidak dilepaskan sehingga tamat transaksi.

Bacaan bersiri (S)

Apabila transaksi membaca data, ia mesti menambah kunci kongsi peringkat jadual terlebih dahulu padanya . Ia tidak akan dikeluarkan sehingga akhir urus niaga;

Apabila transaksi mengemas kini data, ia mesti menambahkan kunci eksklusif peringkat jadual padanya dan ia tidak akan akan dikeluarkan sehingga akhir urus niaga.

Seperti yang anda lihat, apabila hanya menggunakan kunci untuk melaksanakan kawalan tahap pengasingan, penguncian dan buka kunci yang kerap diperlukan, dan konflik membaca dan menulis cenderung berlaku (contohnya, pada peringkat RC, transaksi A mengemas kini baris data 1, Urus niaga B mesti menunggu transaksi A melakukan dan melepaskan kunci sebelum transaksi A komited untuk membaca baris data 1).

Untuk menyelesaikan masalah konflik baca dan tulis tanpa mengunci, MySQL memperkenalkan mekanisme MVCC Untuk butiran, sila lihat artikel analisis saya sebelum ini: Memahami kunci optimistik, kunci pesimis dan MVCC dalam pangkalan data dalam satu artikel. .

Prinsip pelaksanaan tahap pengasingan transaksi InnoDB

Sebelum meneruskan analisis, kami mempunyai beberapa konsep yang perlu kami fahami terlebih dahulu:

1 Bacaan tidak mengunci

Bacaan terkunci : Dalam transaksi, kunci bacaan secara aktif, seperti PILIH ... KUNCI MOD KONGSI dan PILIH ... UNTUK KEMASKINI. Kunci kongsi baris dan kunci eksklusif baris ditambah masing-masing. Klasifikasi kunci boleh didapati dalam artikel analisis saya sebelum ini: Klasifikasi kunci MySQL yang perlu anda ketahui).

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

Bacaan tidak berkunci yang konsisten: InnoDB Gunakan MVCC untuk menyediakan petikan pangkalan data pada masa tertentu kepada pertanyaan transaksi. Pertanyaan akan melihat perubahan yang dibuat oleh urus niaga yang dilakukan sebelum masa itu, tetapi bukan perubahan yang dibuat oleh urus niaga kemudian atau yang tidak komited (selain daripada urus niaga ini). Maksudnya, selepas memulakan urus niaga, data yang dilihat oleh urus niaga adalah data pada masa transaksi dimulakan, dan pengubahsuaian seterusnya bagi urus niaga lain tidak akan kelihatan dalam transaksi ini.

Bacaan konsisten ialah mod lalai untuk InnoDB memproses pernyataan SELECT pada tahap pengasingan RC dan RR. Bacaan tanpa kunci yang konsisten tidak menetapkan sebarang kunci pada jadual yang mereka akses, jadi semasa melakukan bacaan tanpa kunci yang konsisten pada jadual, urus niaga lain boleh membaca atau mengubah suainya secara serentak.

https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

2. Bacaan semasa dan bacaan syot kilat

Bacaan semasa

membaca versi terkini, seperti KEMASKINI, PADAM, MASUKKAN, PILIH... KUNCI DALAM MOD KONGSI, PILIH... UNTUK KEMASKINIOperasi ini adalah semua bacaan semasa. Mengapa ia dipanggil bacaan semasa? Iaitu, ia membaca versi terkini rekod Apabila membaca, ia juga mesti memastikan bahawa transaksi serentak lain tidak boleh mengubah suai rekod semasa, dan rekod bacaan akan dikunci.

Dibaca syot kilat

membaca versi syot kilat, iaitu versi sejarah Operasi SELECT tidak berkunci ialah syot kilat dibaca, iaitu, Bacaan tidak menyekat tanpa mengunci; Premis bacaan syot kilat ialah tahap pengasingan bukan tahap bacaan tanpa komitmen dan bacaan bersiri, kerana bacaan tanpa komitmen sentiasa membaca baris data terkini, bukannya baris data yang mematuhi transaksi semasa versi , dan bacaan bersiri akan mengunci jadual.

3. Penguncian tersirat dan penguncian eksplisit

Pengunci tersirat

InnoDB menggunakan protokol kunci dua peringkat (tanpa paparan aktif mengunci):

• Anda boleh melakukan penguncian pada bila-bila masa, dan InnoDB akan mengunci secara automatik apabila diperlukan berdasarkan tahap pengasingan; dilaksanakan, dan semua kunci akan dilepaskan pada masa yang sama.

Penguncian eksplisit

InnoDB juga menyokong penguncian eksplisit melalui pernyataan tertentu (lapisan enjin storan)

• select ... lock in share mode //共享锁
  select ... for update //排他锁

Kunci paparan pada lapisan Pelayan MySQL:

• lock table
  unlock table

Setelah memahami konsep di atas, mari kita lihat bagaimana transaksi InnoDB dilaksanakan (Perkara berikut bacaan semua merujuk kepada pilihan yang tidak dikunci secara aktif)

事务隔离级别	实现方式
未提交读（RU）	事务对当前被读取的数据不加锁，都是当前读； 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级共享锁，直到事务结束才释放。
提交读（RC）	事务对当前被读取的数据不加锁，且是快照读； 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁（Record），直到事务结束才释放。
可重复读（RR）	事务对当前被读取的数据不加锁，且是快照读； 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁（Record，GAP，Next-Key），直到事务结束才释放。 通过间隙锁，在这个级别MySQL就解决了幻读的问题 通过快照，在这个级别MySQL就解决了不可重复读的问题
序列化读（S）	事务在读取数据时，必须先对其加表级共享锁 ，直到事务结束才释放，都是当前读； 事务在更新数据时，必须先对其加表级排他锁 ，直到事务结束才释放。

可以看到，InnoDB通过MVCC很好的解决了读写冲突的问题，而且提前一个级别就解决了标准级别下会出现的幻读问题，大大提升了数据库的并发能力。

一些常见误区

幻读到底包不包括了delete的情况?

不可重复读：前后多次读取一行，数据内容不一致，针对其他事务的update和delete操作。为了解决这个问题，使用行共享锁，锁定到事务结束（也就是RR级别，当然MySQL使用MVCC在RC级别就解决了这个问题）

幻读：当同一个查询在不同时间生成不同的行集合时就是出现了幻读，针对的是其他事务的insert操作，为了解决这个问题，锁定整个表到事务结束（也就是S级别，当然MySQL使用间隙锁在RR级别就解决了这个问题）

网上很多文章提到幻读和提交读的时候，有的说幻读包括了delete的情况，有的说delete应该属于提交读的问题，那到底真相如何呢？我们实际来看下MySQL的官方文档（如下）

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT) is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html

可以看到，幻读针对的是结果集前后发生变化，所以看起来delete的情况应该归为幻读，但是我们实际分析下上面列出的标准SQL在RR级别的实现原理就知道，标准SQL的RR级别是会对查到的数据行加行共享锁，所以这时候其他事务想删除这些数据行其实是做不到的，所以在RR下，不会出现因delete而出现幻读现象，也就是幻读不包含delete的情况。

MVCC能解决了幻读问题?

网上很多文章会说MVCC或者MVCC+间隙锁解决了幻读问题，实际上MVCC并不能解决幻读问题。如以下的例子：

begin;

#假设users表为空，下面查出来的数据为空

select * from users; #没有加锁

#此时另一个事务提交了，且插入了一条id=1的数据

select * from users; #读快照，查出来的数据为空

update users set name='mysql' where id=1;#update是当前读，所以更新成功，并生成一个更新的快照

select * from users; #读快照，查出来id为1的一条记录，因为MVCC可以查到当前事务生成的快照

commit;

可以看到前后查出来的数据行不一致，发生了幻读。所以说只有MVCC是不能解决幻读问题的，解决幻读问题靠的是间隙锁。如下：

begin;

#假设users表为空，下面查出来的数据为空

select * from users lock in share mode; #加上共享锁

#此时另一个事务B想提交且插入了一条id=1的数据，由于有间隙锁，所以要等待

select * from users; #读快照，查出来的数据为空

update users set name='mysql' where id=1;#update是当前读，由于不存在数据，不进行更新

select * from users; #读快照，查出来的数据为空

commit;

#事务B提交成功并插入数据

注意，RR级别下想解决幻读问题，需要我们显式加锁，不然查询的时候还是不会加锁的。

原文地址：https://segmentfault.com/a/1190000025156465

作者: X先生

【相关推荐：mysql视频教程】

Atas ialah kandungan terperinci Analisis ringkas tahap pengasingan transaksi dalam MySQL dan perbincangan tentang prinsip pelaksanaannya. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!