Beginning PostgreSQL Transactions

Transactions can be a rough concept for those new to databases. Those new to PostgreSQL will find the implementation much different than those in other databases.  The default isolation level in PostgreSQL is READ COMMITTED so that you only see data that has been committed. If someone else works on the same table, you will not see those changes until they are committed.

Start with an elementary table:

demo=# create table ex03 (a int);
CREATE TABLE
demo=# insert into ex03 (a) values (1);
INSERT 0 1

Behind the scenes, the system sets up some information to track your data, as we learned here and here. 


demo=# select a, ctid, xmin, xmax, cmin, cmax from ex03;
 a | ctid  | xmin | xmax | cmin | cmax 
---+-------+------+------+------+------
 1 | (0,1) |  768 |    0 |    0 |    0
(1 row)

The CTID was introduced last time, and the other columns may be new. The documentation details their definitions. For this blog, we are going to focus on XMIN and XMAX.  XMIN is a 32-bit identifier that records the transaction identification that inserted the data in the column. Each successful update makes a new row with the updated columns. XMAX has two possible values. It will be zero when there is no transaction identification of a process working on that column. If there is a value for XMAX, then some activity is being done on that column. Clear as mud? It will be clearer in a few paragraphs. 

First Transaction

For our first transaction on Terminal #1, we will start a transaction, update the value, and peek at the resulting changes. Since a COMMIT has not been sent from this connection and the default isolation is READ COMMITTED, this session is the only one to know of this update.

demo=# start transaction;
START TRANSACTION
demo=*# update ex03 set a=2;
UPDATE 1
demo=*# select a, ctid, xmin, xmax, cmin, cmax from ex03;
 a | ctid  | xmin | xmax | cmin | cmax 
---+-------+------+------+------+------
 2 | (0,2) |  769 |    0 |    0 |    0
(1 row)

The XMIN columns informed us that one row in the table was placed there by transaction number 769.

Second Transaction In Parallel

In another session, someone gets the data from our demo table.

demo=# select * from ex03;
 a 
---
 1
(1 row)

Someone decides to add two rows of data to this. Following good practices, they start a transaction and then insert two rows of data. 

demo=# start transaction;
START TRANSACTION
demo=*# insert into ex03 (a) values (100),(101);
INSERT 0 2
demo=*# select a, ctid, xmin, xmax, cmin, cmax from ex03;
  a  | ctid  | xmin | xmax | cmin | cmax 
-----+-------+------+------+------+------
   1 | (0,1) |  768 |  769 |    0 |    0
 100 | (0,3) |  770 |    0 |    0 |    0
 101 | (0,4) |  770 |    0 |    0 |    0
(3 rows)
demo=*# 

Previously, back in Terminal 1, the first row had an XMIN of 769 and no value for XMAX. Now, on parallel universe Terminal 2, the XMIN for the current session is 770. The two new rows just entered from Terminal 2 have the XMIN of this transaction. The first row where a = 1 having a great than zero XMAX warns us that someone else is changing that row.

You can get the current transaction ID by using SELECT txid_current();


Back To The First Transaction

Back in Terminal 1, there is no knowledge of what is going on in Terminal 2. We do not see the two new rows even if we check the table.

demo=*# select a, ctid, xmin, xmax, cmin, cmax from ex03;
 a | ctid  | xmin | xmax | cmin | cmax 
---+-------+------+------+------+------
 2 | (0,2) |  769 |    0 |    0 |    0
(1 row)
demo=*# commit;
COMMIT
demo=# select a, ctid, xmin, xmax, cmin, cmax from ex03;
 a | ctid  | xmin | xmax | cmin | cmax 
---+-------+------+------+------+------
 2 | (0,2) |  769 |    0 |    0 |    0
(1 row)
demo=# 

Back To The Second Transaction

Terminal 2 has no clue about what has been happening in Terminal 1. Users who check the table see what they saw before, except that the XMIN has now been set to 769 (the transaction ID of the last modification to the row from good old Terminal 1). XMAX has been set to zero, which informs us it is not currently under revision by someone or there was a roll back (later in this blog you will see this).

The user on Terminal 2 now issues a SAVEPOINT. This can be thought of as a transaction within a transaction. This user then modifies the row where a = 101 to a new value.

demo=*# select a, ctid, xmin, xmax, cmin, cmax from ex03;
  a  | ctid  | xmin | xmax | cmin | cmax 
-----+-------+------+------+------+------
   2 | (0,2) |  769 |    0 |    0 |    0
 100 | (0,3) |  770 |    0 |    0 |    0
 101 | (0,4) |  770 |    0 |    0 |    0
(3 rows)
demo=*# savepoint foo;
SAVEPOINT
demo=*# update ex03 set a = 103 where a = 101;
UPDATE 1
demo=*# select a, ctid, xmin, xmax, cmin, cmax from ex03;
  a  | ctid  | xmin | xmax | cmin | cmax 
-----+-------+------+------+------+------
   2 | (0,2) |  769 |    0 |    0 |    0
 100 | (0,3) |  770 |    0 |    0 |    0
 103 | (0,5) |  771 |    0 |    1 |    1
(3 rows)

Note that CITD for value a = 101 is (0,4) before the update and then changes to (0,5) after. You can not see (0,4) right now, but it is there. Put that fact aside in your brain for a moment.

For some reason, this change is not liked. At this point, we can roll back to the SAVEPOINT or back out of the entire transaction, erasing all the changes made.

demo=*# rollback to foo;
ROLLBACK
demo=*# select a, ctid, xmin, xmax, cmin, cmax from ex03;
  a  | ctid  | xmin | xmax | cmin | cmax 
-----+-------+------+------+------+------
   2 | (0,2) |  769 |    0 |    0 |    0
 100 | (0,3) |  770 |    0 |    0 |    0
 101 | (0,4) |  770 |  771 |    0 |    0
(3 rows)

Terminal 2's user is now happy with the changes and commits them. CTID (0,5) has been replaced with CTID(0,4) logically. 

demo=*# commit;
COMMIT
demo=# select a, ctid, xmin, xmax, cmin, cmax from ex03;
  a  | ctid  | xmin | xmax | cmin | cmax 
-----+-------+------+------+------+------
   2 | (0,2) |  769 |    0 |    0 |    0
 100 | (0,3) |  770 |    0 |    0 |    0
 101 | (0,4) |  770 |  771 |    0 |    0
(3 rows)
demo=# 


Back Again To Terminal 1

The user on Terminal 1 now has the same view of the data that the user on Terminal 2 does. 

demo=# select a, ctid, xmin, xmax, cmin, cmax from ex03;
  a  | ctid  | xmin | xmax | cmin | cmax 
-----+-------+------+------+------+------
   2 | (0,2) |  769 |    0 |    0 |    0
 100 | (0,3) |  770 |    0 |    0 |    0
 101 | (0,4) |  770 |  771 |    0 |    0
(3 rows)
demo=# 

Remember, this was for a table with ONE column. In a complicated transaction where one row with multiple columns can be updated several times, this gets much more complex (and we will get to that soon in another blog). One of the beauties of PostgreSQL is that we can observe what is being done behind the scenes on our behalf. 

Comments

Post a Comment

Popular posts from this blog

How Does PostgreSQL Store Your Data?

PostgreSQL is an amazing database. And some of us wonder how it takes what we enter on a keyboard and stores it. The following is peeking into the 'clockworks' to see how things are done. Step 1 On a fresh install of Ubuntu with PostgreSQL 17, let us create a database. stoker@ThinkPad:~$ createdb demo Password:  stoker@ThinkPad:~$ psql demo Password for user stoker:  psql (17.2 (Ubuntu 17.2-1.pgdg24.04+1)) Type "help" for help. Now that we are in this new database, we can ask where PostgreSQL stores the data by looking for the data directory. demo=# show data_directory;        data_directory         -----------------------------  /var/lib/postgresql/17/main (1 row) This informs us that our data will be somewhere under the /var/lib/postgresql17/main directory.  Step 2 Issuing a command to create a table starts of series of events that we will study in later posts. Right now, we want to see where the stuff goes. Here a simp...
Read more

Now That We Know Where PostgreSQL Stores Data, We Can Look At How

 Last time , we started looking at how PostgreSQL stores data by discovering where the data is stored and had a quick peek at that data. There is only one record at this point. demo=# SELECT * FROM ex01;  a | b  ---+---  1 | 1 (1 row) And the contents of that file are not precisely illuminating at first glance. For now, just trust me that our row is in there, and you will see where in just a little bit. root@ThinkPad:/var/lib/postgresql/17/main/base/16389# od -c 16390 0000000  \0  \0  \0  \0   (   z 227 001  \0  \0  \0  \0 034  \0 340 037 0000020  \0     004      \0  \0  \0  \0 340 237   @  \0  \0  \0  \0  \0 0000040  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0 * 0017740 362 002  \0  \0  \0  \0  \...
Read more