MySQL learning path - database transactions, views

Keywords: Database MySQL Big Data

1, What is a transaction

         Transaction refers to a program execution unit that accesses and updates various data in the database. MySQL transactions are mainly used to process data with large amount of operations and high complexity.

         (1) . four features (ACID): ★

                 (1) Atomicity / indivisibility: all operations in a transaction are either completed or not completed, and will not end in an intermediate link. If an error occurs during the execution of a transaction, it will be rolled back to the state before the start of the transaction, as if the transaction had never been executed

                (2) Consistency: the integrity of the database is not destroyed before and after the transaction. This means that the written data must fully comply with all preset rules, including the accuracy and serialization of the data, and the subsequent database can spontaneously complete the predetermined work

                (3) Isolation: the ability of a database to allow multiple concurrent transactions to read, write and modify their data at the same time. Isolation can prevent data inconsistency caused by cross execution when multiple transactions are executed concurrently. Transaction isolation is divided into different levels, including Read uncommitted, read committed, repeatable read, and Serializable

                (4) Durability: after the transaction is completed, the modification of data is permanent and will not be lost even if the system fails.

        Transaction control statement:


                Commit transaction: COMMT or COMMT WORK

                ROLLBACK transaction: ROLLBACK or ROLLBACK WORK

                Create transaction marker point: SAVEPOINT identifier

                Set transaction isolation level: SET TARNSACTION

                Rollback transaction marker point: ROLLBACK TO   identifier

                Delete transaction marker point: retain savepoint   identifier


                Start transaction → write SQL statement (insert, update, delete, mark point) → commit

         Function of marker points: when you have a large number of sql operations in a thing, if the efficiency of rollback (returning to the original state by default) is very low, you can consider returning to a marker point.

        (2) . transaction control statement:

                 mysql database automatically submits transactions by default, which is related to the variable autocommit. The default is 1, that is, it is turned on (ON). You can view it through the black window (cmd window). The effect is as follows:

         Command: show variables like 'autocommit';

          Note: whether autocommit is 1 or 0; After the start transaction, the commit data will take effect only when it is rolled back.

         When autocommit is 0; No matter whether there is a STARTTRANSACTION or not, the commit data will take effect only when it is rolled back.

create database if not exists db_0918;
use db_0918;
create table if not exists new_date(
    id int,
    name varchar(15),
    age int
-- Create a data table first
-- hypothesis autocommit When 0, insert data
insert into new_date values (1, 'AA', 17);
insert into new_date values (1, 'BB', 16); -- If the above statement is executed, the database will not submit data after inserting data, but it has been executed, as shown in Figure 1 and 2 below.
commit;  -- After this statement is run, the data will be submitted, as shown in Figure 3 and 4 below.


insert into new_date values (1, 'AA', 17);
rollback;  # Rollback will cancel the insertion of the above data
insert into new_date values (1, 'BB', 16);
commit;  # The running result only inserts a BB data

# After meeting rpllback, the effect of commit is the same
insert into new_date values (1, 'AA', 17);
insert into new_date values (1, 'BB', 16);
# The insertion result is the same

         Rollback effect:

insert into new_date values (1, 'AA', 17);
savepoint p1;  # Insert a restore point
insert into new_date values (1, 'BB', 16);
rollback to p1;  # Rollback to restore point
commit;  # There is only one data under this submission.

        How to set auto submit:

                Start mysql in the black window (cmd) and enter: SET AUTOCOMMIT=0;

                Note: after setting, only the current connection is allowed. When the database is disconnected and enters again, this value will be restored to the default value, i.e. 1.

          Note: if automatic submission is turned off in the black window, even if the data is inserted, the data does not exist in other people's database. The data is temporarily submitted in their own database. The data will be updated to the database only after you enter commit. This is the isolation mechanism of the database, that is, enter the inserted data in the black window, You can see the data in the black window, but the data refresh does not exist in the database unless you run commit, as follows:



         Scenario of rollback application:

                  For example, bank transfer, when a user transfers money to another user, it will be deducted from the transfer user and added to the receiving user's card. However, when either party fails, it will be rolled back, that is, the transaction fails.

        Dirty read:

                 Dirty reading, which occurs when reading uncommitted isolation levels. When a transaction is accessing data and making changes to the data, but the changes have not been committed to the database, another transaction also accesses the data and uses the data.

timetransferwithdraw money
1Start transaction
2Start transaction
3The query balance is 2000
4Withdraw 1000, the balance has been changed to 1000 (not submitted)
5The query balance is 1000 (this is dirty data)
6There is an error in withdrawal, the transaction is rolled back, and the balance returns to 2000
7Transferred to 2000, the balance is actually 3000 (dirty reading 1000 + 2000)
Normally, this account should be 4000

        Non repeatable reads:
                 Non repeatable reading, which occurs when reading the committed isolation level. The same data is read many times before and after, and the data content is inconsistent.

        Unreal reading:

                 Unreal reading, a phenomenon that occurs at the repeatable read isolation level. Read multiple times before and after to read the data inserted by other transactions.

timeTransaction ATransaction B
2For the first query, the total amount of data is 100
3Start transaction
4Other operations
5100 new data
6Commit transaction
7For the second query, the total amount of data is 200
Normally, the total amount of data read before and after transaction A should be the same

2, What is a view

         A view is a virtual table exported from one or more base tables (or views). A view cannot have the same name as the table name. Data is mostly used as queries, and data is generally not modified through views.


                1. Views can simplify user operations

                2. Views can increase security (Views generally use query statements)

                3. Views provide a certain degree of logical independence for refactoring the database

        View operation:

                Create view: create view name as SQL statement (mostly select);

                Modify view: alter view name as SQL statement;

                Delete view: drop view name;

        Code section: create a table first

create database if not exists db_0918;
use db_0918;
create table if not exists People(
    id int not null auto_increment primary key,
    age int,
    sid char(20),
    sex bit,
    name char(20),
    isDelete bit default 0
) ;

        Insert partial data:

insert into People(age,sex,name) values (18, 1, 'Cleo');
insert into People(age,sid,sex,name) values (18, '', 1, 'Abra');
insert into People(age,sex,name) values (36, 1, 'Louis'),(20, 1, 'Burt'),(30, 1, 'Mason'),(30, 1, 'lilia');
insert into People(age,sex,name) values (34, 1, 'Jane');

        Create view:

create view VPeople as select * from People where id>3;  -- Filter out id Information greater than 3 and create a view VPeople

select * from VPeople where age > 30;  -- Exhibition id>3 Information older than 30
select * from (select * from People where id>3) as stu where age > 30;  -- Equivalent to the above query, so the view query is simpler in this case.

        Modify view:

alter view VPeople as
    select * from People where like 'A%';  -- Query to A First name
select * from VPeople;  -- Presentation view

          Delete view:

drop view VPeople;  -- Delete view

         Views are mostly used for statements with high query frequency or scenes with complex logic. You can consider establishing views.

         For example: website home page data; Filter at the top of the site.


         Storage process:

                 Stored procedure (StoredProcedure) is a large set of database systems in which a set of SQL statements for specific functions is stored in the database. After the first compilation, the call does not need to be compiled again. The user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters). Stored procedure is an important object in database.

        Syntax (similar to SQL writer):

                 create procedure stored procedure name ()


                         sql statement set


                call stored procedure name ();   -- Calling process

-- Create a file named dba Library files, in dba Create a file named tb1 Table with id,name These two fields. Create a file named ad1 Stored procedures, ad1 The function of a stored procedure is to insert three records into tb1 In the table.
create database if not exists dba;
use dba;
create table if not exists tb1(
    id int,
    name varchar(255)
delimiter $$  -- Modify the Terminator (this terminator can be any, usually two $End), or you'll think;The statement is a whole to report an error
create procedure ad1()  -- Define storage process
    insert into tb1(id, name) values (1, 'Joke');
delimiter ;  -- When you're finished, reply this terminator as;

call ad1()  -- Call statement

        Extension, using the while loop to insert three data:

create database if not exists dba;  -- Create Library
use dba;  -- Use library
create table if not exists tb1(  -- Create table
    id int,
    name varchar(255)

delimiter $$  -- Defining symbols
create procedure ad1()  -- Define storage
    declare i int default 1;  -- Declare a variable i,Integer type. The default value is 1
    while (i < 3) do
        insert into tb1(id, name) VALUES (i, 'Andy');
        set i = i + 1;  -- Every run i value+1
        end while;
end; $$
delimiter ;  -- Set back to end symbol

call ad1(); -- call

3, Trigger

4, Functions

Posted by Eggzorcist on Sun, 19 Sep 2021 04:48:15 -0700