PostgreSQL transaction processing and concurrency control

Tag: Select the complete sequence process date ready version to generate the calculation

PostgreSQL transaction processing and concurrency control:

A transaction is the basic unit of work in PostgreSQL and is a user-defined sequence of database operations. These operations are either done entirely or not, and are an inseparable unit of work.

In PostgreSQL, the transaction manager is responsible for managing the module in which the transaction runs (modules are: Lock manager and log manager)

A transaction manager is the backbone of a transactional system that handles the next transaction operation by accepting information.

The lock manager mainly provides the various locks required for write-phase concurrency control of transactions, thus guaranteeing the various isolation levels of the transaction.

The log manager primarily records the state of the transaction execution and the process of changing the data.

After the transaction begins, all operations in the transaction are written to the transaction log. There are generally two types of transactions written to the log: one is operations on data, such as insertions, modifications, and deletions, the objects of which are large amounts of data, and actions for tasks, such as creating an index. When these transactional operations are canceled, the system automatically performs an inverse operation of this operation to ensure system consistency. The system automatically generates a checkpoint mechanism. This checkpoint periodically checks the transaction log, and if the transaction is complete in the transaction log, the transaction in the checkpoint transaction log is committed to the database and a checkpoint submission representation is made in the transaction log. If the transaction is not completed in the transaction log, the checkpoint does not say that the transaction in the transaction log is committed to the database, and that a checkpoint does not commit the identity in the transaction log. The recovery and checkpoint of the transaction ensures the integrity and recoverability of the system.

1. Properties of a transaction

A transaction is a series of operations performed as a single logical unit of work. A logical unit of work must have 4 properties, namely, atomicity, consistency, isolation, and persistence properties, called the acid attribute, to be a transaction.

Atomicity: A transaction must be an atomic unit of work; For data modification, either all is executed or none is executed.

Consistency: When a transaction is complete, all data must be kept consistent.

Isolation: Modifications made by a concurrent transaction must be isolated from modifications made by any other concurrent transaction. When a transaction identifies data, the state in which the data is located is either the state before the second concurrent transaction is modified, or the state after the second transaction is modified, and the data in the middle State is not recognized. This is called serializable because it is able to reload the starting data and replay a series of transactions so that the state at the end of the data is the same state as the original transaction execution.

Persistence: After the transaction is complete, the effect on the system is permanent. This modification will persist even if a system failure occurs.

2. Common statements for Transaction block management:

In PostgreSQL, a transaction is accomplished by enclosing the SQL command with the: Begin and commit commands. A transaction block is a statement that encloses the between Begin and commit. In Postgresql9, common transaction block management statements have the following meanings:

Start transaction: Represents the beginning of a new transaction block.

Begin: Represents the initialization of a transaction block.

Commit: Represents a COMMIT transaction;

Rollback: A rollback operation is performed when a transaction fails.

Set transaction: Sets the characteristics of the current transaction, which has an effect on subsequent transactions.

3, the concurrency control of PostgreSQL

3.1, Dirty read:

When one transaction reads a record as part of another transaction, if the first transaction completes properly, there is no problem, and if another transaction does not complete at this time, a dirty read occurs.

3.2. Phantom reading:

A phantom read occurs when an INSERT or delete operation is performed on a data row that happens to be in the range that a transaction is reading. For example, the insert operation is performed while running the UPDATE statement because a new record line is inserted, so there is no lock and can be cherished.

3.3, non-repeatable READ:

If a transaction reads the same record more than once, but there is another transaction in the middle of the two reads that just modifies the data, the data that is read two times will be different, and a non-repeatable read occurs.

3.2. The transaction isolation level of PostgreSQL:

"The Isolation Level" "Dirty reading" "Magic reading" "Non-repeatable read"

READ UNCOMMITTED may be possible

Read Committed may not be possible

Repeatable reading is not possible

Serializable reading Impossible Impossible

In PostgreSQL, you can request any of the 4 possible transaction isolation levels. But internally, there are actually only two separate isolation levels, corresponding to: Read Committed and Serializable. That is, if you choose to read UNCOMMITTED levels, you are actually using Read committed. When you choose a repeatable read level, you are actually using serializable, so the actual isolation level may be stricter than the selection. The reason for PostgreSQL to provide only two isolation levels is that this is the only reasonable way to relate the standard isolation level to a multi-version concurrency control schema map.

The two isolation levels in PostgreSQL are as follows:

①: Read Committed:

Read Committed is the default level in PostgreSQL. When a transaction runs at this isolation level, a select query can see only data that has been committed before the query starts, and cannot see uncommitted data or data that has been committed by other transactions during query execution.

If two transactions are updating the same set of data, the second transaction waits for the first transaction to commit or the update to roll back. If the first transaction commits, the query condition is recalculated, the second transaction continues with the update operation, and if the first transaction is rolled back, his job is ignored and the second transaction continues to update the rows that were originally found.

②: Serializable:

Serializable basically provides the most stringent transaction isolation. This level emulates the serial transaction execution as if the transaction were to be executed serially (rather than in parallel) one after the other. However, applications that use this level must be prepared to restart the transaction when serialization fails.

If two transactions are updating the same set of data, the serialized transaction waits for the first transaction being updated to commit or rollback. If the first transaction commits, the serialized transaction is rolled back, the entire transaction is restarted from the beginning, and if the first transaction is rolled back, its impact is ignored, and the serializable transaction can be updated in that meta-ancestor.

PostgreSQL transaction processing and concurrency control