“This is the first day of my participation in the August More Text Challenge. For details, see: August More Text Challenge.”

Click to view the previous article “The New Generation of migrant workers” before the end of the engineer must be the database -MySQL(1)

Chapter 5 Views in MySQL

A view is a virtual table. It is a logical table and contains no data itself. It is stored in the dictionary as a select statement. The table we use to create the view is called the “base table.” There are many advantages to using views. We can also use views to manage permissions for a row and a column. And when we modify the original table, it doesn’t affect the view.

Why are views rarely used in real World Web applications

In web development, we usually use ORM so that we don’t have to write a view query for each database. Every time we create a view, we have to query the “base table”, so there is a lot of overhead. So use as little as possible. In the past, I wrote a very complex stored procedure, and was diao by the company DBA. But no doesn’t mean we don’t use it. “No sword” and “no sword” are two different things.

Create a view

Our syntax for creating a view is similar to that for creating a table, except that we need to define the “base table.”

Create a view based on the students table
create view v_students (id, name, age, sex) as select id,name,age,sex from students with check option;
desc v_students;
- get
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
-- Query view data
select * from v_students;
- get
+----+--------+------+------+
| id | name   | age  | sex  |
+----+--------+------+------+
|  1 |Super brother|   20 | 男   |
|  2 |Meng Yang|   10 | 男   |
|  3 |Good dream|   18 | 女   |
|  4 |Ed|   18 | 男   |
|  5 |Xu god|   88 | 女   |
+----+--------+------+------+
Copy the code

As you can see, the view is no different from our normal table;

When creating a view, the data type is also based on the “base table”.

Use the show tables; Statement, you can see the V_students view;

Note: The as keyword cannot be omitted. With check option means that the view is updated within its permissions. Generally, you need to add it to ensure security. Views can be created based on more than one table, as long as the SELECT statement returns a record. The number of columns in the view is the same as the number of columns after the select. Once the view is created, we can use it just like a normal table. Including his additions, deletions, and checks.

Update views

Although much of the syntax is the same as that of ordinary tables, there are some differences.

Create or update views
create or replace view v_students(id, name, city) as select id, name, city from students with check option;
- get
+----+--------+-----------+
| id | name   | city      |
+----+--------+-----------+
|  1 |Super brother|hangzhou|
|  2 |Meng Yang| null      |
|  3 |Good dream|Gully channel|
|  4 |Ed|zhengzhou|
|  5 |Xu god|We rarely|
+----+--------+-----------+
Can the same ALTER be changed?
alter view v_students (name, age, city) as select name, age, city from students with check option;
-- That's fine, too
+--------+------+-----------+
| name   | age  | city      |
+--------+------+-----------+
|Super brother|   20 |hangzhou|
|Meng Yang|   10 | null      |
|Good dream|   18 |Gully channel|
|Ed|   18 |zhengzhou|
|Xu god|   88 |We rarely|
+--------+------+-----------+
Copy the code

When the “base table” field changes, we need to update the view to keep the data consistent. So it’s understandable that it’s expensive.

You can replace or alter the new view.

Run the update command to update data

-- Renew the city of dreams
update v_students set city="Hangzhou"where name="Good dream";- get
+--------+------+-----------+
| name   | age  | city      |
+--------+------+-----------+
|Super brother|   20 |hangzhou|
|Meng Yang|   10 | null      |
|Good dream|   18 |hangzhou|
|Ed|   18 |zhengzhou|
|Xu god|   88 |We rarely|
+--------+------+-----------+
Copy the code

At this point let’s look at the base table students;

+----+--------+------+-----------+------+----------+
| id | name   | sex  | city      | age  | grade_id |
+----+--------+------+-----------+------+----------+
|  1 |Super brother| 男   |hangzhou|   20 |        2 |
|  2 |Meng Yang| 男   | null      |   10 |        1 |
|  3 |Good dream| 女   |hangzhou|   18 |        3 |
|  4 |Ed| 男   |zhengzhou|   18 |        2 |
|  5 |Xu god| 女   |We rarely|   88 |        4 |
+----+--------+------+-----------+------+----------+
Copy the code

Obviously, our students table has also been updated. If you’re familiar with that, just like our array, the shallow copy also changes to the original data.

Note: The view itself has no data, and any DML operations we do on it will be updated to the base table. There are a number of cases where our views can’t do DML operations. The select statement contains distinct, group by, UNION, and grouping functions.

Why can’t some views do DML operations

It is obvious that the select statement is not a single table, and the database does not know which data to update when we update it.

Update view
create or replace view v_students as select name,(select grade from grades where id=1) as v_grade from students with check option;
- get
+--------+-----------+
| name   | v_grade   |
+--------+-----------+
|Super brother|In grade one|
|Meng Yang|In grade one|
|Good dream|In grade one|
|Ed|In grade one|
|Xu god|In grade one|
+--------+-----------+
-- Update data
update v_students set v_grade="Second grade"where name="Super brother";Error: cannot update data
ERROR 1348 (HY000): Column 'v_grade' is not updatable
Copy the code

Other possible cases where DML operations cannot be used are not listed here.

View nesting

So can we continue to create views based on views? The answer is yes.

create view v_stu_2 as select name from v_students with check option;
- get
+--------+
| name   |
+--------+
|Super brother|
|Meng Yang|
|Good dream|
|Ed|
|Xu god|
+--------+
Copy the code

Iv. View permission control

When we define a view, we can define the creator of the view and control the permissions.

➠ definer options

Specifies the creator of the view

create or replace definer="_xdq" view v_students(id, name, city) as select id, name, city from students with check option;
-- View view information
show create view v_students \G;
- get
View: v_students
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`_xdq`@`%` SQL SECURITY DEFINER VIEW `v_students` (`id`,`name`,`city`) AS select `students`.`id` AS `id`,`students`.`name` AS `name`,`students`.`city` AS `city` from `students` WITH CASCADED CHECK OPTION
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set.1 warning (0.00 sec)
Copy the code

You can see our creator _xdq

➠ Permission Control

-- Update user information
update v_students set city="Mountain Gully"where name="Good dream";-- This is an error
ERROR 1449 (HY000): The user specified as a definer ('_xdq'@The '%') does not exist
Copy the code

After we specify the creator of the view above, our host identity does not have permission to modify the data.

This is where we use another keyword.

SQL Security Invoker = SQL Security invoker
create or replace definer="_xdq" sql security invoker view v_students(id, name, city) as select id, name, city from students with check option;
-- At this point, we will revise the data
update v_students set city="Mountain Gully"where name="Good dream";- get
+----+--------+-----------+
| id | name   | city      |
+----+--------+-----------+
|  1 |Super brother|hangzhou|
|  2 |Meng Yang| null      |
|  3 |Good dream|Gully channel|
|  4 |Ed|zhengzhou|
|  5 |Xu god|We rarely|
+----+--------+-----------+
Copy the code

Note: SQL Security INVoker indicates that the user who creates this view needs to have operation rights on the base table. SQL Security Definer indicates that the user who creates this view needs to have operation rights on the base table.

So at this point, we’re pretty much done with the view, and there’s a lot more to do in practice.

Chapter 6 Stored procedures

A stored procedure is a program script, programmed using THE SQL language. Stored procedures are precompiled after they are created and stored in the database. When used, you can specify which stored procedure to call.

What are the benefits of using stored procedures?

You can encapsulate a lot of complex data operations into a script, and call stored procedures directly at the back end, instead of writing complicated SQL in the code; Unified interface can be used to ensure data security. Stored procedure as a encapsulated script has good reusability. Stored procedures can specify permission operations.

What are the disadvantages of using stored procedures?

First of all, it’s not good for maintenance, because the back-end code is cumbersome if it relies on stored procedures. Stored procedures are short scripts, but debugging them is not easy. Complex business logic with complex stored procedures makes it difficult to iterate your requirements next.

A snot a tear

In the past, the company used.net architecture, every project seemed to be very bloated, and stored procedures were called more and more complicated. Basically, every interface depended on stored procedures, which led to the huge SQL Server database, which was very unfriendly to me as a newcomer. After a period of time, I wrote stored procedures very quickly. Basically use the stored procedure to write, write addiction. After all, you can just use it in.NET and get the data you want.

But we’re going to use it less often in MySQL. Don’t stress the database too much.

Whether it works or not, we still need to learn it.

Create a stored procedure

Let’s start with an example that encapsulates a simple stored procedure called update_age to modify the age of the students table.

-- Create stored procedure
delimiter //
create procedure update_age(in p_age int.in p_name varchar(20))
begin
    update students set age=p_age where name=p_name;
end //
Copy the code

Note: since we’ve been writing from the command line, use delimiter // to change the ending semicolon to //

At this point we look at the created stored procedure:

- to see
show create procedure update_age//
-- To get the following result, I omitted a lot of information.. `update_age`(in p_age int.in p_name varchar(20))
begin
    update students set age=p_age where name=p_name;
end.Copy the code

Note: The stored procedure must be followed by parentheses () even if it has no arguments. Do not write the parameter name as the column name, otherwise it will only be treated as the column name.

How do I call a stored procedure?

Call using the call keyword.

Set age to 111
call update_age(111Superheroes, "")//
-- Look at the data table
select * from students;
+----+--------+------+-----------+------+----------+
| id | name   | sex  | city      | age  | grade_id |
+----+--------+------+-----------+------+----------+
|  1 |Super brother| 男   |hangzhou|  111 |        2 |
|  2 |Meng Yang| 男   | null      |   10 |        1 |
|  3 |Good dream| 女   |Gully channel|   18 |        3 |
|  4 |Ed| 男   |zhengzhou|   18 |        2 |
|  5 |Xu god| 女   |We rarely|   88 |        4 |
+----+--------+------+-----------+------+----------+
Copy the code

We can see that the age of Super brother has changed to 111.

We created a stored procedure that can be used multiple times and can set anyone’s age. Instead of having to write the update method every time.

What do I need to be aware of when creating a stored procedure?

Created using the CREATE PROCEDURE keyword, you can pass in parameters, and you can also have output. The stored procedure begins and ends with begin end and can be nested, equivalent to the curly braces {} in our code. Stored procedures can use a variety of SQL syntax, including DML, DDL, and flow control statements such as if, case, and while.

Parameters in the stored procedure

Stored procedures support parameters, and we can think of each stored procedure as a block of code.

The input parameters

In in our example above is the input parameter. This parameter is a local variable, as in other languages.

Output parameters

Use the out keyword to define the output parameters, which are also enclosed in parentheses within the stored procedure.

-- Create a stored procedure that outputs p2, p1 based on +1
create procedure out_test (in p1 int.out p2 int)
begin
    -- Print P2 first
    select p2;
    set p2 = p1 + 1;
    select p2;
end //
Copy the code

Now that the stored procedure is defined, let’s call it by declaring two variables, p_in and P_out, as the input and output parameters.

-- Declare the input parameter p_in
set @p_in=2//
-- Declare an argument p_out
set @p_out=1//
-- Call the stored procedure
call out_test(@p_in,@p_out)//
- get
+------+
| p2   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

+------+
| p2   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)
- print p_out
select @p_out//
- get
+--------+
| @p_out |
+--------+
|      3 |
+--------+
Copy the code

We can see that our declared variable p_out has been changed.

Note: When we pass in the out parameter, the out parameter is an output parameter to the caller, so our select print is null.

Input/output parameter

The keyword inout, the inout parameter has the characteristics of in and out, can input and output. It changes the external variables, which I won’t do here.

Use SQL programming

Our stored procedure is just a block of code, programmed in SQL, and programmed in other languages.

Define a variable

Declare variables with declare data types.

-- Declare variables
declare a int default 0;
Copy the code

Assign a value to a variable

Use the set keyword, which we used in the example above.

Conditional statements

Conditional statements are if, then, else, and case; Similar to if else and switch case in our other languages.

-- if then else
declare a int default 0;
if a=0 then set a=2 end if;
if a=0 then set a=2 else set a=3 end if;

-- case
case a
when 0 then set a = 1
when 1 then set a = 2
when 2 then set a = 3
end case;
Copy the code

Note: Each statement must be followed by the corresponding end statement.

Looping statements

Use while do… End while to do the statement loop. The use of repeat… Until end repeat to loop. Use the loop… Until end loop to loop.

-- Use while do
while a = 0 do
set a = a + 1;
end while;

- the use of repeat
repeat
set a = 1;
until a =0
end repeat;

- the use of loop
loop_lable: loop
set a = a + 1;
if a > 2 then
leave loop_label
end loop;

Copy the code

Note: There is no increment in SQL such as set a++. While checks the condition before the statement is executed. Repeat is executed after the operation is executed. Similar to our while and do while, loop requires the use of leave to leave the loop, and it has no loop condition.

So let’s just use this very briefly

Let’s do a simple paging and query the students table for people younger than 80 and count them.

-- Create stored procedure
-- pageIndex Specifies the page number
-- pageSize How many entries per page
-- totalCount Total number of entries
-- pageNum Total number of pages
create procedure sc_age(
    in params varchar(100),
    in _table text,
    in _where varchar(100),
    in _order varchar(100),
    in pageIndex int.in pageSize int.out totalCount int.out pageNum int
)
begin
    -- Sets the variable start line
    set @startRow = pageSize * (pageIndex - 1);
    set @pageSize = pageSize;
    set @rowIndex = 0;
    Concat is a string linking function
    set @strSql = concat(
        'select sql_calc_found_rows ',
        params,
        ' from ',
        _table,
        concat(' where ', _where),
        concat(' order by ', _order),
        ' limit '.@startRow.', '.@pageSize
    );
    - pretreatment
    prepare strsql from @strSql;
    -- Execute the pre-processed statement
    execute strsql;
    -- Delete it after the execution
    deallocate prepare strsql;

    set totalCount = found_rows();

    -- Determine the total number of pages
    -- div divisible by rounding \
    - % take over
    if (totalCount < = @pageSize) then
        set pageNum = 1;
    else if (totalCount % @pageSize > 0) then
        set pageNum = totalCount div @pageSize + 1;
    else
        set pageNum = totalCount div @pageSize;
    end if;
    end if;
end//

-- Call the stored procedure
set @totalCoun=0//
set @pageCount=0//
call sc_age('name, age, sex, city'.'students'.'age < 80'.'id asc'.1.2.@totalCount.@pageCount)//
-- can get
+--------+------+------+-----------+
| name   | age  | sex  | city      |
+--------+------+------+-----------+
|Meng Yang|   10 | 男   | null      |
|Good dream|   18 | 女   |Gully channel|
+--------+------+------+-----------+
-- Total number of records
select @totalCount
- get
+-------------+
| @totalCount |
+-------------+
|           3 |
+-------------+
-- Total pages
select @pageCount//
- get
+------------+
| @pageCount |
+------------+
|          2 |
+------------+

Copy the code

Note: We used preprocessor commands in the example above because in our stored procedure encapsulation, the SELECT statement is not written dead, in which case we need to use preprocessor statements. Preprocessing statements replace values in SQL with placeholders, which can be understood as template strings in JS. In the preparation of stored procedures, we must pay attention to the syntax, do not omit.

Stored procedure in the actual development of less and less use, basically are in the code processing, in order to reduce the database pressure, so we can learn, but as little as possible.

Chapter 7 Transactions in MySql

A “transaction” is the most basic unit of work in a database that ensures the integrity of a business. It is a sequence of operations that are either performed or not performed.

I. How to control transactions

We can control the transaction by setting auto-commit or not. Transactions that are not committed have a “rollback” capability.

Let’s first take a look at the autocommit status in MySQL.

select @@autocommit;
- get
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
Copy the code

Obviously, auto-commit is enabled by default. When we execute an SQL statement, the effect is immediately executed and cannot be rolled back. Continue with our example above, using the students table.

select * from students;
- get
+----+--------+------+-----------+------+----------+
| id | name   | sex  | city      | age  | grade_id |
+----+--------+------+-----------+------+----------+
|  1 |Super brother| 男   |hangzhou|  111 |        2 |
|  2 |Meng Yang| 男   | null      |   10 |        1 |
|  3 |Good dream| 女   |Gully channel|   18 |        3 |
|  4 |Ed| 男   |zhengzhou|   18 |        2 |
|  5 |Xu god| 女   |We rarely|   88 |        4 |
+----+--------+------+-----------+------+----------+

-- Then we modify a piece of data
update students set age=100 where name="Super brother";- get
+----+--------+------+-----------+------+----------+
| id | name   | sex  | city      | age  | grade_id |
+----+--------+------+-----------+------+----------+
|  1 |Super brother| 男   |hangzhou|  100 |        2 |
|  2 |Meng Yang| 男   | null      |   10 |        1 |
|  3 |Good dream| 女   |Gully channel|   18 |        3 |
|  4 |Ed| 男   |zhengzhou|   18 |        2 |
|  5 |Xu god| 女   |We rarely|   88 |        4 |
+----+--------+------+-----------+------+----------+
-- And then we roll back and forth
rollback;
-- The results did not change
Copy the code

Rollback the update statement using rollback. As you can see, the rollback did not succeed.

Next, let’s set the autoCOMMIT status to 0;

set autocommit=0;
- get
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
Copy the code

Then let’s go ahead and do the above:

update students set age=200 where name="Super brother";- get
+----+--------+------+-----------+------+----------+
| id | name   | sex  | city      | age  | grade_id |
+----+--------+------+-----------+------+----------+
|  1 |Super brother| 男   |hangzhou|  200 |        2 |
|  2 |Meng Yang| 男   | null      |   10 |        1 |
|  3 |Good dream| 女   |Gully channel|   18 |        3 |
|  4 |Ed| 男   |zhengzhou|   18 |        2 |
|  5 |Xu god| 女   |We rarely|   88 |        4 |
+----+--------+------+-----------+------+----------+
-- Then roll back
rollback;
- get
+----+--------+------+-----------+------+----------+
| id | name   | sex  | city      | age  | grade_id |
+----+--------+------+-----------+------+----------+
|  1 |Super brother| 男   |hangzhou|  100 |        2 |
|  2 |Meng Yang| 男   | null      |   10 |        1 |
|  3 |Good dream| 女   |Gully channel|   18 |        3 |
|  4 |Ed| 男   |zhengzhou|   18 |        2 |
|  5 |Xu god| 女   |We rarely|   88 |        4 |
+----+--------+------+-----------+------+----------+
Copy the code

Obviously, our data table record goes back to before the update statement.

Can we commit the transaction manually?

The answer is yes! You can commit manually using the COMMIT statement.

update students set age=100 where name="Super brother";commit;
- get
+----+--------+------+-----------+------+----------+
| id | name   | sex  | city      | age  | grade_id |
+----+--------+------+-----------+------+----------+
|  1 |Super brother| 男   |hangzhou|  100 |        2 |
|  2 |Meng Yang| 男   | null      |   10 |        1 |
|  3 |Good dream| 女   |Gully channel|   18 |        3 |
|  4 |Ed| 男   |zhengzhou|   18 |        2 |
|  5 |Xu god| 女   |We rarely|   88 |        4 |
+----+--------+------+-----------+------+----------+
- the rollback
rollback;
- get
+----+--------+------+-----------+------+----------+
| id | name   | sex  | city      | age  | grade_id |
+----+--------+------+-----------+------+----------+
|  1 |Super brother| 男   |hangzhou|  100 |        2 |
|  2 |Meng Yang| 男   | null      |   10 |        1 |
|  3 |Good dream| 女   |Gully channel|   18 |        3 |
|  4 |Ed| 男   |zhengzhou|   18 |        2 |
|  5 |Xu god| 女   |We rarely|   88 |        4 |
+----+--------+------+-----------+------+----------+
Copy the code

The data record is not rolled back to our previous state.

In addition to setting autoCOMMIT =0, there are other ways to manually start transactions:

• begin;
• start transaction;

Change autoCOMMIT to 1.

begin;
update students set age=200 where name="Super brother";rollback;
- get
+----+--------+------+-----------+------+----------+
| id | name   | sex  | city      | age  | grade_id |
+----+--------+------+-----------+------+----------+
|  1 |Super brother| 男   |hangzhou|  100 |        2 |
|  2 |Meng Yang| 男   | null      |   10 |        1 |
|  3 |Good dream| 女   |Gully channel|   18 |        3 |
|  4 |Ed| 男   |zhengzhou|   18 |        2 |
|  5 |Xu god| 女   |We rarely|   88 |        4 |
+----+--------+------+-----------+------+----------+
Copy the code

Similarly, start transaction is used in the same way.

Note: Transactions provide us with a rollback method, rollback; You can manually start a transaction using autoCOMMIT =0, begin, or start TRANSACTION. Once a transaction is committed, it cannot be rolled back.

2. Four CHARACTERISTICS of transactions (ACID)

Transactions have four characteristics:

• Atomicity A
• Consistency of C
• Isolation, I
• Persistence D

atomic

A transaction is the smallest unit and cannot be subdivided. If an operation fails, all operations in that transaction need to be restored to before the transaction was executed.

consistency

SQL statements must be guaranteed to succeed or fail simultaneously in the same transaction.

Isolation,

Transactions have several isolation levels:

• 1, the read uncommitted. Read uncommitted
• 2, the read committed; Reading has been submitted
• 3, repeatable read; Repeatable read (MySQL default isolation level)
• 4, the serializable; serialization

1, the read uncommitted ✅

If there is transaction A and transaction B, transaction A operates on the data, transaction A is not committed, but transaction B can see the result of operation A. Here’s an example 🌰:

First create a table user.

create table if not exists user(     
    id int,     
    name varchar(20),
    money int(200));Insert a piece of data
insert into user values(1, "elder brother",100);
insert into user values(2Good, "the dream",2000);
- get
+------+--------+-------+
| id   | name   | money |
+------+--------+-------+
|    1 |Super brother|   100 |
|    2 |Good dream|  2000 |
+------+--------+-------+
Copy the code

How do I view the isolation level of a database? Use SELECT to see @@global.transaction_isolation.

select @@global.transaction_isolation;
- get
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
Copy the code

Next, change the isolation level to Read uncommitted;

set global transaction isolation level read uncommitted;
-- select @@global.transaction_isolation
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+
Copy the code

Obviously, the isolation level has been changed to READ-UNCOMMITTED.

What can happen at the READ UNCOMMITTED isolation level?

Let’s continue with our example:

Dream good money 💰 more, want to give super brother transfer 200 yuan.

begin;
update user set money=money- 200. where name="Good dream"; updateuser set money=money+200 where name="Super brother";-- select
+------+--------+-------+
| id   | name   | money |
+------+--------+-------+
|    1 |Super brother|   300 |
|    2 |Good dream|  1800 |
+------+--------+-------+
-- You can see that the transfer succeeds
Copy the code

We open two terminals, “Super brother” and “dream jia” two users. Mengjia transferred 200 fast to Chaoge. Mengjia checked whether the transfer was successful:

select * from user;
- get
+------+--------+-------+
| id   | name   | money |
+------+--------+-------+
|    1 |Super brother|   300 |
|    2 |Good dream|  1800 |
+------+--------+-------+
Copy the code

Mengjia saw that the transfer was successful. Then super brother also want to check whether the transfer is successful.

select * from user;
+------+--------+-------+
| id   | name   | money |
+------+--------+-------+
|    1 |Super brother|   300 |
|    2 |Good dream|  1800 |
+------+--------+-------+
Copy the code

Super saw that the money had arrived. At this time, Mengjia’s house was quite bad. She rolled back.

rollback;
Copy the code

Then when the super brother goes to spend, will find that the money is not enough.

-- The transfer failed
+------+--------+-------+
| id   | name   | money |
+------+--------+-------+
|    1 |Super brother|   100 |
|    2 |Good dream|  2000 |
+------+--------+-------+
Copy the code

If both users are working on data, if transaction A is on, its data can be read by other data. But it is not committed and can be rolled back. The data after the rollback will also be read. This is called a “dirty read.” A “dirty read” is when one transaction reads another transaction that is not committed.

In practice, dirty reads are not allowed. Remember commit.

2, the read committed ✅

First, change the isolation level to Read COMMITTED.

set global transaction isolation level read committed;
- get
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-COMMITTED                 |
+--------------------------------+
Copy the code

What can happen at the Read COMMITTED isolation level?

Follow our example above. User A calculates the average value in the user table.

select avg(money) from user;
- get
+------------+
| avg(money) |
+------------+
|  1050.0000 |
+------------+
Copy the code

User B then inserts a piece of data into the user table.

insert into user values(1, "Meng Yang",1000);
- get
+------+--------+-------+
| id   | name   | money |
+------+--------+-------+
|    1 |Super brother|   100 |
|    2 |Good dream|  2000 |
|    1 |Meng Yang|  1000 |
+------+--------+-------+
Copy the code

At this point, user A recalculates the average.

select avg(money) from user;
- get
+------------+
| avg(money) |
+------------+
|  1033.3333 |
+------------+
Copy the code

Obviously, the average in the User table has changed.

Data inconsistency is found after reading the same table. This situation is called “unrepeatable read.”

3, repeatable read ✅

First change the isolation level repeatable Read.

set global transaction isolation level repeatable read;
- get
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
Copy the code

What’s the problem with repeatable Read isolation level?

So let’s take the example above and see what happens.

First, user A inserts a piece of data into the user table.

begin;
insert into user values(4, "Ed",10);
- get
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 |Super brother|   100 |
|  2 |Good dream|  2000 |
|  3 |Meng Yang|  1000 |
|  4 |Ed|    10 |
+----+--------+-------+
Copy the code

Select * from user b where user B is from;

- queries the user
select * from user;
- get
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 |Super brother|   100 |
|  2 |Good dream|  2000 |
|  3 |Meng Yang|  1000 |
+----+--------+-------+
Copy the code

As you can see, the exact data was not queried. Then we user A will commit the transaction:

- submit
commit;
- User A obtains the command
select * from user;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 |Super brother|   100 |
|  2 |Good dream|  2000 |
|  3 |Meng Yang|  1000 |
|  4 |Ed|    10 |
Copy the code

At this point, user B then queries:

- queries the user
select * from user;
- get
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 |Super brother|   100 |
|  2 |Good dream|  2000 |
|  3 |Meng Yang|  1000 |
+----+--------+-------+
Copy the code

Select * from id where id=4; User B inserts data into the user table:

Insert the same data
insert into user values(4, "Ed",10);
- get
ERROR 1062 (23000): Duplicate entry '4' for key 'user.PRIMARY'
Copy the code

Obviously, the data insertion failed. Transaction A commits data that transaction B cannot read. This phenomenon is called “phantom reading”.

Note: Close the terminal (user B) and restart the query. Then the magic read disappears.

4, the serializable ✅

Set the isolation level to serialization.

set global transaction isolation level serializable;
Copy the code

Similarly, we open two terminals, representing user A and user B; Then both users start a transaction at the same time;

- the user a
begin;
insert into user values(5, "dog",100);
- the user b
begin 
insert into user values(6, "ace",1800);
Copy the code

You can see that user A succeeds, but user B is stuck. After a certain amount of time, an error will be reported and the operation has timed out.

-- Operation timeout
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Copy the code

Next, user A commits the transaction, user B performs the insert operation, and the operation succeeds.

When a table is operated on by a transaction, write operations in other transactions cannot be performed. This phenomenon is called serialization.

Note: If transaction A is committed and transaction B has not timed out, transaction B is executed. Read UNCOMMITTED > READ COMMITTED > REPEATable Read > SerialIZABLE The default isolation level of mySql is REPEATable Read.

This is basically the end of MySQl transactions, there are more to check.

The final chapter

So much for the MySQL database. The article covers many things, including:

• The data type of MySQL
• SQL programming
• The constraint
• view
• The stored procedure
• The transaction
• .

At the same time in order to better write the article, I also listed a lot of examples. Are some of the more good and commonly used examples, the big guy can skip. Will not database or once the university learned to forget the students can learn, simple and easy to use. Follow the knock again, basically can learn. There’s a lot more to learn about MySQL, so I’m not going to continue here, but if you’re interested, you can go down and learn on your own. The volume of the article is too large, it is hard to avoid improper, please point out, thank you very much!