Four isolation levels of Mysql transactions-php手册-php.cn

Home

php教程

php手册

Four isolation levels of Mysql transactions

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Aug 04, 2016 am 08:56 AM

We will use transactions in development work, so do you know what types of transactions are there?
The MYSQL standard defines four types of isolation levels, which are used to limit which changes inside and outside the transaction are visible and which are invisible.
Lower isolation levels generally support higher concurrency and have lower system overhead.
Isolation levels from low to high: Read Uncommitted < Read Committed < Repeatable Read < Serializable.
Read Uncommitted (read uncommitted content)

At this isolation level, all transactions can see the execution results of other uncommitted transactions.
This isolation level is rarely used in practical applications because its performance is not much better than other levels.
Reading uncommitted data is also called dirty read. [Window A]: mysql> set GLOBAL tx_isolation='READ-UNCOMMITTED'; Query OK, 0 rows affected (0.00 sec) mysql> quit; Bye [root@vagrant-centos65 ~]# mysql -uroot -pxxxx (log in again) mysql> SELECT @@tx_isolation; +------------------+ | @@tx_isolation | +------------------+ | READ-UNCOMMITTED | +------------------+ 1 row in set (0.00 sec) mysql> use test; Database changed mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) [Window B]: mysql> select @@tx_isolation; +------------------+ | @@tx_isolation | +------------------+ | READ-UNCOMMITTED | +------------------+ 1 row in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test.user values (3, 'c'); Query OK, 1 row affected (0.00 sec) mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | +----+------+ 3 rows in set (0.00 sec) //So far, window B has not been committed; [Window A]: mysql> select * from user ; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | +----+------+ 3 rows in set (0.00 sec)Read Committed (read submitted content) This is the default isolation level for most database systems (but not the MySQL default). It meets the simple definition of isolation: a transaction can only see changes made by committed transactions. This isolation level also supports so-called nonrepeatable read, because other instances of the same transaction may have new commits during the processing of this instance, so the same select may return different results. <code class="prettyprint linenums lang-php">[Window A]: mysql> SET GLOBAL tx_isolation='READ-COMMITTED'; Query OK, 0 rows affected (0.00 sec) mysql> quit; Bye [root@vagrant-centos65 ~]# mysql -uroot -pxxxx (log in again) mysql> SELECT @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 1 row in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test.user; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) [Window B]: mysql> SELECT @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 1 row in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test.user; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) mysql> delete from test.user where id=1; Query OK, 1 row affected (0.00 sec) mysql> select * from test.user; +----+------+ | id | name | +----+------+ | 2 | b | +----+------+ 1 row in set (0.00 sec) [窗口A]: mysql> select * from test.user; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) [窗口B]: mysql> commit; Query OK, 0 rows affected (0.02 sec) [窗口A]: mysql> select * from test.user; +----+------+ | id | name | +----+------+ | 2 | b | +----+------+ 1 row in set (0.00 sec)Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。
简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。
InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。[窗口A]: mysql> SET GLOBAL tx_isolation='REPEATABLE-READ'; Query OK, 0 rows affected (0.00 sec) mysql> quit; Bye [root@vagrant-centos65 ~]# mysql -uroot -pxxxx(重新登录) mysql> SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) [窗口B]: mysql> quit; Bye [root@vagrant-centos65 ~]# mysql -uroot -pxxxx(重新登录) mysql> SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql> insert into test.user values (4, 'd'); Query OK, 1 row affected (0.00 sec) mysql> select * from test.user; +----+------+ | id | name | +----+------+ | 2 | b | | 4 | d | +----+------+ 2 rows in set (0.00 sec) [窗口A]: mysql> select * from test.user; +----+------+ | id | name | +----+------+ | 2 | b | +----+------+ 1 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from test.user; +----+------+ | id | name | +----+------+ | 2 | b | | 4 | d | +----+------+ 2 rows in set (0.00 sec)Serializable（序列化执行）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。
In short, it adds a shared lock on each data row read. At this level, a lot of timeouts and lock contention can result. [Window A]: mysql> SET GLOBAL tx_isolation='SERIALIZABLE'; Query OK, 0 rows affected (0.00 sec) mysql> quit; Bye [root@vagrant-centos65 ~]# mysql -uroot -pxxxx (log in again) mysql> SELECT @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | SERIALIZABLE | +----------------+ 1 row in set (0.00 sec) mysql> select * from test.user; +----+------+ | id | name | +----+------+ | 2 | b | | 4 | d | +----+------+ 2 rows in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test.user values (5, 'e'); Query OK, 1 row affected (0.00 sec) [Window B]: mysql> quit; Bye [root@vagrant-centos65 ~]# mysql -uroot -pxxxx (log in again) mysql> SELECT @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | SERIALIZABLE | +----------------+ 1 row in set (0.00 sec) mysql> select * from test.user; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction [Window A]: mysql> commit; Query OK, 0 rows affected (0.01 sec) [Window B]: mysql> mysql> select * from test.user; +----+------+ | id | name | +----+------+ | 2 | b | | 4 | d | | 5 | e | +----+------+ 3 rows in set (0.00 sec)Thank ~

Source: http://mp.weixin.qq.com/s?__biz=MjM5NDM4MDIwNw==&mid=2448834642&idx=1&sn=c02c5cc8ab0c1f29142ac8f8aa6b78af#rd

For more [dry information sharing], please follow my personal subscription account.
Four isolation levels of Mysql transactions

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

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

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)

2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Repo: How To Revive Teammates

4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hello Kitty Island Adventure: How To Get Giant Seeds

4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

How Long Does It Take To Beat Split Fiction?

3 weeks ago By DDD

R.E.P.O. Save File Location: Where Is It & How to Protect It?

3 weeks ago By DDD

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

Where is the login entrance for gmail email?

7323

Java Tutorial

1625

CakePHP Tutorial

1350

Laravel Tutorial

1262

PHP Tutorial

1209

Related knowledge

Learn about introductory code examples for Python programming Jan 04, 2024 am 10:50 AM

Learn about Python programming with introductory code examples Python is an easy-to-learn, yet powerful programming language. For beginners, it is very important to understand the introductory code examples of Python programming. This article will provide you with some concrete code examples to help you get started quickly. Print HelloWorldprint("HelloWorld") This is the simplest code example in Python. The print() function is used to output the specified content

PHP variables in action: 10 real-life examples of use Feb 19, 2024 pm 03:00 PM

PHP variables store values during program runtime and are crucial for building dynamic and interactive WEB applications. This article takes an in-depth look at PHP variables and shows them in action with 10 real-life examples. 1. Store user input $username=$_POST["username"];$passWord=$_POST["password"]; This example extracts the username and password from the form submission and stores them in variables for further processing. 2. Set the configuration value $database_host="localhost";$database_username="username";$database_pa

From beginner to proficient: Code implementation of commonly used data structures in Go language Mar 04, 2024 pm 03:09 PM

Title: From Beginner to Mastery: Code Implementation of Commonly Used Data Structures in Go Language Data structures play a vital role in programming and are the basis of programming. In the Go language, there are many commonly used data structures, and mastering the implementation of these data structures is crucial to becoming a good programmer. This article will introduce the commonly used data structures in the Go language and give corresponding code examples to help readers from getting started to becoming proficient in these data structures. 1. Array Array is a basic data structure, a group of the same type

How to use PHP to write inventory management function code in the inventory management system Aug 06, 2023 pm 04:49 PM

How to use PHP to write the inventory management function code in the inventory management system. Inventory management is an indispensable part of many enterprises. For companies with multiple warehouses, the inventory management function is particularly important. By properly managing and tracking inventory, companies can allocate inventory between different warehouses, optimize operating costs, and improve collaboration efficiency. This article will introduce how to use PHP to write code for inventory warehouse management functions, and provide you with relevant code examples. 1. Establish the database before starting to write the code for the inventory warehouse management function.

Java implements simple bubble sort code Jan 30, 2024 am 09:34 AM

The simplest code example of Java bubble sort Bubble sort is a common sorting algorithm. Its basic idea is to gradually adjust the sequence to be sorted into an ordered sequence through the comparison and exchange of adjacent elements. Here is a simple Java code example that demonstrates how to implement bubble sort: publicclassBubbleSort{publicstaticvoidbubbleSort(int[]arr){int

Go language programming examples: code examples in web development Mar 04, 2024 pm 04:54 PM

"Go Language Programming Examples: Code Examples in Web Development" With the rapid development of the Internet, Web development has become an indispensable part of various industries. As a programming language with powerful functions and superior performance, Go language is increasingly favored by developers in web development. This article will introduce how to use Go language for Web development through specific code examples, so that readers can better understand and use Go language to build their own Web applications. 1. Simple HTTP Server First, let’s start with a

Huawei Cloud Edge Computing Interconnection Guide: Java code examples to quickly implement interfaces Jul 05, 2023 pm 09:57 PM

Huawei Cloud Edge Computing Interconnection Guide: Java Code Samples to Quickly Implement Interfaces With the rapid development of IoT technology and the rise of edge computing, more and more enterprises are beginning to pay attention to the application of edge computing. Huawei Cloud provides edge computing services, providing enterprises with highly reliable computing resources and a convenient development environment, making edge computing applications easier to implement. This article will introduce how to quickly implement the Huawei Cloud edge computing interface through Java code. First, we need to prepare the development environment. Make sure you have the Java Development Kit installed (

Guidance and Examples: Learn to implement the selection sort algorithm in Java Feb 18, 2024 am 10:52 AM

Java Selection Sorting Method Code Writing Guide and Examples Selection sorting is a simple and intuitive sorting algorithm. The idea is to select the smallest (or largest) element from the unsorted elements each time and exchange it until all elements are sorted. This article will provide a code writing guide for selection sorting, and attach specific Java sample code. Algorithm Principle The basic principle of selection sort is to divide the array to be sorted into two parts, sorted and unsorted. Each time, the smallest (or largest) element is selected from the unsorted part and placed at the end of the sorted part. Repeat the above

See all articles