This article has participated in the good article call order activity, click to see: back end, big front end double track submission, 20,000 yuan prize pool for you to challenge!

The vast sea of thousands of thousands, thank you for this second you see here. Hope my article is helpful to you!

Wish you in the future, keep love, go to the mountains and seas!

1. The transaction

1.1 Overview of transactions

One or more SQL statements in a transaction form an execution unit, and either all or none of them are executed.

1.1.1 Concept introduction

The normal process of transferring money from the little fish to the kitten is to subtract money from the little fish’s account and add money to the kitten’s account, right? But suppose, when Xiaoyu is transferring money, the money has been deducted from xiaoyu’s account, but the money has not reached The account of Kitten, and the server crashes at this time. There is no extra money in kitten’s account, but there is less money in Xiaoyu’s account. There is a problem with the data. Do you think this transfer is ok? Of course not, so this is where the business comes in.

1.1.2 transaction

A transaction consists of one or more SQL statements in a separate cell where each SQL statement is interdependent. The entire unit is an integral whole. If an SQL statement in the unit fails to execute or produces an error, the entire unit will be rolled back. All affected data will be returned to the state before the event began; If all THE SQL statements in the cell execute successfully, the transaction executes smoothly.

1.1.3 Four ACID properties of transactions

Transaction characteristics meaning
Atomicity All operations in a transaction either succeed or fail.
Consistency Databases always transition from one consistent state to another. Indicates that the system status is consistent after the transaction ends.
Isolation Represents that when multiple transactions are accessed concurrently, the transactions are isolated and invisible. One transaction does not affect the operation of other transactions.
“Durability” Indicates that once a transaction is committed successfully, its data operation on the database is permanent.

1.2 Transaction creation

1.2.1 Transaction classification

  • We first create the database and data required by the transaction:

    CREATE TABLE IF NOT EXISTS account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20),
	balance DOUBLE
);

INSERT INTO account(username, balance) 
VALUES('fish'.1000), ('the cat'.1000);
Copy the code

1.2.2 Implicit transactions

Transactions have no obvious open and end flags. By default, the MySQL commit status is enabled.

  • Check the default commit status of the database:

    SHOW VARIABLES LIKE 'autocommit';

-- or the second:
SELECT @@AUTOCOMMIT; - recommend
Copy the code

  • By default, all database additions, deleters, and changes are committed by default.

    UPDATE account SET balance = Awesome! WHERE username='fish';
SELECT * FROM account;
Copy the code

    When we execute the modification statement, we can find that the number of small fish’s account is indeed less, but if we want to achieve transfer and other functions, this certainly can’t meet our needs. We can’t just deduct one person’s money and not add another person’s money, right? I would never do that, haha. You give me, I can accept a little bit!

  • Therefore, we need to turn off the default submission function, and then perform the operation of the transfer function. Let’s move on.

1.2.3 Displaying transactions

Transactions have distinct open and end flags. The automatic submission function needs to be disabled. Then start the transaction, execute the SQL statement, and check whether the commit or rollback is successful.

Let’s first look at SQL statements related to display transactions:

The SQL statement describe
SET autocommit = 0; Turn off default commit
START TRANSACTION; Open the transaction
COMMIT; Commit the transaction
ROLLBACK; Roll back the transaction
1. Usage Procedure

Transaction execution can be divided into two types:

  1. Success: start the transaction, write multiple SQL statements in the transaction, commit the transaction if successful, once committed, can not be changed.
  2. Failure: The transaction is started first, and multiple SQL statements in the transaction are written. If the transaction fails, the transaction needs to be rolled back to ensure normal data.
2. Concrete demonstration

Before the demo, restore the table regardless of the previous table:

UPDATE account SET balance = 1000 WHERE username='fish';
UPDATE account SET balance = 1000 WHERE username='the cat';
Copy the code

To demonstrate the steps:

  • Success:

    -- Start a transaction
SET autocommit = 0;
START TRANSACTION;

-- Write transaction SQL statements
UPDATE account SET balance = 800 WHERE username='fish';
UPDATE account SET balance = 1200 WHERE username='the cat';

-- Commit transaction
COMMIT;

SELECT * FROM account;
Copy the code

    As you can see, the fish’s account is indeed 200 less, while the cat’s account has been added 200. This is a normal transaction and the commit is successful.

  • Failure:

    -- Start a transaction
SET autocommit = 0;
START TRANSACTION;

-- Write transaction SQL statements
UPDATE account SET balance = 500 WHERE username='fish';
UPDATE account SET balance = 1500 WHERE username='the cat';

-- Roll back transactions
ROLLBACK;

SELECT * FROM account;
Copy the code

    As you can see, Xiaoyu wants to continue transferring 300 to the cat, but I rolled back (there is no way to fail to roll back, only to set the rollback), xiaoyu’s account will continue to keep 800, and the cat’s account is still intact at 1200. Nothing has changed. It feels like nothing has happened. Data consistency is ensured.

1.3 the rollback point

We can see that we are rolling back directly to the state where the transaction started. Can we roll back to a specified place?

1.3.1 grammar

  • Set the rollback point:

    Savepoint Name of the rollback point;Copy the code

  • Back to the rollback point:

    rollback toRollback point name;Copy the code

1.3.2 usage

-- Start a transaction
SET autocommit = 0;
START TRANSACTION;

-- Write transaction SQL statements
UPDATE account SET balance = 600 WHERE username='fish';
UPDATE account SET balance = 1400 WHERE username='the cat';
SAVEPOINTnz; # Set the rollback point hereWrite the transaction SQL statement again
UPDATE account SET balance = 400 WHERE username='fish';
UPDATE account SET balance = 1600 WHERE username='the cat';

-- Roll back to the rollback point
ROLLBACK TO nz;
SELECT * FROM account;
Copy the code

As you can see, we made two changes, and finally because we set the rollback point when we changed the fish to 600, we rolled back only to this point, instead of rolling back the whole thing.

1.4 Transaction isolation level

Multiple transactions running at the same time can cause various concurrency problems if the necessary isolation mechanisms are not in place when these transactions access the same data in the database. The ideal state of transaction operation: multiple transactions do not affect each other, and if the isolation level is not set properly, concurrent access issues may arise.

So what’s the problem with concurrency?

  • Dirty read: For two transactions, one transaction reads data that has been updated but not committed by the other transaction.
  • Non-repeatable read: For two transactions, two reads of data in one transaction are inconsistent, that is, one field was read, but the other transaction changed the data, and then the second read changed the data.
  • Magic read: for two transactions, a transaction read multiple times, the amount of data is not the same, that is, a transaction read a field, but another transaction to add some more fields or data, at this time after reading again will be more than a few rows, illusion feeling!

Is not a little fan misty, like listening to the opposite direction of the week total clock, fan misty you give dream! Or let me guide you, and listen to me slowly detail!

First, let’s look at transaction isolation levels. MySQL provides us with four isolation levels. Let’s see what they are

  • Read -uncommitted: Indicates the lowest isolation level. Read data that has not yet been committed is also a dirty read. This can occur as dirty reads, unrepeatable reads, and phantom reads.

  • Read -committed: Read committed data in a concurrent transaction. This prevents dirty reads, but the value is changed each time, so that non-repeatable reads and phantom reads can still occur.

  • Repeatable -read repeatable read: Default isolation level of mysql, which prevents dirty and non-repeatable reads, but magic reads still occur. The magic read is originally I read only one line of data, at this time again read may be more than one line, at this time is a magic read.

  • Serializable Serializable: Indicates the highest isolation level, which can effectively resolve dirty reads, non-repeatable reads, and phantom reads. But it will be less efficient.

Isolation level Dirty read Unrepeatable read Phantom read
Read-uncommitted Read is not committed May appear May appear May appear
Read-committed Read committed Can be solved May appear May appear
Repeatable -read Repeatable read Can be solved Can be solved May appear
Serializable serializable serializable Can be solved Can be solved Can be solved

Demonstrates the isolation level progression

In order to better demonstrate the interaction between two transactions, I still use the DOS window.

Advanced 1: Demonstrates the lowest level of read-uncommitted

  1. How do I view the global transaction isolation level

    show variables like '%isolation%';

select @@tx_isolation; 
Copy the code

  2. Set the current transaction isolation level

     set session transaction isolation level read uncommitted;
 
 show variables like '%isolation%';
Copy the code

    Of course the other one needs to be changed, same operation!

  3. The transaction is performed on the first window A

    use data_test;
set names gbk;
select * from account;
set autocommit = 0;
START TRANSACTION;
update account set balance = 400 where username='fish';
Copy the code

    First of all, we start the transaction on window A, and modify the money of the fish to 400, at this time no commit, let’s go to the window B to check the results.

  4. View window B

    use data_test;
set names gbk;
select * from account;
Copy the code

    You can see that our window A has not committed data, but our window B can still read the uncommitted data of window A, which is dirty read phenomenon.

  5. If we roll back the transaction in window A, let’s check the result in window B.

    A window is a:

    rollback;
Copy the code

    Window b:

    select * from account;
Copy the code

    We can see that the result of window B is not the same as that of window B.

  6. So let’s show you the illusion again

    A window is a:

    set autocommit = 0;
START TRANSACTION;
insert into account values(null.'the birds'.1000);
Copy the code

    Window b:

    select * from account;
Copy the code

    We can find that when we add a line in window A, it is also unsubmitted. But when we read again in window B, we can find why there is an extra line. At this time, we will have an illusion, which is the phenomenon of magic reading.

    Finally, we roll back in window A to ensure that the data is restored to its original state!

  7. Summary:

    Read-uncommitted: This is the lowest isolation level, where all concurrency can occur.

Advanced 2: Demonstrate the second level of read-committed

  1. Set the current transaction isolation level

    set session transaction isolation level read committed;

select @@tx_isolation; 
Copy the code

    Of course the other one needs to be changed, same operation!

  2. Do a dirty read demonstration to see if the dirty read phenomenon is really resolved, and both start transactions.

    A window is a:

    set autocommit = 0;
START TRANSACTION;
update account set balance = 400 where username='fish';
Copy the code

    Window b:

    set autocommit = 0;
START TRANSACTION;
select * from account;
Copy the code

    Can discover our dirty read phenomenon really can avoid!

  3. Demonstrate the non-repeatable read phenomenon

    In the above state, commit the transaction to window A, and then window B looks at the data again

    A window is a:

    commit;
Copy the code

    Window b:

    select * from account;
Copy the code

    We can see that the phenomenon of non-repeatable reading still exists.

  4. Demonstrate the phenomenon of phantom reading

    A window is a:

    set autocommit = 0;
START TRANSACTION;
insert into account values(3.'the birds'.1000);
commit;
Copy the code

    Window b:

    select * from account;
Copy the code

    It can be found that the phenomenon of hallucination still exists.

    Finally, window B performs rollback to ensure consistency in the next demo.

  5. Conclusion:

    Read-committed: Can read committed data in a concurrent transaction, effectively preventing dirty reads, but non-repeatable reads and phantom reads may still occur.

Advanced 3: Demonstrate the third level repeatable-read

  1. Set the current transaction isolation level

    set session transaction isolation level repeatable read;

select @@tx_isolation; 
Copy the code

    Of course the other one needs to be changed, same operation!

    Dirty reads are addressed in the second level, so there is no need to demonstrate them here. There is a need for guest officials can try to try yo!

  2. Demonstrates the non-repeatable read phenomenon

    Let’s first read the latest data of window B:

    Then modify the data in window A:

    set autocommit = 0;
START TRANSACTION;
update account set balance = 800 where username='fish';
commit;
Copy the code

    Read data from window B again to see if the unrepeatable read phenomenon can be solved:

    You can find that, before and after the two reads are the same, on behalf of the solution can not be repeated phenomenon!

  3. To demonstrate the phenomenon of phantom reading:

    A window is a:

    set autocommit = 0;
START TRANSACTION;
insert into account values(4.'abortion'.1000);
commit;
Copy the code

    We modify the data in window B, and change the amount of id>2 to 500

    update account set balance = 400 where id >2;
select * from account;
Copy the code

    You can find that the phenomenon of magic reading will still have, and may accidentally change someone else’s data

    Finally, window B performs rollback to ensure consistency in the next demo.

  4. Conclusion:

    Repeatable -read repeatable read: The result of reading the same field multiple times is consistent and can prevent dirty and non-repeatable reads, but magic reads can still occur.

Advanced 4: Demonstrates the highest level serializable

  1. Set the current transaction isolation level

    set session transaction isolation level serializable;

select @@tx_isolation; 
Copy the code

    Of course the other one needs to be changed, same operation!

    The unrepeatable read phenomenon is resolved at level 3, so there is no need to demonstrate it here. There is a need for guest officials can try to try yo!

  2. To directly demonstrate the phenomenon of phantom reading:

    Let’s first look at the data in window B:

    First, we enter the data modification statement in window B without pressing Enter:

    update account set balance = 400 where id >3;
Copy the code

    Then add data to window A without committing:

    set autocommit = 0;
START TRANSACTION;
insert into account values(5.'little what'.1000);
Copy the code

    At this time, modify the data in window B, press Enter:

    Serializable means that the table is locked when other users modify the data. The table can only be accessed for the current time and can only be accessed until other users commit the data.

    After window A commits, let’s try window B to modify the data:

    You can find that you can only continue to access window A until it is committed, so you can effectively avoid phantom reading phenomenon.

  3. Conclusion:

    Erializable serialization: The highest isolation level, which can effectively solve dirty reads, non-repeatable reads, and phantom reads. But it will be less efficient.

conclusion

For us, use the default! You don’t need to use the highest isolation level either, because it’s inefficient, unless strong consistency is required.

  • Read -uncommitted: Indicates the lowest isolation level. Read data that has not yet been committed is also a dirty read. This can occur as dirty reads, unrepeatable reads, and phantom reads.

  • Read -committed: Read committed data in a concurrent transaction. This prevents dirty reads, but the value is changed each time, so that non-repeatable reads and phantom reads can still occur.

  • Repeatable -read repeatable read: Default isolation level of mysql, which prevents dirty and non-repeatable reads, but magic reads still occur. The magic read is originally I read only one line of data, at this time again read may be more than one line, at this time is a magic read.

  • Serializable Serializable: Indicates the highest isolation level, which can effectively resolve dirty reads, non-repeatable reads, and phantom reads. But it will be less efficient.

1.5 Differences between DELETE and TRUNCate in transaction usage

Delete can be rolled back, and TRUNCate cannot be rolled back.

That in this case, whether believe or not, we directly to verify the next can, lest I lie to you ha!

Let’s look at the starting data:

Delete:

-- Start a transaction
SET autocommit = 0;
START TRANSACTION;

-- Write transaction SQL statements
DELETE FROM account;

-- Roll back transactions
ROLLBACK;

SELECT * FROM account;
Copy the code

As you can see, the rollback succeeded after we deleted the data. Now let’s look at truncate

Truncate:

-- Start a transaction
SET autocommit = 0;
START TRANSACTION;

-- Write transaction SQL statements
TRUNCATE account;

-- Roll back transactions
ROLLBACK;

SELECT * FROM account;
Copy the code

As you can see, the rollback failed.

Conclusion:

TRUNCATE cannot be rolled back, and DELETE can be rolled back.

2. View

Remember the interview before, the interview brother asked me, do you know view, have you ever used it? And I…

Well, the past is the past, I finally entered this company, haha. This also proves that there is a limit to human learning, and human cognition must be expanded after all. If it remains unchanged, it will fall behind step by step.

Without further ado, let’s see what a view is.

2.1 Concept of View

MySQL has provided views since version 5.0.1. It is essentially a virtual table with rows and columns from the same table used in the query that defines the view, and is generated dynamically while using the view, saving only the SQL logic but not the query results. This saves a select query.

So why do we use views, and what good are views for our regular tables?

  1. Reusability: We can reuse SQL statements.
  2. Simplicity: Users of the view do not need to worry about the structure of the corresponding table, association conditions, and filter conditions, simplifying complex SQL operations.
  3. Security: Users using views can only access the result sets they are allowed to query, protecting our data and improving security.

So let’s learn how to use views!

2.2 Creating and Using views

Create syntax:

CREATE VIEWThe name of the viewASQuery statement;Copy the code

Requirements: Let’s query the employee name, department name, and job information containing a character in the mailbox.

Let’s create a view and do this:

CREATE VIEW view_emp_dep_job 
AS 
SELECT last_name, department_name, job_title
FROM employees e 
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id = e.job_id;
Copy the code

So where is the view that we created?

So when we create the view, how do we use the view?

Actually, using a view is just like using a table query.

Using view syntax:

SELECT * FROMView name;Copy the code

So let’s go back to the view that we created and look at the one that contains a.

SELECT * FROM view_emp_dep_job WHERE last_name LIKE '%a%';
Copy the code

Is it also very easy! It works like a normal table, but we simplify complex SQL statements and reuse SQL statements in this view.

2.3 Viewing Views

Since MySQL 5.1, the SHOW TABLES command displays not only the names of TABLES but also the names of VIEWS. There is no SHOW VIEWS command that displays VIEWS separately.

SHOW TABLES;
Copy the code

If you want to see how the view is defined, there are commands to help!

-- View the SQL statement that created the view
SHOW CREATE VIEW view_emp_dep_job;
Copy the code

2.4 Modifying a View

After we create the view, can we modify the view? That certainly can work!

Grammar 1:

CREATE OR REPALCE VIEWThe name of the viewASQuery statement;Copy the code

This statement means to modify the view if it exists, or create it if it doesn’t!

Presentation:

CREATE OR REPLACE VIEW view_emp_dep_job 
AS 
SELECT last_name, department_name
FROM employees e 
JOIN departments d ON e.department_id = d.department_id

SELECT * FROM view_emp_dep_job;
Copy the code

As you can see, it did work! Let’s look at the second grammar

Syntax 2:

ALTER VIEWView nameASQuery statement;Copy the code

This statement can be seen as modifying the view!

Presentation:

ALTER VIEW view_emp_dep_job 
AS 
SELECT * FROM employees;
SELECT * FROM view_emp_dep_job;
Copy the code

You can see that the statement can also be modified successfully!

Conclusion:

  • Statement 1:CREATE OR REPALCE VIEW VIEW name AS query statement;
  • Statement 2:ALTER VIEW VIEW name AS query statement;

2.5 Deleting a View

Grammar:

DROP VIEWView name;Copy the code

Presentation:

DROP VIEW view_emp_dep_job;
SHOW TABLES;
Copy the code

As you can see, the view was deleted successfully!

2.6 summarize

The difference between a view and a normal table:

  • Storage space:

    View, which holds SQL logic, is a query statement.

    Table It holds the table structure and data

  • Application scenario:

    If complex SQL statements need to be reused, we can create views to reuse them, and generally we opt for lookup tables to symbolize our changing requirements.

Completion of 3.

I believe you see the business view, is not also learned something? So come on together, not only to learn to improve yourself, but also to face the MySQL interview without panic! And the interview frequently asked in the interview questions have MySQL figure, so MySQL learning is also essential drop!

Let’s just wrap up a little bit:

Check the default commit status of the database:SHOW VARIABLES LIKE 'autocommit'Turn off the default commit:SET autocommit = 0Start a transaction:STARTTRANSACTION Commits a TRANSACTION:COMMITRoll back transactions:ROLLBACKSet the rollback point:savepointRollback point name Back to rollback point:rollback toRollback point name create view:CREATE VIEWThe name of the viewASQuery statements use views:SELECT * FROMView name View:SHOWTABLES modify view:ALTER VIEWView nameASDelete view:DROP VIEWView name;Copy the code

Of course, more sentences command some details, I hope you can learn carefully!

So far, the world is closed for today, good night! Although this article is over, BUT I still, never finished. I will try to keep writing articles. The coming days are long, and the horse is slow!

Thank you for seeing this! May you be young and have no regrets!

Note: If there are any mistakes and suggestions, please leave a message! If this article is also helpful to you, I hope you give a lovely and kind attention, thank you very much!