How the Transaction Code Affects Daily Work


In most cases, you won't recognize what transactions are going on inside your database, but it is sometimes useful to start and end transactions manually ” especially when performing critical operations where you want to return to a previous state of the database. You can start transactions explicitly by using the BEGIN command. Let's have a look at what the database tells us about BEGIN:

 persons=#  \h BEGIN  Command:     BEGIN Description: Begins a transaction in chained mode Syntax: BEGIN [ WORK  TRANSACTION ] 

To end a transaction explicitly, you can use the COMMIT command:

 persons=#  \h COMMIT  Command:     COMMIT Description: Commits the current transaction Syntax: COMMIT [ WORK  TRANSACTION ] 

BEGIN and COMMIT are usually done implicitly. Here is a piece of code that shows how starting and ending transactions look like in SQL:

 persons=#  BEGIN TRANSACTION;  BEGIN persons=#  CREATE TABLE person(name VARCHAR(50), gender CHAR(1), height INTEGER);  CREATE persons=#  END TRANSACTION;  COMMIT 

Up to now, you haven't seen any difference; you can see in the following code that the table has been created successfully:

 persons=# \d      List of relations   Name   Type    Owner --------+-------+----------  person  table  postgres (1 row) 

Rollbacks

Recall that transactions can be used when performing critical operations where you might want to return to a previous state. PostgreSQL supports the ROLLBACK command, which is a kind of undo function. Here is an example of how ROLLBACK works:

 persons=#  BEGIN TRANSACTION;  BEGIN persons=#  CREATE TABLE person(name VARCHAR(50), gender CHAR(1), height INTEGER);  CREATE persons=#  \d  List of relations   Name   Type    Owner --------+-------+----------  person  table  postgres (1 row) persons=#  ROLLBACK;  ROLLBACK persons=#  \d  No relations found. 

Suppose that you start a transaction and create the table the same way you did in the previous example, but now you recognize in the middle of the transaction that you have created the wrong table and that the name of the table should be different. You can perform a ROLLBACK . All commands performed in the current transaction will be obsolete, and you will have the same database you had at the beginning. Let's have a look at what PostgreSQL tells us about ROLLBACK :

 persons=#  \h ROLLBACK  Command:     ROLLBACK Description: Aborts the current transaction Syntax: ROLLBACK [ WORK  TRANSACTION ] 

You can see that ROLLBACK stops the current transaction; therefore, you do not need to use COMMIT manually.

Concurrent Transactions

Always remember that other users can see only the result of transactions that have already been finished. A user won't read the result of an unfinished transaction, because this would be a dirty read (refer to the description earlier in this chapter). Table 4.1 summarizes an example with multiple users working on a table simultaneously .

Table 4.1. Example of Multiple Users on the Same Table
User One User Two Result
CREATE TABLE children(name VARCHAR(50), gender CHAR(1), height INTEGER); \d Table "children" can be seen by user two.
BEGIN WORK; \d No relations found.
DROP TABLE children; \d Table "children" can be seen by user two.
COMMIT; \d No relations found.

User two can still see a table that user one has already dropped. One issue when dealing with transactions and DROP TABLE is shown in this code:

 NOTICE:  Caution: DROP TABLE cannot be rolled back, so don't  abort now 

No ROLLBACK can be performed after a DROP TABLE in PostgreSQL database version 7.1.

Now we want to start multiple transactions at once:

 persons=#  BEGIN TRANSACTION;  BEGIN persons=#  BEGIN TRANSACTION;  NOTICE:  BEGIN: already a transaction in progress BEGIN persons=#  END TRANSACTION;  COMMIT persons=#  END TRANSACTION;  NOTICE:  COMMIT: no transaction in progress COMMIT 

Recall that PostgreSQL can run only one transaction per login at once.

Currently, PostgreSQL does not support savepoints in transactions ”the way that Oracle does, for instance. Savepoints are especially useful in long transactions where the user doesn't want to scrap all changes because of a small error. Let's hope we see this feature in future versions of PostgreSQL.

Transaction Isolation

Another important command that affects your daily life when working with transaction is the SET command. SET is used to define the level of transaction isolation. Here is what the database tells us about SET :

 persons=#  \h set  Command:     SET Description: Set run-time parameters for session Syntax: SET variable {  TO  = }  {  value  'value'  DEFAULT } SET CONSTRAINTS { ALL  constraintlist}  mode SET TIME ZONE {  'timezone'  LOCAL  DEFAULT } SET TRANSACTION ISOLATION LEVEL {  READ COMMITTED  SERIALIZABLE } 

SET let's you see what you can do with SET TRANSACTON ISOLATION LEVEL . As described earlier, three major events should be prevented: dirty reads, nonrepeatable reads, and phantom reads. According to the ANSI/ISO SQL standard, four levels of transaction isolation levels are defined:

  • Read uncommitted

  • Read committed

  • Repeatable read

  • Serializable

PostgreSQL supports read committed and serializable as its transaction levels. Serializable transaction levels makes sure that each transaction sees an absolutely consistent view of the database. Neither dirty reads, nonrepeatable reads, nor phantom reads may happen. With read committed, nonrepeatable reads and "phantom reads" may happen.

PostgreSQL uses read committed as the default isolation level. This book won't go further into detail, because PostgreSQL internals won't affect practical work significantly. The most important issue is that a query will read only data that has already been committed by another transaction.

Serialization seems to work as if all transactions would run one after the other (it actually does some kind of emulation of serial transactions) and therefore provides the highest level of transaction isolation.

In most cases, the default level of transaction isolation will satisfy your demands, and it's not necessary to use a different setting than the default one.

Locking

Locking is an interesting and important feature. In most cases, the user doesn't have to care about it because the database manages the whole locking stuff by itself. There are a few cases, however, where the user has to care about locking on the application level.

Assume an example where you perform a full-table scan in a multiuser environment. You select all rows from a table and process the rows one after the after with the help of a program. The program decides whether a row has to be deleted or updated. Thanks to transactions, you get a consistent snapshot of the data returned by the database, but what if a row has already been deleted by another transaction when you want to perform an update? If a query returns rows, it is not sure that the record returned still exists, because other users might already have deleted it. This can happen especially when certain queries are very big and transferring the data to the user takes a long time. To avoid the problem, a user can lock a table.

PostgreSQL offers a variety of locking commands. The most important ones are SELECT FOR UPDATE and LOCK . As you can see in the following code, LOCK offers many possibilities for locking:

 persons=#  \h LOCK  Command:     LOCK Description: Explicitly lock a table inside a transaction Syntax: LOCK [ TABLE ] name LOCK [ TABLE ] name IN [ ROW  ACCESS ] {  SHARE  EXCLUSIVE }  MODE LOCK [ TABLE ] name IN SHARE ROW EXCLUSIVE MODE 

Table 4.2 summarizes a locking example.

Table 4.2. Locking Example with Multiple Users
User One User Two Comment
CREATE TABLE children(name VARCHAR(50), gender CHAR(1), height INTEGER);   User one creates a table.
INSERT INTO children(name, gender, height) VALUES ('Oliver','m','110');   User one inserts a value.
INSERT INTO children(name, gender, height) VALUES ('Jenny','f','117');   User two inserts a value.
BEGIN WORK;   User one starts a transaction.
LOCK children;   User two locks the table.
INSERT INTO children(name, gender, height) VALUES ('Etschi','f','103');   User one inserts a value.
INSERT INTO children(name, gender, height) VALUES ('David','m','105');   The database waits.
COMMIT;   The database performs the INSERT of user two as soon as user one ends the transaction.

User two can insert David as soon as user one quits the transaction. You would not run into trouble when performing INSERT statements, but if user one performed an UPDATE query on all records (let's say changing all genders to NULL ), David's record would also be affected. If user one wanted only to update the record he or she inserted into the database, it would have gone wrong.

You can see in this simple example how useful locking can be, and it can save you a lot of headaches if you use locking correctly. One event you must take care of when working with explicit locks is a deadlock. A deadlock is a situation where one process is waiting for another process to perform an action that never happens. The second process is waiting for the first process. Both processes are waiting for each other, but nothing happens.

The following ways of table locking are supported:

  • AccessShareLock is an internal locking mechanism.

  • RowShareLock is used by SELECT FOR UPDATE and LOCK TABLE in IN ROW SHARE MODE statements.

  • RowExclusiveLock is used by UPDATE , INSERT , DELETE , and LOCK TABLE in ROW EXCLUSIVE MODE .

  • ShareLock is used by CREATE INDEX and LOCK TABLE with IN SHARE MODE statements.

  • ShareRowExclusiveLock is used by LOCK TABLE with IN SHARE ROW EXCLUSIVE MODE statements.

  • ExclusiveLock is used by LOCK TABLE with IN EXCLUSIVE MODE statements.

  • AccessExclusiveLock is used by ALTER TABLE , DROP TABLE , VACUUM , and LOCK TABLE .

Row-level locking is used when certain fields of a record are updated by the user.

You need not know every method your PostgreSQL server uses to lock something; just be aware of how important locking is when building applications and how you can perform locking and unlocking.



PostgreSQL Developer's Handbook2001
PostgreSQL Developer's Handbook2001
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 125

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net