1 transaction

[getting started with database] power node mysql basics 03 There is an overview of the transaction

1.1 general

Transactions can guarantee the atomicity of multiple operations, either all successful or all failed. For the database, the transaction guarantees that the batch DML is either all successful or all failed. Transactions have four characteristics ACID

• Atomicity
  All operations in the whole transaction must be completed (or cancelled) as a unit.
• Consistency
  All transactions require that in the same transaction, all operations must succeed or fail at the same time to ensure data consistency.
  For example, if A transfers 100 yuan to B, A's account must be reduced by 100 yuan and B's account must be increased by 100 yuan. If the system goes down after A transfers 100 blocks to B, A reduces 100 blocks, but B does not increase 100 blocks, which is the inconsistency of data. It's kind of like the conservation of matter.
• Isolation
  One transaction will not affect the operation of other transactions.
• Durability
  After the transaction is completed, the changes made by the transaction to the database will be permanently saved in the database and will not be rolled back.

1.2 isolation level of transactions

1.2.1 concurrent exceptions

• when multiple clients access the same table concurrently, the following consistency problems may occur:

• Dirty Read
  Dirty reading occurs when a transaction starts reading a row of data, but another transaction has updated the data but failed to commit in time.
  

• Non repeatable read
  In the same transaction, the same read operation produces different results before and after reading the same data, which is non repeatable reading.
  

• Phantom Read
  Phantom read refers to a new row that has not existed before in the same transaction due to the submission of other transactions.
  

• Type I missing updates
  The rollback of a transaction results in the loss of updated data of another transaction
  

• Type 2 missing updates
  The commit of a transaction results in the loss of updated data of another transaction
  

1.2.2 four isolation levels

InnoDB implements four isolation levels to control the changes made by transactions and announce the changes to other concurrent transactions:

• Read uncommitted has not been submitted
  Causes: dirty reading
• READ COMMITTED (READ COMMITTED)
  Solved: dirty reading
  Resulting in: non repeatable reading
  After the transaction is started, three pieces of data are read for the first time. The current transaction has not ended yet. It is possible that four pieces of data are read for the second time. 3 is not equal to 4, which is called non repeatable reading.
• REPEATABLE READ can't be read after it is committed. What is always read is the data when the transaction was just started
  This isolation level is the default setting of InnoDB. This is the default transaction isolation level in mysql
  Solved: non repeatable reading
  Led to: unreal reading
  The transaction starts at 9:00 in the morning. As long as the transaction does not end, the data read will still be the same at 9:00 in the evening. What I read is an illusion. Not absolutely true.
• SERIALIZABLE [serialization]
  This is the highest isolation level and the lowest efficiency. Solved all the problems. This isolation level indicates that transactions are queued and cannot be concurrent. The data read every time is the most real and the efficiency is the lowest.
  
  

1.3 isolation implementation mechanism

1.3.1 lock

Pessimistic locks come with the database
Pessimistic locks are pessimistic about things. They believe that there will be problems if they are concurrent. Since there will be problems, they should lock the data in advance

S hared lock

After transaction A adds A shared lock to A data, other transactions can only add A shared lock to the data, but cannot add an exclusive lock.

After the common phase lock is added, only data can be read, and data cannot be retrieved

Exclusive lock (X lock)

After transaction A adds an exclusive lock to A data, other transactions cannot add A shared lock or an exclusive lock to the data.

The exclusive lock can be read and changed

1.3.2 lock

Optimistic locks need to be defined by themselves
Optimistic lock is optimistic about things. It believes that even if it is concurrent, there will be no problem. Assuming it will not have a problem, it should be read and written. When the data is read and needs to be changed after calculation, check whether the data has been changed. If the data is changed, it means that someone has changed the data during the processing, then give up the operation; Otherwise, no one will submit the data

How to identify whether the data has changed?
Add a field to a table, either a timestamp or a version number
Before anyone updates the data, check whether the timestamp or version number has changed. If it has changed, give up the update. If it has not changed, submit the update and modify the timestamp or version number

2 Spring transaction management

Official website documents: https://docs.spring.io/spring-framework/docs/current/reference/html/data-access.html#spring-data-tier
Links may fail and can be found in the following order:



When Spring does transaction management for any database, the API is unified
Specifically, there are two ways to manage transactions

1. Declarative transaction: there is no need to write logic. It can be used only by configuring the method in the xml configuration file or through annotation

2. Programming transaction: the Transaction Template class is required for programming

There are two ways to manage affairs. Usually, the first one is preferred, which is simple; If the business to be processed is complex and only a small part of the transactions in the middle want to be managed, use the second method

2.1 declarative transactions

demo simulates a certain business, so write the code in the business layer

Make up a requirement: after registering a user, automatically send a post to the user: new people check in. A business completes two new operations (new users and new posts), so it is necessary to ensure the transaction of the business

2.1.1 Service example code

@Service
public class DemoService {
    @Autowired
    private UserMapper userMapper;
    @Autowired
    private DiscussPostMapper discussPostMapper;


    //isolation = Isolation.READ_COMMITTED: the isolation level is read committed
    //propagation mechanism a calls b
    //REQUIRED supports the current transaction A. if it does not exist, a new transaction b is created
    //REQUIRES_NEW creates a new transaction b and pauses the current transaction
    //NESTED if transaction a currently exists, it is NESTED in transaction a (b has independent commit and rollback); Otherwise, it is the same as REQUIRED
    @Transactional(isolation = Isolation.READ_COMMITTED,propagation = Propagation.REQUIRED)

    public Object demo(){
        //New user
        User user = new User();
        user.setUsername("alpha");
        user.setSalt(CommunityUtil.generateUUID().substring(0,5));
        user.setPassword(CommunityUtil.md5("123"+user.getSalt()));
        user.setEmail("alpha@qq.com");
        user.setHeaderUrl("http://image.nowcoder.com/head/99t.png");
        user.setCreateTime(new Date());

        userMapper.insertUser(user);

        //New post
        DiscussPost post = new DiscussPost();
        post.setUserId(user.getId());
        post.setTitle("hello");
        post.setContent("hi");
        post.setCreateTime(new Date());
        discussPostMapper.insertDiscussPost(post);

        //Human error, see if the data can be rolled back
        Integer.valueOf("abc");


        return "ok";
    }
}

2.1.2 testing

@RunWith(SpringRunner.class)
@SpringBootTest
@ContextConfiguration(classes = CommunityApplication.class)
public class TransactionTests {
    @Autowired
    private DemoService demoService;

    @Test
    public void testSave1(){
        System.out.println(demoService.save1());
    }
}

report errors

If it is not inserted, the data is rolled back

2.2 programmatic transactions

@Autowired
    private TransactionTemplate transactionTemplate;
    public Object save2(){
        //Set isolation level
        transactionTemplate.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED);
        //Set propagation mechanism
        transactionTemplate.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);

        //Execute SQL access transaction
        return transactionTemplate.execute(new TransactionCallback<Object>() {
            @Override
            public Object doInTransaction(TransactionStatus transactionStatus) {
                //New user
                User user = new User();
                user.setUsername("beta");
                user.setSalt(CommunityUtil.generateUUID().substring(0,5));
                user.setPassword(CommunityUtil.md5("123"+user.getSalt()));
                user.setEmail("beta@qq.com");
                user.setHeaderUrl("http://image.nowcoder.com/head/999.png");
                user.setCreateTime(new Date());

                userMapper.insertUser(user);

                //New post
                DiscussPost post = new DiscussPost();
                post.setUserId(user.getId());
                post.setTitle("nihao");
                post.setContent("11");
                post.setCreateTime(new Date());
                discussPostMapper.insertDiscussPost(post);

                Integer.valueOf("abc");

                return "ok";
            }
        });

    }

    @Test
    public void testSave2(){
        System.out.println(demoService.save2());
    }

Summary: there are two ways to manage affairs. Usually, the first one is preferred, which is simple; If the business to be processed is complex and only a small part of the transactions in the middle want to be managed, use the second method