Home > Articles > Data > SQL

This chapter is from the book

This chapter is from the book

Transaction Processing

Now let’s move on to an important feature in any database system: transaction processing.

A transaction is a group of one or more SQL commands treated as a unit. PostgreSQL promises that all commands within a transaction will complete or that none of them will complete. If any command within a transaction does not complete, PostgreSQL will roll back all changes made within the transaction.

PostgreSQL makes use of transactions to ensure database consistency. Transactions are needed to coordinate updates made by two or more concurrent users. Changes made by a transaction are not visible to other users until the transaction is committed. When you commit a transaction, you are telling PostgreSQL that all the changes made within the transaction are logically complete, the changes should be made permanent, and the changes should be exposed to other users. When you roll back a transaction, you are telling PostgreSQL that the changes made within the transaction should be discarded and not made visible to other users.

To start a new transaction, execute a BEGIN command. To complete the transaction and have PostgreSQL make your changes permanent, execute the COMMIT command. If you want PostgreSQL to revert all changes made within the current transaction, execute the ROLLBACK command11.

It’s important to realize that all SQL commands execute within a transaction. If you don’t explicitly BEGIN a transaction, PostgreSQL will automatically execute each command within its own transaction.

Persistence

I used to think that single-command transactions were pretty useless: I was wrong. Single-command transactions are important because a single command can access multiple rows. Consider the following: Let’s add a new constraint to the customers table.

movies=# ALTER TABLE customers ADD CONSTRAINT 
movies-#   balance_exceeded CHECK( balance <= 50 );

This constraint ensures that no customer is allowed to have a balance exceeding $50.00. Just to prove that it works, let’s try setting a customer’s balance to some value greater than $50.00:

movies=# UPDATE CUSTOMERS SET balance = 100 where customer_id = 1;
ERROR:  ExecReplace: rejected due to CHECK constraint balance_exceeded

You can see that the UPDATE is rejected. What happens if you try to update more than one row? First, let’s look at the data already in the customers table:

movies=# SELECT * FROM customers;
 customer_id |    customer_name     |  phone   | birth_date | balance
-------------+----------------------+----------+------------+---------
           1 | Jones, Henry         | 555-1212 | 1970-10-10 |    0.00
           2 | Rubin, William       | 555-2211 | 1972-07-10 |   15.00
           3 | Panky, Henry         | 555-1221 | 1968-01-21 |    0.00
           4 | Wonderland, Alice N. | 555-1122 | 1969-03-05 |    3.00
           8 | Wink Wankel          | 555-1000 | 1988-12-25 |    0.00
(5 rows)

Now, try to UPDATE every row in this table:

movies=# UPDATE customers SET balance = balance + 40;
ERROR:  ExecReplace: rejected due to CHECK constraint balance_exceeded

This UPDATE command is rejected because adding $40.00 to the balance for Rubin, William violates the balance_exceeded constraint. The question is, were any of the customers updated before the error occurred? The answer is: probably. You don’t really know for sure because any changes made before the error occurred are rolled back. The net effect is that no changes were made to the database:

movies=# SELECT * FROM customers;
 customer_id |    customer_name     |  phone   | birth_date | balance
-------------+----------------------+----------+------------+---------
           1 | Jones, Henry         | 555-1212 | 1970-10-10 |    0.00
           2 | Rubin, William       | 555-2211 | 1972-07-10 |   15.00
           3 | Panky, Henry         | 555-1221 | 1968-01-21 |    0.00
           4 | Wonderland, Alice N. | 555-1122 | 1969-03-05 |    3.00
           8 | Wink Wankel          | 555-1000 | 1988-12-25 |    0.00
(5 rows)

If some of the changes persisted while others did not, you would have to somehow find the persistent changes yourself and revert them. You can see that single-command transactions are far from useless. It took me awhile to learn that lesson.

What about multicommand transactions? PostgreSQL treats a multicommand transaction in much the same way that it treats a single-command transaction. A transaction is atomic, meaning that all the commands within the transaction are treated as a single unit. If any of the commands fail to complete, PostgreSQL reverts the changes made by other commands within the transaction.

Transaction Isolation

I mentioned earlier in this section that the changes made within a transaction are not visible to other users until the transaction is committed. To be a bit more precise, uncommitted changes made in one transaction are not visible to other transactions12.

Transaction isolation helps to ensure consistent data within a database. Let’s look at a few of the problems solved by transaction isolation.

Consider the following transactions:

User: bruce

Time

User: sheila

BEGIN TRANSACTION

T1

BEGIN TRANSACTION

UPDATE customers

T2

 

SET balance = balance - 3

 

 

WHERE customer_id = 2;

 

 

 

T3

SELECT SUM( balance )

 

 

FROM customers;

 

T4

COMMIT TRANSACTION;

ROLLBACK TRANSACTION;

T5

 


At time T1, bruce and sheila each begin a new transaction. bruce updates the balance for customer 3 at time T1. At time T3, sheila computes the SUM() of the balances for all customers, completing her transaction at time T4. At time T5, bruce rolls back his transaction, discarding all changes within his transaction. If these transactions were not isolated from each other, sheila would have an incorrect answer: Her answer was calculated using data that was rolled back.

This problem is known as the dirty read problem: without transaction isolation, sheila would read uncommitted data. The solution to this problem is known as READ COMMITTED. READ COMMITTED is one of the two transaction isolation levels supported by PostgreSQL. A transaction running at the READ COMMITTED isolation level is not allowed to read uncommitted data. I’ll show you how to change transaction levels in a moment.

There are other data consistency problems that are avoided by isolating transactions from each other. In the following scenario, sheila will receive two different answers within the same transaction:

User: bruce

Time

User: sheila

BEGIN TRANSACTION;

T1

BEGIN TRANSACTION;

 

T2

SELECT balance

FROM customers

 

 

WHERE customer_id = 2;

 

 

UPDATE customers

 

 

SET balance = 20

 

 

WHERE customer_id = 2;

T3

 

COMMIT TRANSACTION;

T4

 

 

T5

SELECT balance

 

 

FROM customers

 

 

WHERE customer_id = 2;

 

T6

COMMIT TRANSACTION;


Again, bruce and sheila each start a transaction at time T1. At T2, sheila finds that customer 2 has a balance of $15.00. bruce changes the balance for customer 2 from $15.00 to $20.00 at time T3 and commits his change at time T4. At time T5, sheila executes the same query that she executed earlier in the transaction, but this time she finds that the balance is $20.00. In some applications, this isn’t a problem; in others, this interference between the two transactions is unacceptable. This problem is known as the non-repeatable read.

Here is another type of problem:

User: bruce

Time

User: sheila

BEGIN TRANSACTION;

T1

BEGIN TRANSACTION;

 

T2

SELECT * FROM customers;

INSERT INTO customers VALUES

T3

 

(

 

 

6,

 

 

‘Neville, Robert’,

 

 

‘555-9999’,

 

 

‘1971-03-20’,

 

 

0.00

 

 

);

 

 

COMMIT TRANSACTION;

T4

 

 

T5

SELECT * FROM customers;

 

T6

COMMIT TRANSACTION;


In this example, sheila again executes the same query twice within a single transaction. This time, bruce has inserted a new row in between the sheila’s queries. Notice that this is not a case of a dirty readbruce has committed his change before sheila executes her second query. At time T5, sheila finds a new row. This is similar to the non-repeatable read, but this problem is known as the phantom read problem.

The answer to both the non-repeatable read and the phantom read is the SERIALIZABLE transaction isolation level. A transaction running at the SERIALIZABLE isolation level is only allowed to see data committed before the transaction began.

In PostgreSQL, transactions usually run at the READ COMMITTED isolation level. If you need to avoid the problems present in READ COMMITTED, you can change isolation levels using the SET TRANSACTION command. The syntax for the SET TRANSACTION command is

SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE };

The SET TRANSACTION command affects only the current transaction (and it must be executed before the first DML13 command within the transaction). If you want to change the isolation level for your session (that is, change the isolation level for future transactions), you can use the SET SESSION command:

SET SESSION CHARACTERISTICS AS 
    TRANSACTION ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE }

PostgreSQL version 8.0 introduces a new transaction processing feature called a SAVEPOINT. A SAVEPOINT is a named marker that you define within the stream of commands that make up a transaction. Once you’ve defined a SAVEPOINT, you can ROLLBACK any changes that you’ve made since that point without discarding changes made prior to the SAVEPOINT—in other words, you can ROLLBACK part of a transaction (the trailing part) without rolling back the entire transaction. To create a SAVEPOINT, execute a SAVEPOINT command within a transaction. The syntax for a SAVEPOINT command is very simple:

SAVEPOINT savepoint-name

The savepoint-name must follow the normal rules for an identifier; it must be unique within the first 64 characters and must start with a letter or underscore (or it must be a quoted identifier). A SAVEPOINT gives a name to a point in time; in particular, a point between two SQL commands. Consider the following sequence:

movies=# SELECT customer_id, customer_name FROM customers;
 customer_id |    customer_name
-------------+----------------------
           3 | Panky, Henry
           1 | Jones, Henry
           4 | Wonderland, Alice N.
           2 | Rubin, William
(4 rows)

movies=# START TRANSACTION;
START TRANSACTION

movies=# INSERT INTO customers VALUES( 5, ‘Kemp, Hans’ );
INSERT 44272 1

movies=# SELECT * FROM customers;
 customer_id |    customer_name     |  phone   | birth_date | balance 
-------------+----------------------+----------+------------+---------
           3 | Panky, Henry         | 555-1221 | 1968-01-21 |    0.00 
           1 | Jones, Henry         | 555-1212 | 1970-10-10 |    0.00 
           4 | Wonderland, Alice N. | 555-1122 | 1969-03-05 |    3.00 
           2 | Rubin, William       | 555-2211 | 1972-07-10 |   15.00 
           5 | Kemp, Hans           |          |            |         
(5 rows)

At this point, you’ve started a new transaction and inserted a new row, but you haven’t committed your changes yet. Now define a SAVEPOINT named p1 and insert a second row:

movies=# SAVEPOINT P1;
SAVEPOINT

movies=# INSERT INTO customers VALUES( 6, ‘Falkstein, Gerhard’ );
INSERT 44273 1

The SAVEPOINT command inserted a marker into the transaction stream. If you execute a ROLLBACK command at this point, both of the newly inserted rows will be discarded (in other words, all of the changes you’ve made in this transaction will be rolled back):

movies=# ROLLBACK;
ROLLBACK
movies=# SELECT * FROM customers;
 customer_id |    customer_name     |  phone   | birth_date | balance 
-------------+----------------------+----------+------------+---------
           3 | Panky, Henry         | 555-1221 | 1968-01-21 |    0.00 
           1 | Jones, Henry         | 555-1212 | 1970-10-10 |    0.00 
           4 | Wonderland, Alice N. | 555-1122 | 1969-03-05 |    3.00 
           2 | Rubin, William       | 555-2211 | 1972-07-10 |   15.00 
(4 rows)

Now repeat the same sequence of commands, but this time around, execute a qualified ROLLBACK command, like this:

movies=# ROLLBACK TO SAVEPOINT P1;
ROLLBACK
movies=# SELECT * FROM customers;
 customer_id |    customer_name     |  phone   | birth_date | balance 
-------------+----------------------+----------+------------+---------
           3 | Panky, Henry         | 555-1221 | 1968-01-21 |    0.00 
           1 | Jones, Henry         | 555-1212 | 1970-10-10 |    0.00 
           4 | Wonderland, Alice N. | 555-1122 | 1969-03-05 |    3.00 
           2 | Rubin, William       | 555-2211 | 1972-07-10 |   15.00 
           5 | Kemp, Hans           |          |            |         
(5 rows)

When you ROLLBACK to a SAVEPOINT, changes made since the SAVEPOINT are discarded, but not changes made before the SAVEPOINT. So, you see that the customers table retains the first row that you inserted, but not the second row. When you ROLLBACK to a SAVEPOINT, you are still in the middle of a transaction—you must complete the transaction with a COMMIT or ROLLBACK command.

Here are a few important points to keep in mind when you’re working with SAVEPOINTs:

  • You can nest SAVEPOINTs. For example, if you create a SAVEPOINT named P1, then create a second SAVEPOINT named P2, you have created a nested SAVEPOINT (P2 is nested within P1). If you ROLLBACK TO SAVEPOINT P2, PostgreSQL discards any changes made since P2, but preserves changes made between P1 and P2. On the other hand, if you ROLLBACK TO SAVEPOINT P1, PostgreSQL discards all changes made since P1, including all changes made since P2. Nested SAVEPOINTs are handy when you are working with a multilevel table structure such as ORDERS and LINEITEMS (where you have multiple line items per order). If you define a SAVEPOINT prior to modifying each order, and a second, nested SAVEPOINT prior to modifying each line item, you can ROLLBACK changes made to a single line item, changes made to a single order, or an entire transaction.

  • You can use the same SAVEPOINT name as often as you like within a single transaction—the new SAVEPOINT simply replaces the old SAVEPOINT14. Again, this is useful when you are working with a multilevel table structure. If you create a SAVEPOINT prior to processing each line item and you give each of those SAVEPOINTs the same name, you can ROLLBACK changes made to the most recently processed line item.

  • If you ROLLBACK to a SAVEPOINT, the SAVEPOINT is not destroyed—you can make more changes in the transaction and ROLLBACK to the SAVEPOINT again. However, any SAVEPOINTs nested within that SAVEPOINT will be destroyed. To continue the ORDERS and LINEITEMS example, if you ROLLBACK the changes made to an ORDERS row, you also discard changes made to the LINEITEMS for that ORDER and you are destroying the SAVEPOINT that you created for the most recent line item.

  • If you make a mistake (such as a typing error), PostgreSQL rolls back to the most recent SAVEPOINT. That’s a very nice feature. If you’ve used PostgreSQL for any length of time, you’ve surely exercised your vocabulary after watching PostgreSQL throw out a long and complex transaction because you made a simple typing error. If you insert SAVEPOINTs in your transaction, you won’t lose as much work when your fingers fumble a table name.

Multi-Versioning and Locking

Most commercial (and open-source) databases use locking to coordinate multiuser updates. If you are modifying a table, that table is locked against updates and queries made by other users. Some databases perform page-level or row-level locking to reduce contention, but the principle is the same—other users must wait to read the data you have modified until you have committed your changes.

PostgreSQL uses a different model called multi-versioning, or MVCC for short (locks are still used, but much less frequently than you might expect). In a multi-versioning system, the database creates a new copy of the rows you have modified. Other users see the original values until you commit your changes—they don’t have to wait until you finish. If you roll back a transaction, other users are not affected—they did not have access to your changes in the first place. If you commit your changes, the original rows are marked as obsolete and other transactions running at the READ COMMITTED isolation level will see your changes. Transactions running at the SERIALIZABLE isolation level will continue to see the original rows. Obsolete data is not automatically removed from a PostgreSQL database. It is hidden, but not removed. You can remove obsolete rows using the VACUUM command. The syntax of the VACUUM command is

VACUUM [ VERBOSE ] [ ANALYZE ] [ table ]

I’ll talk about the VACUUM command in more detail in the next chapter.

The MVCC transaction model provides for much higher concurrency than most other models. Even though PostgreSQL uses multiple versions to isolate transactions, it is still necessary to lock data in some circumstances.

Try this experiment. Open two psql sessions, each connected to the movies database. In one session, enter the following commands:

movies=# BEGIN WORK;
BEGIN
movies=# INSERT INTO customers VALUES
movies-#  ( 5, ‘Manyjars, John’, ‘555-8000’, ‘1960-04-02’, 0 );
INSERT

In the other session, enter these commands:

movies=# BEGIN WORK;
BEGIN
movies=# INSERT INTO customers VALUES
movies-#  ( 6, ‘Smallberries, John’, ‘555-8001’, ‘1960-04-02’, 0 );
INSERT

When you press the Enter (or Return) key, this INSERT statement completes immediately. Now, enter this command into the second session:

movies=# INSERT INTO customers VALUES
movies-#  ( 5, ‘Gomez, John’, ‘555-8000’, ‘1960-04-02’, 0 );

This time, when you press Enter, psql hangs. What is it waiting for? Notice that in the first session, you already added a customer whose customer_id is 5, but you have not yet committed this change. In the second session, you are also trying to insert a customer whose customer_id is 5. You can’t have two customers with the same customer_id (because you have defined the customer_id column to be the unique PRIMARY KEY). If you commit the first transaction, the second session would receive a duplicate value error. If you roll back the first transaction, the second insertion will continue (because there is no longer a constraint violation). PostgreSQL won’t know which result to give you until the transaction completes in the first session.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020