Chapter 4. Understanding Transactions


Transactions are an interesting and useful feature of PostgreSQL. All sophisticated database systems offer some kind of transaction code these days.

When modifying data in a transaction-enabled database, the database uses some kind of "everything or nothing" algorithm. Imagine a DELETE statement that has to delete several thousand rows. If someone presses the reset button while the operation is running, you might run into trouble because only half of the table might have been deleted. You also might face integrity problems. To avoid such nasty occurrences, a highly sophisticated transaction-based system has been implemented in PostgreSQL.

Imagine a user starting a DELETE statement that affects millions of rows. If someone presses the reset button, the database will jump back to the state it had before the DELETE statement. If the DELETE statement quits successfully, however, the changes will be visible to all users. An operation has to be completed successfully or the changes made by it will silently be discarded. This can be very useful. Imagine a query that updates the amount of products stored in your bookshop. If the query terminates after half of the records, it would be difficult to find out which records have already been processed . Doing all changes again is, in most cases, easier and safer.

Transactions are also very important for Multi-Version Concurrency Control (MVCC). MVCC is an extremely sophisticated method of providing all users a consistent snapshot of the database when performing a query. MVCC is also a technique that significantly speeds up the database's performance in multiuser environments. It is important for you to understand what is meant by "consistent snapshot of the database." Imagine a situation where one user performs a full-table scan on a huge table while a second user performs INSERT , UPDATE , and DELETE statements on the same table. Assume that the changes of user two affect the data user one is currently reading. If user one is performing join operations, his or her query might look for data that user two has already deleted or updated. The result of user one's query would be trash if the data used by a query changes while performing the query.

Many databases use a simple way of solving the problem by performing a table lock. If a query locks a table, all other queries would have to wait until the lock is released. This makes sure that nothing evil can happen to the table meanwhile. Imagine a high-availability system where huge queries that take hours to be processed are executed ”the system would actually be down for hours if table locking were done. PostgreSQL offers the MVCC system instead.

In PostgreSQL environments, every query sees a consistent snapshot of the data (internally, processing is done using Lehman-Yao high-concurrency btrees, which is a very efficient data structure for that purpose). PostgreSQL uses a multiversion model for that purpose. No table locking has to be done because PostgreSQL uses an advanced row-locking system (table-locking is used as seldom as possible).

One important issue is transaction isolation. According to the ANSI/ISO SQL standard, four levels of transaction isolation are defined. Three events have to be prevented from happening:

  • Dirty reads . Data from unfinished sessions are read by a query. This leads to inconsistent input data.

  • Nonrepeatable reads . A transaction must be able to read data more often than once. All reads must lead to the same result. Therefore, no other transaction might change the data used by a transaction; otherwise , the database will run into trouble.

  • Phantom read . A transaction executes a query again, but the result differs from the first query because the input data has changed. The database can't perform the transaction correctly in this case.

PostgreSQL does not support nested transactions, which are transactions started and committed inside another transaction. This won't have a significant restriction for most applications, but you should consider it when using PostgreSQL's transaction code for your applications.



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