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