Detailed explanation of the transaction processing function of the PostgreSQL Client

The target of PostgreSQL is very similar to that of the current MySQL database. So what are the advantages of PostgreSQL? This article will be explained to you.

This article introduces the basic concepts of PostgreSQL's transaction processing function, and explains how to execute various transactions through the PostgreSQL client and from the PHP script. Through this article, you will learn what transactions are, how PostgreSQL implements them, and how to use transactions in your PHP application.

1. What is a transaction?

Transactions can be viewed as an ordered set of database operations. These operations should be treated as a whole. That is, when all operations in the set are successful, the transaction is considered successful, otherwise, the transaction is considered unsuccessful even if only one operation fails. If all the operations are successful, the transaction will be committed, and modifications made to the transaction will be used by all other database processes. If the operation fails, the transaction will be rolled back, and all modifications made by all completed operations in the transaction will be revoked. Modifications made during a transaction before the transaction is committed are only available to processes that own the transaction. The reason for doing so is to prevent other threads from rolling back the transaction after using the data modified by the transaction, resulting in data integrity errors.

• PostgreSQL 8.2 Overview
• The new version of PostgreSQL improves Oracle compatibility.
• PostgreSQL 8.4 released the new query and monitoring tool
• PostgreSQL vs. InnoDB multi-version Concurrency Control
• Founder of PostgreSQL: MySQL recession is inevitable

The transaction function is the key to the enterprise database, because many business flows are composed of multiple steps. Next we will take online shopping as an example to describe. During the checkout, the customer's shopping cart will be compared with the existing inventory to ensure that there is inventory. Next, the customer must provide the billing and delivery information, and then check whether the corresponding credit card is available and deduct the fee from it. Then, you need to deduct the corresponding quantity from the product inventory list. If the inventory is insufficient, you should also send a notification to the purchasing department. In these steps, as long as one error occurs, all modifications should not take effect. If the customer still deducts money from the customer's credit card without stock, the customer will be angry and the problem will be serious. Similarly, when a credit card is invalid as an online merchant, you certainly do not want to deduct the number of items selected by the customer from the inventory list, or send a purchase notification accordingly.

The transactions mentioned here must meet four requirements:

Atomicity: all the steps of the transaction must be successful. Otherwise, no step will be submitted.

Consistency: all the steps of the transaction must be successful. Otherwise, all data will be restored to the State before the start of the transaction.

Isolation: before the transaction is completed, all executed steps must be isolated from the system.

Persistence: All submitted data must be properly stored and restored to a valid State in case of system failure.

The transaction support function of PostgreSQL fully complies with the above four basic principles (sometimes referred to as ACID), so as to effectively ensure the integrity of the database.

Ii. PostgreSQL transaction isolation

PostgreSQL supports transactions through multi-version concurrency control or MVCC. That is to say, when a transaction is processed, it will see its own database snapshot, rather than the actual status of the underlying data. This makes it impossible for any given transaction to see some modifications made to the data by other started but not committed transactions. This principle is called transaction isolation.

The SQL standard specifies three attributes to determine which level of a transaction is at level 4 isolation. These attributes are as follows:

Dirty read: one transaction reads data written by another uncommitted parallel transaction.

Unrepeatable read: when a transaction reads the previously read data again, it finds that the data has been modified by another committed transaction.

Phantom read: when a transaction executes a query again, it returns a set of rows that meet the query conditions and finds that these rows have changed due to other recently committed transactions.

These three conditions determine the isolation level of a transaction. All four levels are shown in table 1.

Table 1 Standard SQL transaction isolation level

PostgreSQL allows you to request any of the four possible transaction isolation levels. However, there are actually only two isolation levels available internally, which correspond to read committed and serializable respectively. If you choose the level of read uncommitted, you actually use the Read committed. When you choose the Repeatable read level, you actually use serializable, therefore, the actual isolation level may be stricter than you choose. Although this seems to be contrary to our intuition, the SQL standard does allow this, because the four isolation levels only define which phenomena cannot happen, but do not define that phenomenon, therefore, except for the unallowed transaction features, all features are allowed. For example, if you request the Repeatable read mode, this standard only requires that you are not allowed to read dirty data or re-read data, but does not require you to allow phantom read. Therefore, the serializable transaction mode meets the requirements of the Repeatable read mode, even if it is not fully consistent with the definition. Therefore, you should know exactly that when you request the read uncommitted mode, what you actually get is the Read committed mode. When you request the repeat mode, what we get is the serializable mode. You should also be aware that by default, if you do not request a specific isolation level, you will get the Read committed isolation level.

Next, let's take a look at the main differences between Read committed and serializable. In read-committed mode, only the data submitted before the start of the SELECT query can be seen, but the uncommitted data can never be seen, or other parallel transaction Commit Changes During query execution; however, SELECT does see the results of previous updates in the same transaction, even if they have not been committed. In fact, a SELECT query shows a snapshot of the database at the beginning of the query. Note that two adjacent SELECT commands may display different data, even if they are in the same transaction, because other transactions will be committed when the first SELECT statement is executed. When a transaction is serializable, a SELECT query can only see the data submitted before the transaction starts, but never uncommitted data or modifications committed by other parallel transactions in the transaction execution; however, the SELECT statement does show the effect of the previous update in the same transaction, even if the transaction has not been committed. This behavior is not the same as the Read committed level. Its SELECT statement displays the snapshot at the beginning of the transaction, rather than the snapshot at the beginning of the current query in the transaction. In this way, the SELECT command after a transaction always displays the same data. This means that the SELECT command after the next transaction in the Read committed mode can view different data, but the same data is always seen in serializable mode.

For the above differences, please be clear. Although it seems a little complicated, it is easy to understand as long as we grasp two key points: First, PostgreSQL runs the transaction concurrently, that is, when a transaction is executed, it does not prevent another transaction from performing operations on the same data. Second, pay attention to the concept of snapshot. Data snapshots are operated before the transaction is committed, rather than the database itself. At the same time, pay attention to the time when snapshots are used at different isolation levels-snapshots before the transaction starts, or the snapshot before the start of the transaction internal operation? I think we can grasp the differences between different isolation levels as long as we grasp the above points.

The basic concepts of transactions are introduced above. Next we will demonstrate how to use transactions in the PostgreSQL client.

3. Create an example table

The following describes the transaction concept through a specific online transaction application. To this end, we need to first create two tables in the database named company for this example program: participant ant and trunk. In addition, we will introduce the usage and structure of each table. After creating a table, we also need to fill in some sample data for them, as shown below.

First, we will create a special account table to store information about the participants, including their names, email addresses, and available cash:

CREATE TABLE participant (  
participantid SERIAL,  
name TEXT NOT NULL,  
email TEXT NOT NULL,  
cash NUMERIC(5,2) NOT NULL,  
PRIMARY KEY (participantid)  
 );  
CREATE TABLE participant (  
participantid SERIAL,  
name TEXT NOT NULL,  
email TEXT NOT NULL,  
cash NUMERIC(5,2) NOT NULL,  
PRIMARY KEY (participantid)  
 ); 
Then, create the trunk table. This table stores information about all the items of the participant, including the owner, name, description, and price:

 
CREATE TABLE trunk (  
trunkid SERIAL,  
participantid INTEGER NOT NULL REFERENCES participant(participantid),  
name TEXT NOT NULL,  
price NUMERIC(5,2) NOT NULL,  
description TEXT NOT NULL,  
PRIMARY KEY (trunkid)  
); 
The tables used are all created. Next we will add sample data. For simplicity, we have added only two participants, Tom and Jack, and added a small number of items to the trunks table, as shown below:

 
 INSERT INTO participant (name,email,cash) VALUES 
('Tom','Tom@example.com','1100.00');  
INSERT INTO participant (name,email,cash) VALUES 
('Jack','Jack@example.com','1150.00');  
 INSERT INTO trunk (participantid,name,price,description) VALUES 
 (1,'Linux CD','1.00','Complete OS on a CD'); INSERT INTO trunk (participantid,  
 name,price,description) VALUES 
 (2,'ComputerABC','12.99','a book about computer!');  
 INSERT INTO trunk (participantid,name,price,description) VALUES 
 (2,'Magazines','6.00','Stack of Computer Magazines'); 
Iv. Simple example application
In order to let readers understand the transaction running mechanism, we can run our sample program from the command line. Our example program demonstrates how two traders exchange items in cash. Before examining the code, let's take a look at the pseudocode that is easier to understand:

1. The participant Tom requests an item, such as ComputerABC in the virtual storage box of the participant Jack.

2. The participant Tom transferred $12.99 in cash to Jack's account. The result is that the amount of cash in Tom's account is less than 12.99, and the amount of cash in Jack's account is increased by 12.99.

3. Change the owner of ComputerABC to the participant Tom.

As you can see, each step in this process is critical to the overall success of the transaction. Therefore, we must ensure that our data will not be damaged due to the failure of one step. Of course, the real scenario is much more complicated than here, for example, it is necessary to check whether the buyer has enough cash. However, for the sake of simplicity, we ignore some details, so that the reader can focus on the subject of transactions.

We can submit the start transaction command to start transaction processing:

 
company=# START TRANSACTION;  
START TRANSACTION 
Note that start transaction also has an alias, that is, the BEGIN command. Although both can complete this task, we recommend that you use the latter because it complies with SQL specifications. Next, deduct $12.99 from Tom's account:

 
company=# UPDATE participant SET cash=cash-12.99 WHERE participantid=1;  
UPDATE 1 
Then, add $12.99 to Jack's account:

 
company=# UPDATE participant SET cash=cash+12.99 WHERE participantid=2;  
UPDATE 1 
Then, transfer ComputerABC to Tom:

 
company=# UPDATE trunk SET participantid =1 WHERE name='ComputerABC' AND 
company-# participantid=2;  
UPDATE 1 
Now we have completed a transaction. Next we will introduce another feature of PostgreSQL: savepoint. Note that the Savepoint function is introduced from PostgreSQL 8.0.0. Therefore, if you are using PostgreSQL earlier than this version, you cannot use the commands described below. A Savepoint is like a bookmarkdonefile of a transaction. We can set a point in a transaction to roll back to the saved point in case of a transaction error. We can submit a save point as follows:

 
company=# SAVEPOINT savepoint1;  
SAVEPOINT 
After submitting the save point, we can continue to execute various statements. To demonstrate the save point function, if we want to check the modifications made to the particle ant table, but the query command misspelled the name of the particle ant table:

 
company=# SELECT * FROM particapant;  
ERROR: relation "particapant" does not exist 
Note: For PostgreSQL versions earlier than version 8.0.0, the entire transaction must be rolled back. If we execute this query without setting the storage point, we will have to roll back the entire transaction because of a single error in the transaction. Even if we correct this error, PostgreSQL won't let us continue the transaction:

 
company=# SELECT * FROM participant;  
ERROR: current transaction is aborted, commands ignored until end of transaction block 
However, because we have submitted a save point, we can roll back to this save point, that is, to make our transaction back to the status before the error:

 
company=# ROLLBACK TO savepoint1;  
ROLLBACK 
Note: spelling errors are very annoying. However, for PostgreSQL 8.1, the client psql has a \ reseterror option that can automatically set the storage point and roll back when an error occurs.

We can now perform queries within our transactions, as if an error had occurred at all. Next we will take some time to check the participating ipant table to ensure that the correct amount of cash is credited to the debit and credit.

 
company=# SELECT * FROM participant; 
Will return:

 
participantid | name | email | cash  
---------------+--------+--------------------+--------  
1 | Tom | Tom@example.com | 1087.01  
2 | Jack | Jack@example.com | 1162.99  
(2 rows) 
In addition, we also need to check the trunk table to see if the owner of ComputerABC has modified the table. However, it should be noted that, because PostgreSQL enforces the ACID principle, this change is only available for the current connection that executes the transaction. To illustrate this, we start another psql client and log on to the company database again. When we view the participant table, we will find that the corresponding cash value of both parties has not changed. This is caused by isolation in ACID. Other connections do not see any changes made during transaction processing unless we have committed the changes.

How can I cancel a transaction? Return to the first client window and use the ROLLBACK command to cancel these changes:

 
company=# ROLLBACK;  
ROLLBACK 
Now, run the SELECT command again:

 
company=# SELECT * FROM participant;  
This returns:  
CHAPTER  
 
participantid | name | email | cash  
---------------+--------+--------------------+--------  
1 | Tom | Tom@example.com | 1100.00  
2 | Jack | Jack@example.com | 1150.00  
(2 rows) 
Note that the amount of cash on both sides of the transaction has been restored to the original value. Check the trunk table and you will see that the owner of ComputerABC has not changed. Repeat the previous process again. This time, you use the COMMIT command instead of the rollback operation to submit changes. Once the transaction is committed, return to the second client and view the two data tables. You will find that the submitted changes are available.

It must be noted that any modifications made to the data between transactions will not take effect until the COMMIT or ROLLBACK command is submitted. This means that if the PostgreSQL server crashes before committing these changes, these changes will not happen. to make these changes happen, You must restart the transaction.

V. Summary

This article introduces the transaction function of PostgreSQL and explains how to use transactions through the PostgreSQL client. By reading this article, readers will learn what transactions are and how PostgreSQL implements them. In the next article, we will introduce how to use transactions in your PHP application.