Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Using Transaction Blocks

Transaction blocks are explicitly started with the BEGIN SQL command. This keyword may optionally be followed by either of the noise terms WORK or TRANSACTION, though they have no effect on the statement, or the transaction block.

Example 7-38 begins a transaction block within the booktown database.

Example 7-38. Beginning a transaction

booktown=# 
BEGIN;

BEGIN

Any SQL statement made after the BEGIN SQL command will appear to take effect as normal to the user making the modifications. As stated earlier, however, other users connected to the database will be oblivious to the modifications that appear to have been made from within your transaction block until it is committed.

Transaction blocks are closed with the COMMIT SQL command, which may be followed by either of the optional noise terms WORK or TRANSACTION. Example 7-39 uses the COMMIT SQL command to synchronize the database system with the result of an UPDATE statement.

Example 7-39. Committing a transaction

booktown=# 
BEGIN;

BEGIN
booktown=# 
UPDATE subjects SET location = NULL

booktown-# 
                WHERE id = 12;

UPDATE 1
booktown=# 
SELECT location FROM subjects WHERE id = 12;

 location
----------

(1 row)

booktown=# 
COMMIT;

COMMIT

Again, even though the SELECT statement immediately reflects the result of the UPDATE statement in Example 7-39, other users connected to the same database will not be aware of that modification until after the COMMIT statement is executed.

To roll back a transaction, the ROLLBACK SQL command is used. Again, either of the optional noise terms WORK or TRANSACTION may follow the ROLLBACK command.

Example 7-40 begins a transaction block, makes a modification to the subjects table, and verifies the modification within the block. The transaction is then rolled back, returning the subjects table to the state that it was in before the transaction block began.

Example 7-40. Rolling back a transaction

booktown=# 
BEGIN;

BEGIN
booktown=# 
SELECT * FROM subjects WHERE id = 12;

 id | subject  | location
----+----------+----------
 12 | Religion |
(1 row)

booktown=# 
UPDATE subjects SET location = 'Sunset Dr'

booktown-# 
                WHERE id = 12;

UPDATE 1
booktown=# 
SELECT * FROM subjects WHERE id = 12;

 id | subject  | location
----+----------+-----------
 12 | Religion | Sunset Dr
(1 row)

booktown=# 
ROLLBACK;

ROLLBACK
booktown=# 
SELECT * FROM subjects WHERE id = 12;

 id | subject  | location
----+----------+----------
 12 | Religion |
(1 row)

PostgreSQL is very strict about errors in SQL statements inside of transaction blocks. Even an innocuous parse error, such as that shown in Example 7-41, will cause the transaction to enter into the ABORT STATE. This means that no further statements may be executed until either the COMMIT or ROLLBACK command is used to end the transaction block.

Example 7-41. Recovering from the abort state

booktown=# 
BEGIN;

BEGIN
booktown=# 
SELECT * FROM;

ERROR:  parser: parse error at or near ";"
booktown=# 
SELECT * FROM books;

NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
booktown=# 
COMMIT;

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire