I. Transaction concept
Transaction, a very important concept in our relational database, conforms to ACID properties. A program execution Unit (Unit) consisting of a set of SQL statements that either Commit or Rollback.
Four characteristics of transactions
The ACID properties
Atomicity
Consistency
Isolation
They offer “Durability”
Mp.weixin.qq.com/s?__biz=MzI…
The isolation level of the transaction
The following problems occur when concurrent transactions are not isolated.
- Problem one: dirty reading
Dirty reads are when a transaction reads uncommitted data from another transaction.
- Problem two: not repeatable
Non-repeatable reads refer to reading a row of data in a table within a transaction, with different results. The difference between an unrepeatable read and a dirty read is that a dirty read reads the dirty data that has not been committed in a previous transaction. An unrepeatable read reads the data that has been committed in a previous transaction.
- Problem three: Phantom reading (virtual reading)
Phantom read (virtual read) refers to a transaction that reads data inserted by another transaction, resulting in a different number of reads.
The isolation levels for transactions are:
- Read uncommitted
- Read Committed
- Repeatable Reads
- Serializable
SESSION: Sets the transaction isolation level of the current SESSION. If GLOBAL or SESSION is not specified in the statement, the default value is SESSION
Repeatable Read (Repeatable Read)
PS: In the actual development process, such locking behavior (when using transaction isolation level, the underlying problem can be solved by locking mechanism), is very costly system performance. Pessimistic and optimistic locks will be covered in the next section
Juejin. Cn/post / 684490…
Pessimistic lock and optimistic lock
define
Pessimistic locks, as the name suggests, are intensely exclusive and exclusive. It refers to the conservative attitude towards data being modified by other transactions, including the current transactions of the system, as well as transactions from external systems, and therefore keeping the data locked throughout the data processing process. The realization of pessimistic lock, often rely on the locking mechanism provided by the database (only the locking mechanism provided by the database layer can truly ensure the exclusivity of data access, otherwise, even if the locking mechanism is implemented in this system, it can not guarantee that the external system will not modify the data).
Optimistic locking mechanism adopts a more relaxed locking mechanism. Optimistic locking is a mechanism to avoid data processing errors caused by database illusionary reading and long service processing time. However, optimistic locking does not deliberately use the locking mechanism of the database itself, but ensures the correctness of data according to the data itself.
Optimistic locking is generally implemented in the following two ways:
- Using Version numbers is implemented using the Version logging mechanism, which is the most common implementation of optimistic locking.
- The second implementation of optimistic locking using timestamps is similar to the first
Applicable scenario
Pessimistic locking
This method is suitable for scenarios with frequent write operations. If a large number of read operations occur, locks are added during each read operation, which increases the overhead of locks and reduces the throughput of the system.
Optimistic locking
This method is suitable for scenarios where read operations are frequent. If a large number of write operations occur, data conflicts are more likely. To ensure data consistency, the application layer needs to constantly retrieve data, which increases a large number of query operations and reduces the throughput of the system.
Mp.weixin.qq.com/s/zUIS-FDxZ…