MySQL InnoDB存储引擎锁机制实验
之前的文章提到MySQL的InnoDB存储引擎使用的是行级锁,并且默认的事务隔离级别为可重复读,而不同于Oracle默认的事务隔离级别提交
之前的文章提到MySQL的InnoDB存储引擎使用的是行级锁,并且默认的事务隔离级别为可重复读,而不同于Oracle默认的事务隔离级别提交读。那么MySQL的InnoDB存储引擎的锁机制的具体表现是怎样的呢?
相关阅读:MySQL数据库锁机制
实验如下:
首先建立一张测试使用的表:
CREATE TABLE`test_innodb_lock` (
`a` int(11) DEFAULT NULL,
`b` varchar(16) DEFAULT NULL,
KEY `test_innodb_lock_a_IDX` (`a`)
) ENGINE=InnoDB
然后再往这张表里插入一些数据,以备使用,最终表数据如下:
+------+------+
| a | b |
+------+------+
| 1 | a |
| 1 | x |
| 1 | y |
| 2 | b |
| 2 | w |
| 2 | z |
| 3 | c |
| 4 | d |
| 5 | e |
| 8 | ff |
| 8 | f |
| 10 | g |
+------+------+
首先我们来看看行级锁的情况:
实验一:
打开两个MySQL客户端,
在客户端1执行:
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
修改客户端1的事务提交方式为手动提交;
在客户端2执行:
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
同样修改客户端2的事务提交方式为手动提交;
在客户端1执行:
mysql> update test_innodb_lock set b ='xxx' where a = 1 and b = 'y';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
同时使用索引字段a和非索引字段b更新一条数据;
在客户端2执行:
mysql> update test_innodb_lock set b ='xxx' where a=1 and b = 'x';
同时使用索引字段a(并且索引值同客户端1的值相同)和非索引字段更新另外一条数据;
结果发现客户端2的update语句被阻塞,,需要客户端1提交或回滚才能继续执行。说明,虽然两个事务最终更新的数据不是同一条数据,但然后可能被锁定,这是因为两条SQL语句都使用了相同的索引值(a=1),行级锁上升为页级锁。
实验二:
在客户端1执行:
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
回滚实验一的操作;
在客户端2执行:
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
回滚实验一的操作;
在客户端1执行:
mysql> update test_innodb_lock set b ='xxx' where a = 1 and b = 'a';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
同时使用索引字段a和非索引字段b更新一条数据;
在客户端2执行:
mysql> update test_innodb_lock set b ='xxx' where a=2 and b = 'b';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
同时使用索引字段a(索引值不同于客户端1SQL语句的索引值)和非索引字段b更新一条数据;
更新顺利进行,执行并没有被阻塞;
说明,同是根据索引和非索引字段进行更新数据,当两个事务的SQL语句中的索引条件值不一样时,更新仍然能够顺利进行。

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Full table scanning may be faster in MySQL than using indexes. Specific cases include: 1) the data volume is small; 2) when the query returns a large amount of data; 3) when the index column is not highly selective; 4) when the complex query. By analyzing query plans, optimizing indexes, avoiding over-index and regularly maintaining tables, you can make the best choices in practical applications.

InnoDB's full-text search capabilities are very powerful, which can significantly improve database query efficiency and ability to process large amounts of text data. 1) InnoDB implements full-text search through inverted indexing, supporting basic and advanced search queries. 2) Use MATCH and AGAINST keywords to search, support Boolean mode and phrase search. 3) Optimization methods include using word segmentation technology, periodic rebuilding of indexes and adjusting cache size to improve performance and accuracy.

Yes, MySQL can be installed on Windows 7, and although Microsoft has stopped supporting Windows 7, MySQL is still compatible with it. However, the following points should be noted during the installation process: Download the MySQL installer for Windows. Select the appropriate version of MySQL (community or enterprise). Select the appropriate installation directory and character set during the installation process. Set the root user password and keep it properly. Connect to the database for testing. Note the compatibility and security issues on Windows 7, and it is recommended to upgrade to a supported operating system.

MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.

The difference between clustered index and non-clustered index is: 1. Clustered index stores data rows in the index structure, which is suitable for querying by primary key and range. 2. The non-clustered index stores index key values and pointers to data rows, and is suitable for non-primary key column queries.

In MySQL database, the relationship between the user and the database is defined by permissions and tables. The user has a username and password to access the database. Permissions are granted through the GRANT command, while the table is created by the CREATE TABLE command. To establish a relationship between a user and a database, you need to create a database, create a user, and then grant permissions.

MySQL and MariaDB can coexist, but need to be configured with caution. The key is to allocate different port numbers and data directories to each database, and adjust parameters such as memory allocation and cache size. Connection pooling, application configuration, and version differences also need to be considered and need to be carefully tested and planned to avoid pitfalls. Running two databases simultaneously can cause performance problems in situations where resources are limited.

MySQL supports four index types: B-Tree, Hash, Full-text, and Spatial. 1.B-Tree index is suitable for equal value search, range query and sorting. 2. Hash index is suitable for equal value searches, but does not support range query and sorting. 3. Full-text index is used for full-text search and is suitable for processing large amounts of text data. 4. Spatial index is used for geospatial data query and is suitable for GIS applications.
