Shulou(Shulou.com)06/01 Report--

Postgresql transaction processing and concurrency control:

Transaction is the basic unit of work in postgresql, which is a user-defined sequence of database operations. These operations are either done or not done at all, which is an indivisible unit of work.

In postgresql, the transaction manager is responsible for managing the modules of transaction running (modules are: lock manager and log manager)

The transaction manager is the hub of the transaction system, which processes the next transaction operation by receiving information.

The lock manager mainly provides all kinds of locks needed for concurrency control in the write phase of the transaction, thus ensuring various isolation levels of the transaction.

The log manager mainly records the state of transaction execution and the process of data changes.

After the transaction starts, all operations in the transaction are written to the transaction log. There are generally two types of transactions written to the log: one is for data operations, such as inserts, modifications, and deletions, which target large amounts of data; the other is for tasks, such as creating indexes. When canceling these transaction operations, the system automatically performs the reverse operation of this operation to ensure the consistency of the system. The system automatically generates a checkpoint mechanism. This checkpoint periodically checks the transaction log. If all transactions are completed in the transaction log, the transaction in the checkpoint transaction log is committed to the database and a checkpoint commit representation is made in the transaction log. If the transaction is not completed in the transaction log, the checkpoint does not commit the transaction in the transaction log to the database and makes an identification of the checkpoint uncommitted in the transaction log. Transaction recovery and checkpoints ensure the integrity and recoverability of the system.

1. Attributes of the transaction

A transaction is a series of operations performed as a single logical unit of work. A logical unit of work must have four attributes, namely atomicity, consistency, isolation, and persistence, or ACID for short, in order to become a transaction.

Atomicity: transactions must be atomic units of work; for data modifications, either all or none are performed.

Consistency: all data must be consistent when the transaction is completed.

Isolation: changes made by concurrent transactions must be isolated from changes made by any other concurrent transactions. When the transaction identifies the data, the state of the data is either before the second concurrent transaction is modified, or after the second transaction is modified, and the data of the intermediate state is not recognized. This is called serializability because it can reload the starting data and replay a series of transactions so that the state at the end of the data is the same as that in which the original transaction was executed.

Persistence: after the transaction is completed, the impact on the system is permanent. The modification will be maintained even in the event of a system failure.

2. Common statements for transaction block management:

In postgresql, a transaction is achieved by enclosing the SQL command with: begin and commit commands. Transaction blocks are statements that surround begin and commit. In postgresql9, the common transaction block management statements have the following meanings:

Start transaction: means to start a new transaction block.

Begin: initializes a transaction block.

Commit: indicates a committed transaction

Rollback: indicates that a rollback operation is performed when a transaction fails.

Set transaction: sets the characteristics of the current transaction and has no effect on subsequent transactions.

3. Concurrency control of postgresql

3.1. Dirty reading:

When the record read by one transaction is part of another transaction, there is no problem if the first transaction completes normally, and if the other transaction is not completed at this time, a dirty read occurs.

3.2, Phantom Reading:

Phantom reading occurs when a data performs an insert or delete operation and the data row happens to fall within the scope that a transaction is reading. For example, when the insert operation is executed while running the update statement, because a new record row is inserted, it is not locked and can be cherished.

3.3. Non-repeatable read:

If a transaction reads the same record more than once, but another transaction happens to modify the data in the middle of the two reads, the data read from the two reads will be different, and a non-repetitive read occurs.

3.2. transaction isolation level of postgresql:

"isolation level", "dirty reading", "phantom reading" and "non-repeatable reading"

Read unsubmitted may

It is impossible to read submitted. It is possible.

Is it possible that repeatable reading is impossible?

But serial reading is impossible.

In postgresql, any of the four possible transaction isolation levels can be requested. But internally, there are actually only two separate isolation levels, corresponding to read committed and serializable. That is, if you choose to read an uncommitted level, you are actually using a read committed. When choosing a repeatable readable level, you actually use serializability, so the actual isolation level may be more stringent than the chosen one. The reason postgresql offers only two isolation levels is that this is the only reasonable way to relate standard isolation levels to multi-version concurrency control architecture mapping.

The two isolation levels in postgresql are as follows:

①: read submitted:

Reading submitted is the default level in postgresql. When a transaction runs at this isolation level, an select query can only see data that has been committed before the query starts, not data that has not been committed or that has been committed by other transactions during query execution.

If two transactions are updating the same set of data, the second transaction needs to wait for the first transaction to commit or update rollback. If the first transaction commits, the system recalculates the query condition, and after the condition is met, the second transaction continues with the update operation; if the first transaction updates back, his job is ignored and the second transaction continues to update the originally discovered row.

②: serializable:

Serializability basically provides the strictest transaction isolation. This level simulates serial transaction execution, as if transactions would be executed serially one after another (rather than in parallel). However, applications that use this level must be prepared to restart transactions if serialization fails.

If two transactions are updating the same set of data, the serialized transaction will wait for the first transaction being updated to commit or roll back. If the first transaction commits, the serialized transaction rolls back and restarts the entire transaction from scratch; if the first transaction rolls back, its impact is ignored, and the serializable transaction can be updated on the meta-ancestor.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.