Heim Datenbank MySQL-Tutorial MySQL的伪行级锁

MySQL的伪行级锁

Jun 07, 2016 pm 05:19 PM
数据库

接触mysql之前一直以为mysql的innodb引擎所支持的行级锁和oracle,postgresql是一样的,是对数据行上加锁。但其实是不一样的,理

接触mysql之前一直以为mysql的innodb引擎所支持的行级锁和Oracle,postgresql是一样的,是对数据行上加锁。但其实是不一样的,理解不一样,对mysql的锁机制就容易产生误解。innodb的行级锁实际上是基于索引项来锁定的。以下是测试机上的验证测试过程 

一.数据准备

mysql> use test; Database changed mysql> show create table t_kenyon \G *************************** 1. row *************************** Table: t_kenyon Create Table: CREATE TABLE `t_kenyon` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%autocommit%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> show variables like '%innodb_lock%'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | +--------------------------------+-------+ 2 rows in set (0.00 sec) mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql> select * from t_kenyon; +------+ | id | +------+ | 1 | | 123 | | 789 | | 345 | | 78 | | 78 | +------+ 6 rows in set (0.00 sec)以上是测试表t_kenyon,设置提交方式为手动提交.

二.过程(开启两个session,分别设置autocommit=off)

1.session 1 update mysql> update t_kenyon set id = 999 where id = 1; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t_kenyon; +------+ | id | +------+ | 999 | | 123 | | 789 | | 345 | | 78 | | 78 | +------+ 6 rows in set (0.00 sec)2.session 2 update mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> select * from t_kenyon; +------+ | id | +------+ | 1 | | 123 | | 789 | | 345 | | 78 | | 78 | +------+ 6 rows in set (0.00 sec) mysql> update t_kenyon set id = 88888 where id = 345;第二个session更新的值是345,但是也一直被阻塞,直到session1被rollback或者commit,如果session1未做回滚或者提交,session2中的该阻塞在超出mysql的锁时间限制时自动回滚,该参数为innodb_lock_wait_timeout,默认值50秒 现象如下 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

加索引后的测试 
3.session 1 update

mysql> create index ind_kenyon on t_kenyon(id); Query OK, 0 rows affected (28.58 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> update t_kenyon set id = 999 where id = 1; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t_kenyon; +------+ | id | +------+ | 78 | | 78 | | 123 | | 345 | | 789 | | 999 | +------+ 6 rows in set (0.00 sec)4.session 2 update mysql> select * from t_kenyon; +------+ | id | +------+ | 1 | | 78 | | 78 | | 123 | | 345 | | 789 | +------+ 6 rows in set (0.00 sec) mysql> update t_kenyon set id = 7777 where id = 345; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t_kenyon; +------+ | id | +------+ | 1 | | 78 | | 78 | | 123 | | 789 | | 7777 | +------+ 6 rows in set (0.00 sec)执行计划 mysql> explain select * from t_kenyon where id = 345 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_kenyon type: ref possible_keys: ind_kenyon key: ind_kenyon key_len: 5 ref: const rows: 1 Extra: Using where; Using index 1 row in set (0.00 sec)可以看到加了索引后,不同的数据更新并没有被阻塞,实现了真正意义上行锁
三.行级锁的扩展限制
1.相同索引阻塞
mysql> select * from t_kenyon; +------+---------+ | id | name | +------+---------+ | 1 | kenyon | | 123 | francs | | 789 | lighten | | 345 | mood | | 78 | opp | | 78 | opp | | 789 | james | +------+---------+ 7 rows in set (0.00 sec) mysql> explain select * from t_kenyon where id =1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_kenyon type: ref possible_keys: ind_kenyon key: ind_kenyon key_len: 5 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec) mysql> update t_kenyon set name = 'john' where id = 789 and name = 'james'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 02.在另外的会话中同样对id=789的另一条数据进行更新 mysql> update t_kenyon set name = 'yagobu' where id = 789 and name ='lighten'; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> update t_kenyon set name = 'yagobu' where id = 789 and name ='xxxxx'; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction这时我们可以看到即使有索引,不同数据的更新也被阻塞了,哪怕没有检索到其他数据,只要用到了同一个索引键值都会被阻塞,如上述name='xxxx'中是没有的,也被阻塞了。 所以可以引申开来,只要是在一个session中>或者
3.不同事务对表的不同索引锁定不同行
a.session 1 mysql> select * from t_kenyon; +------+------------+ | id | name | +------+------------+ | 1 | kenyon | | 123 | francs | | 345 | mood | | 78 | opp | | 78 | opp | | 789 | lighten | | 789 | james | | 899 | jiangkaish | | 902 | song | | 907 | hu | | 997 | wenjiab | +------+------------+ 11 rows in set (0.00 sec) mysql> update t_kenyon set name = 'kenyon_god' where id = 789; Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 02.session 2更新另一个 mysql> update t_kenyon set name = 'koko' where name = 'hu';3.在session 1中查看进程 mysql> show processlist; +----+------+-----------+------+---------+------+----------+-----------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------+-----------------------------------------------------+ | 8 | root | localhost | test | Query | 0 | NULL | show processlist | | 9 | root | localhost | test | Query | 8 | Updating | update t_kenyon set name = 'koko' where name = 'hu' | | 10 | root | localhost | NULL | Sleep | 113 | | NULL | +----+------+-----------+------+---------+------+----------+-----------------------------------------------------+ 3 rows in set (0.00 sec)可以看到被锁住了。

4.对name项加索引达到目的
a.会话1 mysql> alter table t_kenyon add index ind(name); Query OK, 0 rows affected (0.33 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> update t_kenyon set name = 'kenyon_god' where id = 789; Query OK, 2 rows affected (0.02 sec) Rows matched: 2 Changed: 2 Warnings: 0b.会话2 mysql> update t_kenyon set name = 'koko' where name = 'hu'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

至此对表的不同行数据也达到了更新的目的。

四.总结
MYSQL的innodb引擎在查询过程中如果有基于索引扫描,可以实现行级锁定,但是该行级锁有相当的限制,查询未使用索引或表中未建索引时会触发表锁,数据量大、查询效果慢的情况下该现象将被放大,严重会导致DB奔溃。

linux

Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn

Heiße KI -Werkzeuge

Undresser.AI Undress

Undresser.AI Undress

KI-gestützte App zum Erstellen realistischer Aktfotos

AI Clothes Remover

AI Clothes Remover

Online-KI-Tool zum Entfernen von Kleidung aus Fotos.

Undress AI Tool

Undress AI Tool

Ausziehbilder kostenlos

Clothoff.io

Clothoff.io

KI-Kleiderentferner

AI Hentai Generator

AI Hentai Generator

Erstellen Sie kostenlos Ai Hentai.

Heißer Artikel

R.E.P.O. Energiekristalle erklärten und was sie tun (gelber Kristall)
2 Wochen vor By 尊渡假赌尊渡假赌尊渡假赌
Repo: Wie man Teamkollegen wiederbelebt
4 Wochen vor By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Abenteuer: Wie man riesige Samen bekommt
4 Wochen vor By 尊渡假赌尊渡假赌尊渡假赌

Heiße Werkzeuge

Notepad++7.3.1

Notepad++7.3.1

Einfach zu bedienender und kostenloser Code-Editor

SublimeText3 chinesische Version

SublimeText3 chinesische Version

Chinesische Version, sehr einfach zu bedienen

Senden Sie Studio 13.0.1

Senden Sie Studio 13.0.1

Leistungsstarke integrierte PHP-Entwicklungsumgebung

Dreamweaver CS6

Dreamweaver CS6

Visuelle Webentwicklungstools

SublimeText3 Mac-Version

SublimeText3 Mac-Version

Codebearbeitungssoftware auf Gottesniveau (SublimeText3)

Wie implementiert die Go-Sprache die Hinzufügungs-, Lösch-, Änderungs- und Abfragevorgänge der Datenbank? Wie implementiert die Go-Sprache die Hinzufügungs-, Lösch-, Änderungs- und Abfragevorgänge der Datenbank? Mar 27, 2024 pm 09:39 PM

Die Go-Sprache ist eine effiziente, prägnante und leicht zu erlernende Programmiersprache. Sie wird von Entwicklern aufgrund ihrer Vorteile bei der gleichzeitigen Programmierung und Netzwerkprogrammierung bevorzugt. In der tatsächlichen Entwicklung sind Datenbankoperationen ein unverzichtbarer Bestandteil. In diesem Artikel wird erläutert, wie die Go-Sprache zum Implementieren von Datenbank-Hinzufügungs-, Lösch-, Änderungs- und Abfrageoperationen verwendet wird. In der Go-Sprache verwenden wir normalerweise Bibliotheken von Drittanbietern, um Datenbanken zu betreiben, z. B. häufig verwendete SQL-Pakete, Gorm usw. Hier nehmen wir das SQL-Paket als Beispiel, um vorzustellen, wie die Hinzufügungs-, Lösch-, Änderungs- und Abfragevorgänge der Datenbank implementiert werden. Angenommen, wir verwenden eine MySQL-Datenbank.

Ausführliches Tutorial zum Herstellen einer Datenbankverbindung mit MySQLi in PHP Ausführliches Tutorial zum Herstellen einer Datenbankverbindung mit MySQLi in PHP Jun 04, 2024 pm 01:42 PM

So verwenden Sie MySQLi zum Herstellen einer Datenbankverbindung in PHP: MySQLi-Erweiterung einbinden (require_once) Verbindungsfunktion erstellen (functionconnect_to_db) Verbindungsfunktion aufrufen ($conn=connect_to_db()) Abfrage ausführen ($result=$conn->query()) Schließen Verbindung ( $conn->close())

Wie implementiert Hibernate polymorphe Zuordnung? Wie implementiert Hibernate polymorphe Zuordnung? Apr 17, 2024 pm 12:09 PM

Die polymorphe Hibernate-Zuordnung kann geerbte Klassen der Datenbank zuordnen und bietet die folgenden Zuordnungstypen: Joined-Subclass: Erstellen Sie eine separate Tabelle für die Unterklasse, einschließlich aller Spalten der übergeordneten Klasse. Tabelle pro Klasse: Erstellen Sie eine separate Tabelle für Unterklassen, die nur unterklassenspezifische Spalten enthält. Union-Unterklasse: ähnelt der verbundenen Unterklasse, aber die Tabelle der übergeordneten Klasse vereint alle Spalten der Unterklasse.

iOS 18 fügt eine neue Albumfunktion „Wiederhergestellt' hinzu, um verlorene oder beschädigte Fotos wiederherzustellen iOS 18 fügt eine neue Albumfunktion „Wiederhergestellt' hinzu, um verlorene oder beschädigte Fotos wiederherzustellen Jul 18, 2024 am 05:48 AM

Apples neueste Versionen der iOS18-, iPadOS18- und macOS Sequoia-Systeme haben der Fotoanwendung eine wichtige Funktion hinzugefügt, die Benutzern dabei helfen soll, aus verschiedenen Gründen verlorene oder beschädigte Fotos und Videos einfach wiederherzustellen. Mit der neuen Funktion wird im Abschnitt „Extras“ der Fotos-App ein Album mit dem Namen „Wiederhergestellt“ eingeführt, das automatisch angezeigt wird, wenn ein Benutzer Bilder oder Videos auf seinem Gerät hat, die nicht Teil seiner Fotobibliothek sind. Das Aufkommen des Albums „Wiederhergestellt“ bietet eine Lösung für Fotos und Videos, die aufgrund einer Datenbankbeschädigung verloren gehen, die Kameraanwendung nicht korrekt in der Fotobibliothek speichert oder eine Drittanbieteranwendung die Fotobibliothek verwaltet. Benutzer benötigen nur wenige einfache Schritte

Eine ausführliche Analyse, wie HTML die Datenbank liest Eine ausführliche Analyse, wie HTML die Datenbank liest Apr 09, 2024 pm 12:36 PM

HTML kann die Datenbank nicht direkt lesen, dies kann jedoch über JavaScript und AJAX erreicht werden. Zu den Schritten gehören das Herstellen einer Datenbankverbindung, das Senden einer Abfrage, das Verarbeiten der Antwort und das Aktualisieren der Seite. Dieser Artikel bietet ein praktisches Beispiel für die Verwendung von JavaScript, AJAX und PHP zum Lesen von Daten aus einer MySQL-Datenbank und zeigt, wie Abfrageergebnisse dynamisch auf einer HTML-Seite angezeigt werden. In diesem Beispiel wird XMLHttpRequest verwendet, um eine Datenbankverbindung herzustellen, eine Abfrage zu senden und die Antwort zu verarbeiten. Dadurch werden Daten in Seitenelemente gefüllt und die Funktion des HTML-Lesens der Datenbank realisiert.

Analyse der Grundprinzipien des MySQL-Datenbankverwaltungssystems Analyse der Grundprinzipien des MySQL-Datenbankverwaltungssystems Mar 25, 2024 pm 12:42 PM

Analyse der Grundprinzipien des MySQL-Datenbankverwaltungssystems MySQL ist ein häufig verwendetes relationales Datenbankverwaltungssystem, das eine strukturierte Abfragesprache (SQL) zur Datenspeicherung und -verwaltung verwendet. In diesem Artikel werden die Grundprinzipien des MySQL-Datenbankverwaltungssystems vorgestellt, einschließlich Datenbankerstellung, Datentabellenentwurf, Datenhinzufügung, -löschung, -änderung und anderer Vorgänge, und spezifische Codebeispiele bereitgestellt. 1. Datenbankerstellung In MySQL müssen Sie zunächst eine Datenbankinstanz zum Speichern von Daten erstellen. Der folgende Code kann eine Datei mit dem Namen „my

Tipps und Praktiken zum Umgang mit verstümmelten chinesischen Zeichen in Datenbanken mit PHP Tipps und Praktiken zum Umgang mit verstümmelten chinesischen Zeichen in Datenbanken mit PHP Mar 27, 2024 pm 05:21 PM

PHP ist eine Back-End-Programmiersprache, die in der Website-Entwicklung weit verbreitet ist. Sie verfügt über leistungsstarke Datenbankbetriebsfunktionen und wird häufig zur Interaktion mit Datenbanken wie MySQL verwendet. Aufgrund der Komplexität der Kodierung chinesischer Zeichen treten jedoch häufig Probleme beim Umgang mit verstümmelten chinesischen Zeichen in der Datenbank auf. In diesem Artikel werden die Fähigkeiten und Praktiken von PHP beim Umgang mit chinesischen verstümmelten Zeichen in Datenbanken vorgestellt, einschließlich häufiger Ursachen für verstümmelte Zeichen, Lösungen und spezifischer Codebeispiele. Häufige Gründe für verstümmelte Zeichen sind falsche Einstellungen für den Datenbank-Zeichensatz: Beim Erstellen der Datenbank muss der richtige Zeichensatz ausgewählt werden, z. B. utf8 oder u

Wie lässt sich Go WebSocket in Datenbanken integrieren? Wie lässt sich Go WebSocket in Datenbanken integrieren? Jun 05, 2024 pm 03:18 PM

So integrieren Sie GoWebSocket in eine Datenbank: Richten Sie eine Datenbankverbindung ein: Verwenden Sie das Datenbank-/SQL-Paket, um eine Verbindung zur Datenbank herzustellen. Speichern Sie WebSocket-Nachrichten in der Datenbank: Verwenden Sie die INSERT-Anweisung, um die Nachricht in die Datenbank einzufügen. WebSocket-Nachrichten aus der Datenbank abrufen: Verwenden Sie die SELECT-Anweisung, um Nachrichten aus der Datenbank abzurufen.

See all articles