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 of transactions.

The first important thing to know is that 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=# \x 
Expanded display is on. 
test=# SELECT now(), now(); 
             now               |             now               
-------------------------------+------------------------------
 2017-08-24 16:03:27.174253+02 | 2017-08-24 16:03:27.174253+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.

In this section, you will learn basic locking mechanisms. The goal is to understand how locking works in general and how to get simple applications right.

To show how things work, a simple table can be created. For demonstration purposes, I will add one row to the table:

test=# CREATE TABLE  t_test (id int);  
CREATE TABLE 
test=# INSERT INTO t_test VALUES (1);  
INSERT 0 1 

The first important thing is that tables can be read concurrently. Many users reading the same data at the same time won't block each other. This allows PostgreSQL to handle thousands of users without problems.

The question now is: what happens if reads and writes occur at the same time? Here is an example. Let us assume that the table contains one row and its id = 0:

Sometimes, data is selected from the database; then some processing happens in the application and, finally, some changes are made back on the database side. This is a classic example of SELECT FOR UPDATE.

Here is an example:

BEGIN;
SELECT * FROM  invoice WHERE  processed = false; 
** application magic  will  happen here  **  
UPDATE invoice SET processed = true  ...  
COMMIT; 

The problem here is that two people might select the same unprocessed data. Changes made to these processed rows will then be overwritten. In short, a race condition will occur.

To solve this problem, developers can make use of SELECT FOR UPDATE. Here is how it works:

BEGIN; 
SELECT * FROM invoice WHERE processed = false FOR UPDATE; 
** application magic  will  happen here  **  
UPDATE invoice SET processed = true ...  
COMMIT; 

The SELECT FOR UPDATE will lock rows...

Up until now, you have seen how to handle locking as well as some basic concurrency. In this section, you will learn transaction isolation. To me, this is one of the most neglected topics in modern software development. Only a small fraction of software developers are actually aware of this issue, which in turn leads to mind-boggling bugs.

Here is an example of what can happen:

Most users would actually expect the left transaction to always return 300 regardless of the second transaction. However, this is not true. By default, PostgreSQL runs in the READ COMMITTED transaction...

Deadlocks are an important issue and can happen in every database I am aware of. Basically, a deadlock will happen if two transactions have to wait on each other.

In this section, you will see how this can happen. Let's suppose we have a table containing two rows:

CREATE TABLE t_deadlock (id int); 
INSERT INTO t_deadlock VALUES (1), (2); 

The next listing shows what can happen:

As soon as the deadlock...

PostgreSQL has a highly efficient and sophisticated transaction machinery that is capable of handling locks in a really fine-grained and efficient way. Some years ago, some people came up with the idea of using this code to synchronize applications with each other.

Thus, advisory locks were born.

When using advisory locks, it is important to mention that they won't go away on COMMIT as normal locks do. Therefore, it is really important to make sure that unlocking is done properly and in a totally reliable way.

If you decide to use an advisory lock, what you really lock is a number. So, this is not about rows or data; it is really just a number. Here is how it works:

Transactions are an integral part of the PostgreSQL system. However, transactions come with a small price tag attached. As already shown in this chapter, it can happen that concurrent users will be presented with different data. Not everybody will get the same data returned by a query. In addition to this, DELETE and UPDATE are not allowed to actually overwrite data as ROLLBACK would not work. If you happen to be in the middle of a large DELETE operation, you cannot be sure whether you will be able to COMMIT or not. In addition to this, data is still visible while you perform DELETE, and sometimes data is even visible once your modification has long since finished.

Consequently, this means that cleanup has to happen asynchronously. A transaction cannot clean up its own mess and COMMIT/ROLLBACK might be too early to take care of dead rows...

In this chapter, you learned transactions, locking and its logical implications, and the general architecture the PostgreSQL transaction machinery can have for storage, concurrency, and administration. You saw how rows are locked and which features are available in PostgreSQL.

In Chapter 3, Making use of Indexes, you will learn one of the most important topics in database work: indexing. You will learn about the PostgreSQL query optimizer as well as various types of indexes and their behavior.