- Mastering PostgreSQL 9.6
- Hans Jurgen Schonig
- 404字
- 2021-07-09 19:57:11
Working with PostgreSQL transactions
PostgreSQL provides you with a highly advanced transaction machinery that offers countless features to developers and administrators alike. In this section, it is time to look at the basic concept.
The first important thing to know is this: in PostgreSQL, everything is a transaction. If you send a simple query to the server, it is already a transaction. Here is an example:
test=# SELECT now(), now();
now | now
------------------------------+------------------------------
2016-08-30 12:03:27.84596+02 | 2016-08-30 12:03:27.84596+02
(1 row)
In this case, the SELECT statement will be a separate transaction. If the same command is executed again, different timestamps will be returned.
If more than one statement has to be part of the same transactions, the BEGIN clause must be used:
test=# h BEGIN
Command: BEGIN
Description: start a transaction block
Syntax:
BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]
where transaction_mode is one of:
ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ
| READ COMMITTED | READ UNCOMMITTED }
READ WRITE | READ ONLY
[ NOT ] DEFERRABLE
The BEGIN clause will ensure that more than one command will be packed into a transaction. Here is how it works:
test=# BEGIN;
BEGIN
test=# SELECT now();
now
-------------------------------
2016-08-30 12:13:54.839277+02
(1 row)
test=# SELECT now();
now
-------------------------------
2016-08-30 12:13:54.839277+02
(1 row)
test=# COMMIT;
COMMIT
The important point here is that both timestamps will be identical. As mentioned earlier, we are talking about transaction time here.
To end the transaction, COMMIT can be used:
test=# h COMMIT
Command: COMMIT
Description: commit the current transaction
Syntax:
COMMIT [ WORK | TRANSACTION ]
There are a couple of syntax elements here. You can just use COMMIT, COMMIT WORK, or COMMIT TRANSACTION. All three options have the same meaning. If this is not enough, there is more:
test=# h END
Command: END
Description: commit the current transaction
Syntax:
END [ WORK | TRANSACTION ]
The END clause is the same as the COMMIT clause.
ROLLBACK is the counterpart of COMMIT. Instead of successfully ending a transaction, it will simply stop the transaction without ever making things visible to other transactions:
test=# h ROLLBACK
Command: ROLLBACK
Description: abort the current transaction
Syntax:
ROLLBACK [ WORK | TRANSACTION ]
Some applications use ABORT instead of ROLLBACK. The meaning is the same.
- 集成架構(gòu)中型系統(tǒng)
- Go Machine Learning Projects
- 人工免疫算法改進(jìn)及其應(yīng)用
- 機(jī)器學(xué)習(xí)與大數(shù)據(jù)技術(shù)
- Windows XP中文版應(yīng)用基礎(chǔ)
- 數(shù)據(jù)庫(kù)原理與應(yīng)用技術(shù)學(xué)習(xí)指導(dǎo)
- 數(shù)據(jù)挖掘方法及天體光譜挖掘技術(shù)
- 網(wǎng)絡(luò)綜合布線設(shè)計(jì)與施工技術(shù)
- Ceph:Designing and Implementing Scalable Storage Systems
- 信息物理系統(tǒng)(CPS)測(cè)試與評(píng)價(jià)技術(shù)
- 基于32位ColdFire構(gòu)建嵌入式系統(tǒng)
- Containers in OpenStack
- 設(shè)計(jì)模式
- Kubernetes on AWS
- Flash CS3動(dòng)畫(huà)制作