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.

The first important thing to know is this: 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=# SELECT now(), now();
             now              |             now               
------------------------------+------------------------------
 2016-08-30 12:03:27.84596+02 | 2016-08-30 12:03:27.84596+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 about basic locking mechanisms. The goal is to make you 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:

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 those 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 just like an UPDATE would. This means that no changes can...

Up to now, you have seen how to handle locking as well as some basic concurrency. In this section, you will learn about 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 disgusting and 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 READ COMMITTED transaction isolation mode. This means...

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 us 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 is detected, the following error message...

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 that, 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 that, data is still visible while you do a 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 can not clean up its own mess and COMMIT/ROLLBACK might be too early to take care of dead rows.

The solution to the problem is VACUUM...

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

In the next chapter, 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 index and their behavior.