Hello everyone, I am your Yu Xiaoer. Today, W
Technology Github learning address: github.com/codeGoogler…
Programming books for programmers: github.com/codeGoogler…
Transaction characteristics:
Atomicity: The indivisibility of a transaction, the indivisibility of the logical units that make up the transaction.
Consistency: Data integrity remains consistent before and after a transaction is executed.
Isolation: Transaction execution should not be interrupted by other transactions.
Persistence: Data is persisted to the database once a transaction is completed.
View/Set the isolation level
SELECT @@TX_isolation from SET TX_isolation =’ XXX ‘
The isolation level of the transaction
If isolation is not considered, some security issues arise
Isolation: The execution of a transaction should not be interrupted by other transactions.
Dirty read: A transaction reads data that has not been submitted by another transaction, causing inconsistent query results. Unrepeatable read: A transaction reads update data that has been submitted by another transaction, causing inconsistent query results for multiple times. Virtual read/phantom read: one transaction reads insert data that another transaction has committed, resulting in inconsistent results for multiple queries.
Set the isolation level of the transaction:
Read Uncommitted: Dirty reads, unrepeatable reads, and virtual reads can occur. Read Uncommitted: Avoids dirty reads. Repeatable read is possible: avoid dirty read and repeatable read, but virtual read is possible. Serializable: Avoid dirty read, unrepeatable read, and virtual read.
The four isolation levels are demonstrated by example
Read uncommitted:
Transaction A: Starts the transaction and executes SELECT * FROM testtest, but does not commit the transaction
set tx_isolation='read-uncommitted';
start transaction;
SELECT * FROM testtest;
Copy the codeThe results show:
UPDATE testtest set age = 200 WHERE name = ‘zhangsan’; But the transaction is not committed
start transaction;
UPDATE testtest set age = 200 WHERE name = 'zhangsan';
Copy the codeTransaction A: Execute SELECT * FROM testtest again
The modified 200 is still read even though transaction B has not committed, and there is a dirty read!
Read Committed
Transaction A: Starts the transaction and executes SELECT * FROM testtest, but does not commit the transaction
UPDATE testtest set age = 200 WHERE name = ‘zhangsan’; But the transaction is not committed
start transaction;
UPDATE testtest set age = 200 WHERE name = 'zhangsan';
Copy the codeTransaction A: Execute SELECT * FROM testtest again
The modified 200 is not read without transaction B committing, avoiding a dirty read!
Transaction B: Commits the transaction.
Transaction A: Execute SELECT * FROM testtest again
In the case of transaction B commit, the modified 200 is read and an unrepeatable read occurs! (Multiple reads in the same transaction are inconsistent)
Repeatable Read mysql default
Transaction A: Starts the transaction and executes SELECT * FROM testtest, but does not commit the transaction
UPDATE testtest set age = 200 WHERE name = ‘zhangsan’; But the transaction is not committed
start transaction;
UPDATE testtest set age = 200 WHERE name = 'zhangsan';
Copy the codeTransaction A: Execute SELECT * FROM testtest again
The modified 200 is not read without transaction B committing, avoiding a dirty read!
Transaction B: Commits the transaction.
Transaction A: Execute SELECT * FROM testtest again
In the case of a transaction B commit, the modified 200 is not read, avoiding unrepeatable reads!
Start transaction B again, add a record, and commit the transaction
start transaction;
INSERT INTO testtest(name,age) VALUES ('wangwu','100');
COMMIT;
Copy the codeTransaction A: Execute SELECT * FROM testtest again
The new record is not read
Transaction A: Inserts the new record just added by transaction B
INSERT INTO testtest(name,age) VALUES ('wangwu','100');
Copy the code
Transaction B has already added and committed the transaction (name field has unique index). (Not when you query, but when you insert, it does exist, like an illusion).
Serializable
Transaction A: Starts the transaction and executes SELECT * FROM testtest, but does not commit the transaction
start transaction;
SELECT * FROM testtest;
Copy the codeUPDATE testtest set age = 200 WHERE name = ‘zhangsan’
Transaction B is found to be parked there with no execution until transaction A commits the transaction. In short, it places a shared lock on each read row. At this level, a lot of timeouts and lock contention can result.
supplement
The highest isolation level is Serializable and the lowest is Read Uncommitted. Of course, the higher the isolation level, the lower the execution efficiency. A level like Serializable locks a table (similar to Java multithreading locks) so that other threads can only wait outside the lock, so the isolation level you choose should depend on the actual situation. The default isolation level in the MySQL database is Repeatable Read.
In MySQL database, the above four isolation levels are supported. Repeatable Read is the default; In Oracle databases, only the Serializable level and Read COMMITTED level are supported. The default level is Read COMMITTED
Propagation behavior
1, PROPAGATION_REQUIRED: Create a new transaction if there is no transaction currently, PROPAGATION_REQUIRED: Join the transaction if there is one, this setting is the most commonly used.
2, PROPAGATION_NESTED: Executes within a nested transaction if a transaction currently exists. If there are no transactions currently, an operation similar to PROPAGATION_REQUIRED is performed
3, PROPAGATION_SUPPORTS: Supports the current transaction, if a transaction currently exists, join the transaction, if there is no transaction, execute as a non-transaction. ‘
4. PROPAGATION_MANDATORY: The current transaction is supported, PROPAGATION_MANDATORY. If a transaction exists, join it, and throw an exception if no transaction exists.
5, PROPAGATION_REQUIRES_NEW: Support the current transaction, create a new transaction, regardless of whether the current transaction exists.
PROPAGATION_NOT_SUPPORTED: Execute an operation non-transactionally, or suspend the current transaction if one exists.
7, PROPAGATION_NEVER: Execute non-transactionally, throw an exception if a transaction currently exists.
Extension: www.cnblogs.com/523823-wu/p…
Author: Anti – Mage
Link: blog.csdn.net/u013083284/… Source: CSDN
Finally, one of the most confusing points: What are Spring transactions? Whether the transaction is the same as the database
Essentially is the same concept, the spring transaction is the transaction of the database encapsulation, the final essence of the implementation or in the database, if the database does not support transactions,spring transactions are useless. The transaction of the database is simply open, roll back and close. Spring’s packaging of database transactions, the principle is to take a data connection, according to the transaction configuration of Spring, the operation of this data connection to the database for transaction open, roll back or close operation
Technology Github learning address: github.com/codeGoogler…
Programming books for programmers: github.com/codeGoogler…
On how to learn Java, on the one hand, we need to continue to learn, to learn the basic knowledge of solid, on the other hand, we should also realize that Java learning can not only rely on theory, more rely on practical operation, so we should practice more projects, learning in practice is the best way to learn. Many people don’t know how to learn at first. Here I have compiled some important technical articles into the Github open source project, hoping to give you some help. The project is: JavaCodeHub
In addition, I also sorted out some programming books for programmers and put them on Github. The projects are: ProgramBooks, if necessary, you can get them by yourself. Address: github.com/codeGoogler…
If Github access is too slow? I also put ProgramBooks on the code cloud
Finally, we will continue to use our official account “Terminal R&d Department” to recommend a high-quality technology-related article every day, mainly sharing Java related technology and interview skills. Our goal is to know what it is, why, lay a solid foundation, and do everything well! The main technology of the public super worth our attention.