Oracle的事务指的是需要分配回滚段的SQL语句,也就是说select并不是oracle事务的一部分.比如运行一个查询,然后在另外一个会话查询
MySQL在5.5.3版本引入了metadata lock
他的本意是解决之前版本事务隔离特性的几个bug,但是引入的问题也不小.
先说说MySQL的事务吧.
Oracle的事务指的是需要分配回滚段的SQL语句,也就是说select并不是oracle事务的一部分.
比如运行一个查询,然后在另外一个会话查询v$transaction,并不会有任何相关的信息.直到事务中出现insert,update,delete。
而innodb的事务包括select查询.
无论事务隔离级别是可重复读,还是读提交,只要有查询,事务就开始了
下图证明了在5.6.15,设置了autocommit=0之后,运行一个查询就可以开启一个事务.
第一个会话运行查询.
第二个会话,运行 show engine innodb status\G 查看事务情况
可以看到id为1的线程,已经开始了一个事务.
为什么Oracle的事务仅包括insert,update和delete的语句,而innodb的事务包括所有的语句呢?
我觉得这个和厂商支持的隔离级别有很大的关系.
众所周知,Oracle仅仅支持读提交和串行化两种事务隔离级别,而读提交是绝大多数数据库的选择.
读提交意味着可以出现幻读和不可重复读,那么从实现原理的角度,Oracle可以在语句(Statement级别)开始的时候,记录SCN然后应用MVCC查询.每个查询只需要记录自己开始的SCN即可.而语句开始的SCN和事务并没有关系.所以Oracle的事务,并不包括查询.
而innodb支持可重复读隔离级别,也就是说在一个事务中,无论运行多少次查询,结果都必须是一致的.
(innodb不仅支持可重复读,并且使用间隙锁在可重复读级别避免了幻读,当然这也带来了很多问题..)
所以它记录的不是每个查询语句的LSN,而是事务第一个语句发生时的LSN,无论第一个语句是查询,还是修改.
innodb在可重复读的级别下,查询用事务开始时的LSN应用MVCC,与Oracle不同的是,innodb查询回滚段中小于事务开始的LSN的数据版本,
而oracle查询回滚段中小于语句SCN的数据版本.
也就是说,同样都是MVCC,oracle是语句级的,innodb是事务级的
这里有一个问题,按说事务包括查询是因为可重复读隔离级别的需要,但是innodb读提交隔离级别同样也将查询作为了事务的一部分.
可能是因为架构或者代码实现层面的问题吧.
不管怎么样,Innodb就是这么做了.
然后再说说metadata lock
在5.5.3之前,metadata lock是语句级的,这实际上破坏了事务的一致性.
比如一个事务,在可重复读隔离级别,运行两次查询,居然结果不一致.
这正是因为metadata lock是语句级造成的问题,
在两个查询的间隔,另外一个会话执行了truncate table.
所以再次运行查询,没有任何结果.
MySQL为了解决这个问题,在5.5.3将metadata lock提升为事务级别的锁.
任何DDL都需要先获得metadata lock,但是这个锁需要等事务结束的时候释放.
同样的实验,在5.6.13就变成这样的了.
第一个会话的事务没有结束,那么第二个会话的DDL就被阻塞
使用show processlist可以看到DDL语句在等待第一个会话事务的metadata lock
通过这种方式,就保证了可重复读隔离级别下,事务的一致性.
和之前提到的查询也作为事务的一部分一样,innodb并没有为读提交量身定制一些东西,
比如读提交并不需要查询作为事务的一部分
和读提交并不需要事务级别的metadata lock.
可能是出于架构层面的问题,很多可重复读的特性强加在了读提交上,
所以一旦这些特性出现问题,即使将隔离级别降为读提交也不能避免.
接下来问题来了,
刚才的DDL被metadata lock阻塞,这个DDL还会进一步阻塞其他的事务.甚至是查询(查询是innodb事务的一部分.)
这就有点抓狂了,因为这个时候,系统其实已经Hung了.
假设id为1的线程持有metadata lock 没有提交,
id为2的线程进行DDL,然后被阻塞在线程1的metadata锁上,
这时,数据库依次来了8个查询,他们都阻塞在了线程2上.
假如线程1的事务不结束,其他的线程都被阻塞.
即使线程1的事务结束了..也是后面8个事务依次获得metadata锁,与此同时,这个DDL可能又阻塞了80个事务..
这时候,系统的并发为1,这个DDL可能永远不能执行.并且这种情况不在死锁检测的范围内.
它的锁超时时间,由lock_wait_timeout参数控制,默认是31536000(一年,坑爹吧)
MySQL虽然保证了事务的一致性,避免了bug,但是引入的问题却可能让我这样的初级dba丢了饭碗..
最后梳理一下可能引发metadata lock连环阻塞的情况
1.在有其他事务运行的时候,进行DDL操作(alter table;truncate;)
2.在mysqldump运行的时候,进行DDL操作.(想想就觉得坑爹)
3.在Master-Slave复制环境,在Slave运行查询,会导致Master传过来的DDL阻塞.导致复制延迟增大.
4.创建索引(...)
作为初级dba来说,为了保住饭碗,可以有两个动作
1.将lock_wait_timeout参数调低
2.在运行DDL之前,查看事务是否频繁,在运行DDL之后,开启另外一个会话,使用show processlist查看是否被metadata lock阻塞.
一旦阻塞,先Kill ddl的操作.
MySQL索引之隔离列
MySQL索引之哈希索引
MySQL索引之前缀索引和索引选择性
MySQL索引使用率监控
MySQL索引优化和in or替换为union all
MySQL索引设计的原则
本文永久更新链接地址：
，
Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AMInnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.
MySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AMCompared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.
Learning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AMMySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.
MySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AMMySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.
MySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AMMySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.
MySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AMMySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.
The Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AMSQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.
MySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AMThe basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA
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
DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software
Zend Studio 13.0.1
Powerful PHP integrated development environment
SublimeText3 Mac version
God-level code editing software (SublimeText3)
mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),
PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool
