Transaction propagation
Propagation_required |
If there is no current transaction, create a new transaction, if one is already present, join the transaction. This is the most common choice. |
Propagation_supports |
Supports the current transaction and is executed in a non-transactional manner if no transaction is currently in use. |
Propagation_mandatory |
Throws an exception if no transaction is currently in use for the current transaction. |
Propagation_requires_new |
Creates a new transaction, suspending the current transaction if the transaction currently exists. |
propagation_not_supported |
Executes the operation in a non-transactional manner, suspending the current transaction if a transaction is currently present. |
Propagation_never |
Executes in a non-transactional manner, throwing an exception if a transaction is currently present. |
propagation_nested |
Executes within a nested transaction if the transaction is currently present. If there is currently no transaction, perform a similar operation as propagation_required. |
Transaction isolation
exception conditions for database concurrency operations:
1. Update lost (Lost update): Two transactions update one row of data at the same time but the second transaction fails to exit, resulting in two modifications to the data. This is the system does not perform any lock operation and therefore the concurrency transaction is not isolated.
2. Dirty Read (Dirty Reads): One transaction starts reading a row of data but another transaction has updated this data but is not able to commit in a timely manner. This is quite dangerous and it is possible that all operations are rolled back.
3. Non-repeatable read (non-repeatable Reads): One transaction repeats two times on the same row of data but gets different results. For example, there is another transaction that modifies and submits the row's data in the middle of a two-read session.
4. Two update issues (Second lost updates problem): Cannot read the exceptions repeatedly, There are two concurrent transactions that read the same row of data at the same time, then one of them modifies the commit and the other commits the modification, which causes the first write operation to fail.
5. Phantom Read (Phantom Reads): Also known as Phantom (Phantom). The transaction was queried two times during the operation, and the second query result contained data that did not appear in the first query (the same is not required for two query SQL statements) because there was another transaction inserting data during the two queries. to avoid several scenarios where there are 4 transaction isolation levels defined in a standard SQL specification, different isolation levels are different for transactions.
troubleshoot problems with transactional concurrency
1. Unauthorized read (READ UNCOMMITTED): Also known as uncommitted read. Allow dirty reads but do not allow updates to be lost, if one transaction has already started writing data, another transaction does not allow simultaneous writes, but allows reading this row of data . This isolation level can be achieved through an "exclusive write lock". The lowest level of transaction isolation, which guarantees that no physical corruption is read. Contrary to the read COMMITTED isolation level, it allows the reading of data that has been modified by other users but not yet committed.
2. Authorized read (read Committed): Also known as read-committed. Allows non-repeatable reads but does not allow dirty reads, transactions that read data allow other transactions to continue to access the row's data, but uncommitted write transactions will prevent other transactions from accessing the row . This can be achieved by "instantaneous shared read lock" and "Exclusive write lock", which is the default level of SQL Server. Under this isolation level, the SELECT command does not return data that has not yet been committed (Committed), and it cannot return dirty data.
3. Repeatable READ (Repeatable Read): Prevents non-repeatable reads and dirty reads. However, phantom data can sometimes occur, read data transactions will prohibit write transactions (but allow read transactions), write transactions prohibit any other transactions . This can be achieved through "shared read lock" and "Exclusive write lock", where the data read with the Select command will not be changed during the entire command execution. This option affects the performance of the system, and it is best not to use this isolation level for unnecessary situations.
4. Serial (Serializable): Also known as Serializable. Provides strict transaction isolation, which requires transactional serialization execution, and transactions can be executed one after the other, but not concurrently. If transaction serialization is not possible only through row-level locks, other mechanisms must be ensured that the newly inserted data is not accessed by the just executing query operation transaction. The highest level of transaction isolation, which is completely isolated between transactions. If transactions run at the serializable read isolation level, then any concurrent overlapping transactions can be guaranteed to be serial.
Isolation Level Update loss dirty read duplicate read phantom read
read N y y-y not authorized
authorization to read n n y y
REPEATABLE Read n n n Y
serial n n n N
Spring transaction propagation, isolation level
