Ketahui lebih lanjut tentang transaksi dan kunci dalam MySQL

Pangkalan data MySQL ialah sistem capaian berbilang pengguna, jadi apabila berbilang pengguna membaca dan mengemas kini data pada masa yang sama, data tidak akan dimusnahkan, jadi kunci tercipta teknologi. Apabila pengguna cuba mengubah suai rekod dalam pangkalan data, dia mesti memperoleh kunci dahulu Kemudian, semasa pengguna yang memegang kunci itu masih mengubah suai, pengguna lain tidak boleh mengubah suai rekod ini. [Cadangan berkaitan: tutorial video mysql]

Kunci dalam MySQL

Tetapi berbanding dengan pangkalan data lain, mekanisme penguncian MySQL adalah agak mudah enjin storan mempunyai mekanisme penguncian yang berbeza. Enjin storan MylSAM dan MEMORY menggunakan kunci peringkat meja, enjin storan BDB menggunakan kunci halaman, dan enjin storan InnoDB yang biasa digunakan menyokong kunci peringkat baris dan kunci peringkat jadual penguncian digunakan.

Ciri-ciri ketiga-tiga jenis kunci ini adalah seperti berikut:

• Kunci peringkat meja: overhed rendah, penguncian pantas, tiada jalan buntu, butiran penguncian yang besar dan kunci konflik mempunyai kebarangkalian tertinggi dan konkurensi terendah.

• Kunci peringkat baris: overhed tinggi, penguncian perlahan, kebuntuan akan berlaku, butiran penguncian adalah yang paling kecil, kebarangkalian konflik kunci adalah yang paling rendah, dan konkurensi adalah yang tertinggi.

• Kunci halaman: Overhed dan masa mengunci adalah antara kunci meja dan kunci baris, kebuntuan akan berlaku, butiran penguncian adalah antara kunci meja dan kunci baris, dan konkurensi adalah purata .

MyISAM

Kunci meja MyISAM

MySQL menyediakan untuk jadual Terdapat dua jenis kunci, iaitu:

• READ LOCK: Membenarkan pengguna membaca data daripada jadual sahaja.

• TULIS KUNCI: Membenarkan pengguna membaca dan menulis pada jadual.

Operasi baca MyISAM pada jadual tidak akan menyekat permintaan baca pengguna lain untuk jadual yang sama, tetapi akan menyekat permintaan tulis pada jadual yang sama akan menyekat The operasi baca dan tulis pengguna lain pada jadual yang sama, operasi baca dan tulis jadual MyISAM, dan operasi tulis adalah bersiri.

MyISAM akan menambah kunci baca secara automatik pada semua jadual yang digunakan sebelum melaksanakan pernyataan pertanyaan (PILIH), dan secara automatik akan menambah tulis pada jadual yang terlibat sebelum melaksanakan operasi kemas kini (KEMASKINI, PADAM, INSERT, dll.) Mengunci, proses ini tidak memerlukan campur tangan manual kami, jadi kami secara amnya tidak perlu menggunakan perintah LOCK TABLE untuk mengunci jadual MyISAM secara eksplisit, tetapi tiada masalah dengan memaparkan kunci.

Selain itu, apabila menggunakan LOCK TABLES untuk menambahkan kunci jadual secara eksplisit pada jadual, anda mesti mendapatkan semua kunci pada jadual yang terlibat pada masa yang sama, kerana selepas melaksanakan LOCK TABLES, anda hanya boleh mengakses jadual terkunci secara eksplisit dan tidak mengaksesnya. Jadual tidak dikunci, jika tidak, ralat akan berlaku Pada masa yang sama, jika kunci baca ditambah, hanya operasi pertanyaan boleh dilakukan dan operasi kemas kini tidak boleh dilakukan benar dalam kes penguncian automatik Ini betul-betul jadual MyISAM tidak akan menyebabkan kebuntuan.

Lihat contoh di bawah.

1. Cipta jadual

CREATE TABLE test_table (   
      Id INT NOT NULL AUTO_INCREMENT,   
      Name VARCHAR(50) NOT NULL,   
      Message VARCHAR(80) NOT NULL,  
      PRIMARY KEY (Id)  
);

2. Sesi 1 memperoleh kunci tulis

mysql> lock table  test_table write;
Query OK, 0 rows affected (0.01 sec)

3.

Kami tahu bahawa apabila sesi memegang kunci WRITE, semua sesi lain tidak boleh mengakses data jadual, jadi apabila sesi kedua melaksanakan pernyataan berikut, ia akan sentiasa dalam keadaan menunggu.

mysql> select * from test_table;

4. Membuka kunci Sesi 1

unlock table;

Sisipan serentak

Dalam MyISAM, operasi baca dan tulis adalah bersiri, tetapi ia boleh Mengikut tetapan concurrent_insert, biarkan MyISAM menyokong pertanyaan dan sisipan selari.

concurrent_insert mempunyai nilai berikut:

• 0: Fungsi sisipan serentak tidak dibenarkan.

• 1: Benarkan sisipan serentak untuk jadual tanpa lubang, dengan data baharu di hujung fail data (lalai).

• 2: Sisipan serentak pada penghujung fail data dibenarkan tanpa mengira sama ada terdapat lubang dalam jadual.

Lubang merujuk kepada baris di tengah jadual yang belum dipadamkan.

InnoDB

InnoDB berbeza daripada MyISAM Satu ialah ia menyokong transaksi, dan satu lagi ialah ia menggunakan kunci peringkat baris Terdapat banyak perbezaan antara kunci tahap dan kunci meja.

Ciri-ciri transaksi

• Atomicity

  Transaksi ialah unit operasi atom Semua pengubahsuaian pada data sama ada dilaksanakan, Atau tidak berbuat apa-apa.

• Ketekalan

  Data mesti kekal konsisten pada permulaan dan penyelesaian transaksi. Ini bermakna semua peraturan data yang berkaitan mesti digunakan pada pengubahsuaian transaksi untuk mengekalkan integriti data.

• Pengasingan

  Sistem pangkalan data memastikan transaksi tidak terjejas oleh operasi serentak luaran dan boleh dilaksanakan dalam persekitaran "bebas", yang bermaksud keadaan perantaraan semasa pemprosesan transaksi adalah Bahagian luar tidak kelihatan.

• Kegigihan

  Selepas transaksi selesai, pengubahsuaiannya kepada data adalah kekal dan boleh dikekalkan walaupun kegagalan sistem berlaku.

Masalah yang disebabkan oleh pemprosesan transaksi serentak

Berbanding dengan pemprosesan bersiri, walaupun penggunaan sumber dipertingkatkan, ia boleh menyokong lebih ramai pengguna, tetapi transaksi serentak pemprosesan juga akan membawa beberapa masalah, terutamanya termasuk situasi berikut.

Kemas kini hilang

由于每个事务都不知道其他事务的存在，就会发生丢失更新问题，也就是最后的更新覆盖了由其他事务所做的更新。

脏读

脏读又称无效数据的读出，当事务1将某一值修改后，然后事务2读取该值，后面事务1又因为一些原因撤销对该值的修改，这就导致了事务2所读取到的数据是无效的。

不可重复读

指的是一个事务在读取某些数据后，再次读取之前读过的数据，却发现读出的数据已经发生了改变。

幻读

当事务1按相同的查询条件重新读取以前查询过的数据时，却发现其他事务插入了满足这个条件的新数据。

事务隔离级别

上面说的"更新丢失"是应该完全避免的，但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁。

而脏读、不可重复读、幻读，都是数据库读一致性问题，必须由数据库提供事务隔离机制来解决。数据库实现事务隔离的方式，可分为以下两种，一种是在读取数据前加锁，阻止其他事务对数据进行修改，另一种不需要锁，通过MVCC或MCC来实现，这种技术叫做数据多版本并发控制，通过一定机制生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定级别的一致性读取。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上串行化进行。

InnoDB有四个事务隔离级别： READ UNCOMMITTED， READ COMMITTED， REPEATABLE READ，和 SERIALIZABLE。默认隔离级别是REPEATABLE READ。

隔离级别	脏读	不可重复性	幻读
读未提交	√	√	√
读已提交	×	√	√
可重复读取	×	×	√
可序列化（serializable）	×	×	×

查询/更改隔离级别

显示隔离级别
show global variables like '%isolation%';
select @@transaction_isolation;

设置隔离级别
set global transaction_isolation ='read-committed';
set session transaction isolation level read uncommitted;

READ UNCOMMITTED（读未提交）

在这个隔离级别，所有事务都可以看到其他未提交事务的执行结果。这种隔离级别在实际应用中很少使用，读取未提交的数据也称为脏读。

例子

启动两个会话，并设置隔离级别为READ UNCOMMITTED。

mysql> select * from user;
+-----------+---------+
| user_name | balance |
+-----------+---------+
| 张三      |     100 |
| 李四      |     100 |
| 王五      |      80 |
+-----------+---------+

时间	事务1	事务2
T1	begin;	begin;
T2	select * from user where user_name="张三"; 此时张三余额100
T3		select * from user where user_name="张三"; 此时张三余额100
T4	update user set balance =80 where user_name ="张三";
T4		select * from user where user_name="张三"; 此时张三余额80
T5	commit	commit

可以看到，在T4时刻，事务1没有提交，但是事务2可以看到被事务1锁更改的数据。

READ COMMITTED （读已提交）

这是大多数数据库系统的默认隔离级别，但不是MySQL的默认级别，他避免了脏读现象，因为在任何未提交的事务前，对任何其他事务都是不可见的，也就是其他事务看不到未提交的数据，允许不可重复读。

例子

将两个会话中隔离级别设置为读已提交
set session transaction isolation level read committed;

时间	事务1	事务2
T1	begin;	begin;
T2	select * from user where user_name="张三"; 此时张三余额100
T3		select * from user where user_name="张三"; 此时张三余额100
T4	update user set balance =80 where user_name ="张三";
T4		select * from user where user_name="张三"; 此时张三余额100
T5	commit
T5		select * from user where user_name="张三"; 此时张三余额80

可以看到，在T4时刻，事务1没有提交，但是事务2读取到的数据还是100,当事务1提交后，事务2才可以看到。

REPEATABLE READ （可重复读）

这是 MySQL 的默认事务隔离级别，它确保同一事务读取数据时，将看到相同的数据行，但是会出现幻读，当事务1按条件进行查询后，另一个事务在该范围内插入一个新数据，那么事务1再次读取时，就会读到这个新数据。InnoDB 和 Falcon 存储引擎通过 mvcc（多版本并发控制）机制解决了这个问题。

例子

设置两个会话隔离级别为可重复读
set session transaction isolation level repeatable read;

时间	事务1	事务2
T1	begin;	begin;
T2	update user set balance =80 where user_name ="张三";
T3	commit;
T4		select * from user where user_name="张三"; 张三余额为100

可以看到，在T3时刻，事务1已经提交更改，但是在T4时刻的事务2中，还是读取到了原来的数据，但是如果事务2在原来的基础上再减10元，那么最终余额是90还是70呢？,答案是70。.

mysql> update user set balance=balance-10 where user_name="张三";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user where user_name="张三";
+-----------+---------+
| user_name | balance |
+-----------+---------+
| 张三      |      70 |
+-----------+---------+
1 row in set (0.00 sec)

SERIALIZABLE （序列化）

他是最高的隔离级别，InnoDB将所有普通SELECT语句隐式转换为SELECT ... LOCK IN SHARE MODE，所有事务按照顺序依次执行，因此，脏读、不可重复读、幻读都不会出现。但是，由于事务是串行执行，所以效率会大大下降，

例子

设置隔离级别为序列化
set session transaction isolation level serializable;

时间	事务1	事务2
T1	begin;	begin;
T2	select * from user where user_name="张三";
T3		update user set balance =80 where user_name ="张三";

这一次，有趣的是，事务2在T3时刻更新被阻止了,原因是在serializable隔离级别下，MySQL隐式地将所有普通SELECT查询转换为SELECT FOR SHARE， 持有SELECT FOR SHARE锁的事务只允许其他事务对SELECT行进行处理，而不允许其他事务UPDATE或DELETE它们。

所以有了这个锁定机制，我们之前看到的不一致数据场景就不再可能了。

但是，这个锁具有超时时间，在等待一会后，如果其他事务在这段时间内没有提交或回滚释放锁，将抛出锁等待超时错误，如下所示：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

InnoDB行锁

InnoDB 的行级锁也分为共享锁和排他锁两种。

• 共享锁允许持有锁的事务读取行。

• 独占锁允许持有锁事务的更新或删除行。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁，这两种意向锁都是表锁。

• 意向共享锁 事务想要获得一张表中某几行的共享锁。
• 意向排他锁 事务想要获得一张表中某几行的排他锁。

InnoDB 行锁是通过锁定索引上的索引条目来实现的，因此，InnoDB 只有在通过索引条件检索到数据时才使用行级锁；否则，InnoDB 将使用表锁。

我们可以显示的加锁，但对于update、delete、insert语句，InnoDB会自动给涉及的数据集加排他锁，对于普通的 select 语句，InnoDB 不会加任何锁，下面是显示的加锁方式：

• 共享锁：SELECT FROM table_name WHERE … LOCK IN SHARE MODE
• 排他锁：SELECT * FROM table_name WHERE … FOR UPDATE

Next-Key锁

当我们使用范围条件而不是相等条件检索数据，并请求其共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于在条件范围内但并不存在的记录，叫做间隙(GAP), InnoDB也会对这个"间隙"加锁，这种锁机制就是所谓的Next-Key锁。

举例来说，假如user表中只有101条记录，其user_id的值分别是1.2. ..100. 101，当查找大于100的user_id时，使用下面SQL。

select.* from emp where user_id > 100 for update;

这就是一个范围条件的查询， InnoDB不仅会对user_id为101的记录加锁，也会对user_id大于101的"间隙"加锁，虽然这些记录并不存在。

InnoDB使用Next-Key锁的目的，一方面是为了防止幻读，另一方面， 是为了满足恢复和复制的需要。

更多编程相关知识，请访问：编程视频！！

Atas ialah kandungan terperinci Ketahui lebih lanjut tentang transaksi dan kunci dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!