Shulou(Shulou.com)05/31 Report--

This article mainly introduces what is the transaction in PostgreSQL. It is very detailed and has a certain reference value. Friends who are interested must finish it!

I. what is a transaction?

Transactions can be regarded as an ordered set of operations, which should be treated as a whole, that is, when all operations in the set are successful, the transaction is considered successful, otherwise, even if only one of the operations fails, the transaction will also be considered unsuccessful. If all operations are successful, the transaction is committed and its changes can be used by all other database processes. If the operation fails, the transaction is rolled back and all changes made by completed operations within the transaction are undone. Before a transaction commits, changes made during a transaction are available only to the process that owns the transaction. This is done to prevent the transaction from being rolled back after other threads use the data modified by the transaction, resulting in a data integrity error.

Transaction functionality is the key to an enterprise database, because many business processes are made up of multiple steps, so let's take online shopping as an example. At checkout, the customer's shopping cart is compared with existing inventory to make sure it is in stock. Next, the customer must provide charging and delivery information, and then need to check whether the corresponding credit card is available and deduct money from it. Then, the corresponding quantity needs to be deducted from the product inventory, and if the inventory is insufficient, notice should be given to the purchasing department. In these steps, as long as an error occurs in one step, none of the changes should take effect. If the money is deducted from the customer's credit card when there is no stock, the customer will be very angry and the problem will be very serious. Similarly, as an online merchant, when the credit card is invalid, you certainly do not want to deduct the number of items selected by the customer from the inventory or issue a purchase notice accordingly.

The affairs we are talking about here must meet four major requirements:

Atomicity: all steps of the transaction must be successful; otherwise, no steps will be committed.

L consistency: all steps of the transaction must be successful; otherwise, all data will be restored to the state it was before the transaction started.

Isolation: all performed steps must be isolated from the system until the transaction is completed.

L persistence: all submitted data must be properly saved by the system and be restored to a valid state in the event of a system failure.

The transaction support function of PostgreSQL fully follows the above four basic principles (sometimes referred to as ACID for short), which can effectively ensure the integrity of the database.

II. Transaction isolation of PostgreSQL

PostgreSQL's transaction support is achieved through what is commonly known as multi-version concurrency control or MVCC methods, that is, every time a transaction is processed, it sees its own database snapshot rather than the actual state of the underlying data. This makes it impossible for any given transaction to see some of the changes made to the data by other transactions that have been started but not yet committed. This principle is called transaction isolation.

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

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

L cannot be read repeatedly: when a transaction re-reads previously read data, it is found that the data has been modified by another committed transaction

L Phantom reading: when a transaction re-executes a query, it returns a set of rows that meet the query criteria and finds that these rows have changed due to other recently committed transactions.

These three cases determine the isolation level of a transaction, and all four levels are shown in Table 1.

Table 1 SQL standard transaction isolation level

# FormatImgID_0#

PostgreSQL allows you to request any of the four possible transaction isolation levels. Internally, however, there are actually only two isolation levels available, corresponding to read committed and serializable. If you choose a read uncommitted level, you actually use a read committed level, and when you choose a repeatable read level, you actually use serializability, so the actual isolation level may be more stringent than the one you choose. Although this may seem counterintuitive, the SQL standard does allow it, because the four isolation levels only define which phenomenon cannot occur, but not that phenomenon must occur, so all features are allowed except for transaction features that are not allowed. For example, if you request a repeatable read mode, the standard only requires you not to read dirty or reread, but not to allow phantom reading. Therefore, the serializable transaction mode meets the requirements of the repeatable readable mode, even if it does not exactly match the definition. Therefore, you should know for sure that when you ask to read an uncommitted pattern, you actually get a read committed pattern, and when you request a repeatable read, you actually get a serializable pattern. You should also be aware that by default, if you do not request a specific isolation level, you will get a read submitted isolation level.

Let's take a look at the main differences between read committed and serializable. In read committed mode, SELECT queries can only see data committed before the query starts, and can never see uncommitted data or changes committed by other parallel transactions when the query is executed; however, SELECT does see the results of previous updates in the same transaction, even if they have not yet been committed. In fact, a SELECT query sees a snapshot of the database at the moment the query starts to run. Note that two adjacent SELECT commands may see different data, even if they are in the same transaction, because other transactions will be committed when the first SELECT executes. When a transaction is serializable, an SELECT query can only see data committed before the transaction starts and never see uncommitted data or modifications committed by other parallel transactions in the transaction execution; however, SELECT does see the effect of previous updates in the same transaction, even if the transaction has not yet been committed. This behavior is not quite the same as reading the committed level, and its SELECT sees a snapshot of the beginning of the transaction, not the snapshot of the start of the current query within the transaction. In this way, the SELECT commands that follow within a transaction always see the same data. This means that you can see different data by reading the SELECT commands behind a transaction in committed mode, but always see the same data in serializable mode.

For the above differences, readers must be clear. Although it may seem a little complicated at first, it is easy to understand as long as you grasp two main points: first, PostgreSQL runs the concurrent operation of a transaction, that is, when one transaction executes, it does not prevent another transaction from operating on the same data. Second, be sure to pay attention to the concept of snapshots. Before a transaction commits, it operates on the data snapshot rather than the database itself, while paying attention to when the snapshot is used at different isolation levels-- the snapshot before the transaction starts, or the snapshot before the transaction internal operation starts? I think as long as we grasp the above points, we will be able to grasp the differences between the various isolation levels.

After introducing the basic concepts of transactions, let's demonstrate how to use transactions in the PostgreSQL client.

Create a sample table

Next, we use a specific online trading application as an example to illustrate the transaction concept introduced above. To do this, we need to first create two tables for the sample program in the database named company: participant and trunk. At the same time, we will also introduce the purpose and structure of each table. After building the tables, we also need to fill in some sample data for them, as shown below.

We first create the Participant table, which is used to store information about participants in the exchange, including their names, e-mail addresses, and available cash:

1 CREATE TABLE participant (

2 participantid SERIAL

3 name TEXT NOT NULL

4 email TEXT NOT NULL

5 cash NUMERIC (5 cash NUMERIC 2) NOT NULL

6 PRIMARY KEY (participantid)

7)

8 CREATE TABLE

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.