Home Database Mysql Tutorial About Mysql transactional

About Mysql transactional

Oct 13, 2017 am 10:30 AM
mysql about

1》What does transaction refer to?
         A transaction can be regarded as a business logic processing unit, this unit is either executed or not executed;
2》ACID principle:
 (1) Atomicity(Automicity)
 (2) Consistency (Consistency)
A account has 3,000 yuan, B account 2000 fast,
-A --500-"B
A account and B account is 5000 fast
Before the implementation and after the execution , this sum and this state should be consistent.
(3) isolation (LSOLATION)
A account from 3000-"500-& gt; B account 2500
update (1) Transfer of transfers
select Sun () a b Jianhe
(4) Durability
Guaranteed persistence:
1 & gt before handling:
Crossing the data to the disk before. What if you want to withdraw it? Data recalled from disk? Very slow? it's painful? what to do?
2 & gt; combined with the transaction aspiration to complete the
things log, there is also a disk on the disk. What is the difference between it and the data file?
            Difference: The transaction log generates sequential I/O, while the disk data file is random I/O. Each operation of the transaction log is written sequentially to a continuous storage block on the disk, and our data file it's not true.
(1) Things Log: Sequence IO
(2) Data file: Random Io
Write the transaction into the transaction log. After a while, the background or related process of the thing will be Implemented into data files, this way we ensure the persistence of the data operated by things.

3》The status of the transaction.
     (1) Either an active transaction
         The transaction is being executed
 (2) Partially committed transaction
             We know that the transaction is either executed or not executed , what is partial submission?
          That is, the transaction has been executed, part of it has been written to the disk, and the other part is being executed, and the last execution statement is in the process of being written. We call it partial submission
        (3) Failed transaction
                The transaction is normal Submitted, but did not achieve the purpose, it is called a failed transaction
       (4) Aborted transaction
               Aborted transaction is called not submitted, or it ends prematurely halfway, which is called abort.
(5) The transaction submitted
Health submitted by the health, our affairs must be a certain middle of these 5 states. Once the transaction is submitted, it cannot be revoked.

4 "Equipment and scheduling of the transaction
Affairs:
1 & gt; increase throughput and resource utilization rate
2 & gt; reduce waiting time
Affairs to reduce each other Between the effects, we need to isolate the transaction, what if it is isolated? This depends on transaction scheduling. Any database system itself has such complexity in transaction scheduling. The scheduling method integrates many strategies to complete the scheduling. Generally speaking, in order to ensure the isolation and atomicity of transactions, they need to use transaction scheduling. To complete, there are two general strategies for scheduling:

transaction scheduling:
1 & gt; recovery scheduling
Generally, when the two transactions are scheduled between each other, the cross execution of any two transactions will not lead to the change of another transaction. We Call it as: recovery scheduling

# 2 & gt; No -level joint adjustment
to restore scheduling and step by step means that after the end of the transaction, they have no impact on each other, especially in affairs, in affairs, in affairs It will still be affected when rolling back, and it will still be affected when the transaction is rolled back. Therefore, in order to avoid the impact during rollback, you can also implement what is called: cascade-free scheduling.

5》Isolation of Transactions
We know about transaction scheduling. A transaction may involve multiple operations. These operations can be cross-executed. The existence of transaction scheduling is to better schedule the order so that these cross-executions can be interconnected with each other. It will not have an excessive impact, so in order to further reduce the impact between transactions, we have so-called isolation levels. There are four isolation levels for transactions. Which four?
1 & gt; Read Read UNCOMMITTED
at the isolation level. All transactions can see the execution results of other unprepared transactions. How much is the level? Reading the unsurmitted data is also becoming: Dirty Read (Dirty Read)
2 & gt ), which satisfies the simple definition of isolation: a transaction can only see changes made by transactions that have been committed; When multiple instances read data concurrently, they will see the same data rows. However, in theory, this leads to another problem: phantom read (Phantom read). Simply put: phantom read refers to reading a certain range as a user. When the data row is in this range, another transaction inserts a new row in this range. When the user reads the data row in this range, he will find a new "phantom row"
. Innodb and Falcon engines use multi-version concurrency control (MVCC) mechanism solves this problem.

4 & GT; Serial serializable
This is the highest -level isolation mechanism. It can conflict with each other through mandatory transactions, so as to solve the problem of illusion reading. Simply put: it is in A shared lock is added to each read data row. At this level, it may lead to a large number of timeouts and lock competition. Concurrency control is implemented, and our real-time concurrency control

          technology relies on the following technologies:

                        (1). Lock. Read lock, write lock, exclusive lock, shared lock.
                                                                                                                                               . Timestamp, the start time and execution time of each transaction must be recorded
#              When a transaction applies for a type of lock, in order to achieve concurrency control, it needs this lock, but due to the results of transaction scheduling, it has never been able to obtain the lock. The result is:
                                                                                         . In addition, there is another kind of lock called: deadlock

6》Start transaction and rollback

Generally speaking, the operation of starting a transaction:         
                                                                                                                                                                                                                      Start transaction 2>Delete information in one of the student tables
    delete from student where num='4'
​ ​ 3>How to perform data recovery? Transaction rollback. Guessed it.
                                                                                                                                                                                                                                       
#                                                                                                                              select * from student;
                                                                                                           3>Transaction submission
                        commit ;
          4>Try to rollback again
                                                                                                                  rollback
                                                                select * from student; At that time, I found that the 75th one was wrong and the execution was wrong. Do I need to roll back the first 80? What should we do at this time? The role of save points comes out. For example: I save every 10 operations: ​​​Affairs
Start Transaction;
2 & GT; Delete the information of "Cuihua" in one Student table
Delete from Student Where Name = 'Cuihua'
Save a transaction point
Savepoint yya
3 & gt; Delete the information of "Ximen Blog" in one of the Student tables

Delete from Student Where Name = 'Ximenchuiniu'

Savepoint yyb
4 & GT; "Two" Information
                    delete from student where name='niuer'
                               savepoint                   ’’ ’     through ’s ’’s ’ through ’’s' through ’ through through through through through‐‐‐‐‐‐‐‐ ‐ to 5>                                 rollback to yya Think about it. What is the save point yya? ​ ​
​ ​ ​ 6>Check whether the recovery is successful
​ ​ ​ ​ ​select * from student;

6.3 Automatic submission of transactions
1 & gt; Query mysql transaction automatically submit
SELECT @@ AutoCommit;
MySQL & GT; Select @@ AutoCommit;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----+
                                                                                                                                          ------+
                                         1 row in set (0.00 sec)

                                                                                                               >Test Verification
Delete from student where name = 'ximenchuniu'
Rollback;
Does it return? And we didn't explicitly start a transaction? Transactions support DML statement rollback. DDL is a hidden test commit, so unfortunately it is not supported.

Verify that read is uncommitted:
                                                                                                                                                                          together  together  together  out    Query the isolation level select @@tx_isolation; Affairs
Window One Window Two Common Operation:
1.Select @@ tx_isolation;
2.Set tx_isolation = 'Read_uncommittd';

## Window 1 Operation:

1.start transaction; Start a cross transaction
        2.update student set age='70' where d_id='1010';

Window 2 operation
        1.start transaction;  Start a cross transaction
2.Select * from Student; You can see that the 1 window is changed to 70, and the window transaction is not submitted. It is seen by 2, which is called

verification read and submitted
, open In the two windows, the two parties crossed a transaction
window 1 and window 2 common operation:
1.Select @@ tx_isolation;
2.Set tx_isolation = 'read_committd'
## window window 1 Operation:
1.start Transaction; Start a cross -crossing transaction
2.Update Student Set Age = '70 'where d_id =' 1010 ';

3.Commit; The third step of the window will see the data I submitted again


2 Operation
1. Start Transaction; Start a cross transaction
2.Select * from Student; Updated data
3.Select * from Student; When a window A executes 3 steps, you can see the update data and avoid dirty reading

Verification can be re -read
A MySQL, open two windows, and the two parties crossed a transaction
Phantom Reading:
definition: the same transaction T1, in two different time periods, such as 1 2 execution, execute On the same day, the query statements get the records of the records are differently called Phantom Reading

Window 1 and Window 2 Common operation:
1.Select @同 同 同 同 同 同';

window 1 Operation
1.start transaction; Start a cross -crossing transaction
2.Update Student Set Age = '60' where num = '10 ';
3.Commit

2 Operation
1. Start Transaction; Start a cross transaction
2.Select * from Student; ; 1commit;Still not see the updated data in window 1
In the two windows, the two parties cross the business of a transaction
两 1 and window 2 common operation:
1.Select @@ tx_isolation;

2.Set tx_isolation = 'Serializable';

## 两1 operation
                1.start transaction; start a cross transaction
              2.update student set age='60' where num='10'; cannot be executed. It can only be executed after Window 2 submits the operation!

2 Operation
1.start Transaction; Start a cross -crossing transaction
2.Commit; Submit here, the Update of the 1 window can be successfully executed. This is serializable!
Reason:
Two transactions to cross the same data at the same time, one party must submit first. Otherwise, reading and writing will block each other.

The above is the detailed content of About Mysql transactional. For more information, please follow other related articles on the PHP Chinese website!

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

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
WWE 2K25: How To Unlock Everything In MyRise
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

MySQL: The Ease of Data Management for Beginners MySQL: The Ease of Data Management for Beginners Apr 09, 2025 am 12:07 AM

MySQL is suitable for beginners because it is simple to install, powerful and easy to manage data. 1. Simple installation and configuration, suitable for a variety of operating systems. 2. Support basic operations such as creating databases and tables, inserting, querying, updating and deleting data. 3. Provide advanced functions such as JOIN operations and subqueries. 4. Performance can be improved through indexing, query optimization and table partitioning. 5. Support backup, recovery and security measures to ensure data security and consistency.

Can I retrieve the database password in Navicat? Can I retrieve the database password in Navicat? Apr 08, 2025 pm 09:51 PM

Navicat itself does not store the database password, and can only retrieve the encrypted password. Solution: 1. Check the password manager; 2. Check Navicat's "Remember Password" function; 3. Reset the database password; 4. Contact the database administrator.

MySQL: Simple Concepts for Easy Learning MySQL: Simple Concepts for Easy Learning Apr 10, 2025 am 09:29 AM

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.

How to create navicat premium How to create navicat premium Apr 09, 2025 am 07:09 AM

Create a database using Navicat Premium: Connect to the database server and enter the connection parameters. Right-click on the server and select Create Database. Enter the name of the new database and the specified character set and collation. Connect to the new database and create the table in the Object Browser. Right-click on the table and select Insert Data to insert the data.

How to view database password in Navicat for MariaDB? How to view database password in Navicat for MariaDB? Apr 08, 2025 pm 09:18 PM

Navicat for MariaDB cannot view the database password directly because the password is stored in encrypted form. To ensure the database security, there are three ways to reset your password: reset your password through Navicat and set a complex password. View the configuration file (not recommended, high risk). Use system command line tools (not recommended, you need to be proficient in command line tools).

MySQL and SQL: Essential Skills for Developers MySQL and SQL: Essential Skills for Developers Apr 10, 2025 am 09:30 AM

MySQL and SQL are essential skills for developers. 1.MySQL is an open source relational database management system, and SQL is the standard language used to manage and operate databases. 2.MySQL supports multiple storage engines through efficient data storage and retrieval functions, and SQL completes complex data operations through simple statements. 3. Examples of usage include basic queries and advanced queries, such as filtering and sorting by condition. 4. Common errors include syntax errors and performance issues, which can be optimized by checking SQL statements and using EXPLAIN commands. 5. Performance optimization techniques include using indexes, avoiding full table scanning, optimizing JOIN operations and improving code readability.

How to create a new connection to mysql in navicat How to create a new connection to mysql in navicat Apr 09, 2025 am 07:21 AM

You can create a new MySQL connection in Navicat by following the steps: Open the application and select New Connection (Ctrl N). Select "MySQL" as the connection type. Enter the hostname/IP address, port, username, and password. (Optional) Configure advanced options. Save the connection and enter the connection name.

How to execute sql in navicat How to execute sql in navicat Apr 08, 2025 pm 11:42 PM

Steps to perform SQL in Navicat: Connect to the database. Create a SQL Editor window. Write SQL queries or scripts. Click the Run button to execute a query or script. View the results (if the query is executed).

See all articles