官术网_书友最值得收藏!

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.

Keep in mind that the  now() function will return the transaction time. The SELECT statement will, therefore, always return two identical timestamps.

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.

主站蜘蛛池模板: 丰台区| 白银市| 通道| 伊春市| 辽阳县| 襄汾县| 满洲里市| 芮城县| 固阳县| 青铜峡市| 墨脱县| 什邡市| 仁化县| 南通市| 英吉沙县| 蚌埠市| 东海县| 荥阳市| 潜江市| 怀来县| 察雅县| 朔州市| 夏津县| 五家渠市| 横峰县| 石屏县| 瓦房店市| 诸城市| 德令哈市| 黎城县| 山阳县| 钟祥市| 永仁县| 牟定县| 常宁市| 潼关县| 临夏市| 当雄县| 安仁县| 龙井市| 临漳县|