Mysql08 (transaction)

Business ： Represents a collection of operations , An integral , All or nothing , All or nothing

The beginning of a transaction depends on a DML sentence

/*
 The end of the business 
  1、 natural commit（ Make the data modification effective ） perhaps rollback（ Restore data to the previous state ）
  2、 Automatic submission , However, in general, auto submission should be turned off , Efficiency is too low 
  3、 After the user closes the session , Automatically commit transactions 
  4、 Rollback transactions when the system crashes or loses power , That is, restore the data to the previous state 
*/
insert into emp(empno,ename) values(2222,'zhangsan');
--commit;
--rollback;
select * from emp;

--savepoint   Save it 
-- When a set of operations contains multiple SQL sentence , But just want to make some of it work , Some part of it fails , At this point, you can use the savepoint 
-- At this time, if you need to roll back to a certain state, use  rollback to sp1;
delete from emp where empno = 1111;
delete from emp where empno = 2222;
savepoint sp1;
delete from emp where empno = 1234;
rollback to sp1;
commit;
/*
 Four characteristics of transactions ：ACID
   Atomicity ： Represents indivisible , A set of operations is either all successful , All or nothing , You can't cut it in the middle 
   Uniformity ： Finally, it is to ensure the consistency of data , When passing by N After multiple operations , The state of the data will not change （ Transfer accounts ）
           From one consistency state to another , That is, the data can't be disordered 
   Isolation, ： The correlation between transactions has no impact ,（ Isolation level ）
           Strict isolation will reduce efficiency , In some cases, in order to improve the execution efficiency of the program , The level of isolation needs to be reduced 
           Isolation level ：
             Read uncommitted 
             Read submitted 
             Repeatable 
             serialize 
           Data inconsistency ：
             Dirty reading 
             It can't be read repeatedly 
             Fantasy reading 
   persistence ： All data modifications must be persisted to the storage medium , No data loss due to application shutdown 
   Of the four characteristics , Which is the most critical ？
      All features are designed to ensure data consistency , So consistency is the ultimate pursuit 
      Consistency in transactions is through 、 Isolation, 、 Durability to guarantee 
      Lock mechanism ：
      In order to solve the problem of concurrent access , Data inconsistency , Need to lock data 
      When locking, you need to consider 《 Particle size 》 The problem of ：
          Object of operation 
             database 
             surface 
             That's ok 
      In general , The smaller the size of the lock , The more efficient , The larger the particle size , The less efficient  
             In the actual working environment , Most operations are row level locks   
*/