Question au combien intéressante! On va distinguer le mode mono-transactionnel et multitransactionnel

Hors mode transactionnel

Créons la petite table suivante

CREATE TABLE test (
    id integer,
    data text
);

On insère les données en utilisant la transaction suivante

BEGIN TRANSACTION;
INSERT INTO test (id, data) VALUES (1, 'ligne 1');
INSERT INTO test (id, data) VALUES (2, 'ligne 2');
INSERT INTO test (id, data) VALUES (3, 'ligne 3');
INSERT INTO test (id, data) VALUES (4, 'ligne 4');
INSERT INTO test (id, data) VALUES (5, 'ligne 5');
INSERT INTO test (id, data) VALUES (6, 'ligne 6');
INSERT INTO test (id, data) VALUES (7, 'ligne 7');
INSERT INTO test (id, data) VALUES (8, 'ligne 8');
INSERT INTO test (id, data) VALUES (9, 'ligne 9');
INSERT INTO test (id, data) VALUES (10, 'ligne 10');
END TRANSACTION;

Ce qui donne donc

performance=# SELECT id,data FROM test;
 id |   data   
----+----------
  1 | ligne 1
  2 | ligne 2
  3 | ligne 3
  4 | ligne 4
  5 | ligne 5
  6 | ligne 6
  7 | ligne 7
  8 | ligne 8
  9 | ligne 9
 10 | ligne 10
(10 rows)

A quoi serve xmin et cmin?

La requête suivante le montre

performance=# SELECT xmin,cmin,xmax,cmax,ctid,id,data FROM test ORDER BY 6;
   xmin   | cmin | xmax | cmax |  ctid  | id |   data   
----------+------+------+------+--------+----+----------
 10328502 |    0 |    0 |    0 |  (0,1) |  1 | ligne 1
 10328502 |    1 |    0 |    0 |  (0,2) |  2 | ligne 2
 10328502 |    2 |    0 |    0 |  (0,3) |  3 | ligne 3
 10328502 |    3 |    0 |    0 |  (0,4) |  4 | ligne 4
 10328502 |    4 |    0 |    0 |  (0,5) |  5 | ligne 5
 10328502 |    5 |    0 |    0 |  (0,6) |  6 | ligne 6
 10328502 |    6 |    0 |    0 |  (0,7) |  7 | ligne 7
 10328502 |    7 |    0 |    0 |  (0,8) |  8 | ligne 8
 10328502 |    8 |    0 |    0 |  (0,9) |  9 | ligne 9
 10328502 |    9 |    0 |    0 | (0,10) | 10 | ligne 10
(10 rows)

Donc
  • xmin: identifiant de transaction pour la version de ligne au niveau insertion;
  • cmin: identifiant d'instruction SQL au sein de la transaction-mère (xmin). Le compteur commence à zéro.

Faisons maintenant des petites 'delete' au sein d'une transaction avortée

BEGIN TRANSACTION;
DELETE FROM test  WHERE id=1;
DELETE FROM test   WHERE id=2;
DELETE FROM test  WHERE id=3;
DELETE FROM test  WHERE id=6;
DELETE FROM test   WHERE id=7;
DELETE FROM test  WHERE id=8;;
ROLLBACK TRANSACTION;

Comme on a fait un ROLLBACK, on a alors:

performance=# SELECT xmin,cmin,xmax,cmax,ctid,id,data FROM test ORDER BY 6;
   xmin   | cmin |   xmax   | cmax |  ctid  | id |   data   
----------+------+----------+------+--------+----+----------
 10328502 |    0 | 10328504 |    0 |  (0,1) |  1 | ligne 1
 10328502 |    1 | 10328504 |    1 |  (0,2) |  2 | ligne 2
 10328502 |    2 | 10328504 |    2 |  (0,3) |  3 | ligne 3
 10328502 |    3 |        0 |    0 |  (0,4) |  4 | ligne 4
 10328502 |    4 |        0 |    0 |  (0,5) |  5 | ligne 5
 10328502 |    5 | 10328504 |    3 |  (0,6) |  6 | ligne 6
 10328502 |    6 | 10328504 |    4 |  (0,7) |  7 | ligne 7
 10328502 |    7 | 10328504 |    5 |  (0,8) |  8 | ligne 8
 10328502 |    8 |        0 |    0 |  (0,9) |  9 | ligne 9
 10328502 |    9 |        0 |    0 | (0,10) | 10 | ligne 10
(10 rows)

En mode transactionnel.

En fait, on voit vraiment les différences en mode transactionnel. Supposons donc deux utilisateurs david et postgres. On va commencer par dropper la table. David (connexion 1) et postgres (connexion 2) se connecre à la base.

David se connecte à la base, détruit la base et reconstruit la table et la recharge

DROP TABLE test;

CREATE TABLE test (
    id integer,
    data text
);

BEGIN TRANSACTION;
INSERT INTO test (id, data) VALUES (1, 'ligne 1');
INSERT INTO test (id, data) VALUES (2, 'ligne 2');
INSERT INTO test (id, data) VALUES (3, 'ligne 3');
INSERT INTO test (id, data) VALUES (4, 'ligne 4');
INSERT INTO test (id, data) VALUES (5, 'ligne 5');
INSERT INTO test (id, data) VALUES (6, 'ligne 6');
INSERT INTO test (id, data) VALUES (7, 'ligne 7');
INSERT INTO test (id, data) VALUES (8, 'ligne 8');
INSERT INTO test (id, data) VALUES (9, 'ligne 9');
INSERT INTO test (id, data) VALUES (10, 'ligne 10');
END TRANSACTION;
Tout est commité sans souci!

postgres se connecte à son tour et fait

performance=# SELECT xmin,cmin,xmax,cmax,ctid,id,data FROM test ORDER BY 6;
   xmin   | cmin | xmax | cmax |  ctid  | id |   data   
----------+------+------+------+--------+----+----------
 10328534 |    0 |    0 |    0 |  (0,1) |  1 | ligne 1
 10328534 |    1 |    0 |    0 |  (0,2) |  2 | ligne 2
 10328534 |    2 |    0 |    0 |  (0,3) |  3 | ligne 3
 10328534 |    3 |    0 |    0 |  (0,4) |  4 | ligne 4
 10328534 |    4 |    0 |    0 |  (0,5) |  5 | ligne 5
 10328534 |    5 |    0 |    0 |  (0,6) |  6 | ligne 6
 10328534 |    6 |    0 |    0 |  (0,7) |  7 | ligne 7
 10328534 |    7 |    0 |    0 |  (0,8) |  8 | ligne 8
 10328534 |    8 |    0 |    0 |  (0,9) |  9 | ligne 9
 10328534 |    9 |    0 |    0 | (0,10) | 10 | ligne 10
(10 rows)

Postgres ouvre maintenant une transaction

begin;

et execute les instructions siuvantes

DELETE FROM test  WHERE id=1;
DELETE FROM test   WHERE id=2;
DELETE FROM test  WHERE id=3;
DELETE FROM test  WHERE id=6;
DELETE FROM test   WHERE id=7;
DELETE FROM test  WHERE id=8;;

Pour le moment postgres n'a pas encore commité. David lance la requête suivante

performance=# SELECT xmin,cmin,xmax,cmax,ctid,id,data FROM test ORDER BY 6;
   xmin   | cmin |   xmax   | cmax |  ctid  | id |   data   
----------+------+----------+------+--------+----+----------
 10328544 |    0 | 10328547 |    0 | (0,1)  |  1 | ligne 1
 10328544 |    1 | 10328547 |    1 | (0,2)  |  2 | ligne 2
 10328544 |    2 | 10328547 |    2 | (0,3)  |  3 | ligne 3
 10328544 |    3 |        0 |    0 | (0,4)  |  4 | ligne 4
 10328544 |    4 |        0 |    0 | (0,5)  |  5 | ligne 5
 10328544 |    5 | 10328547 |    3 | (0,6)  |  6 | ligne 6
 10328544 |    6 | 10328547 |    4 | (0,7)  |  7 | ligne 7
 10328544 |    7 | 10328547 |    5 | (0,8)  |  8 | ligne 8
 10328544 |    8 |        0 |    0 | (0,9)  |  9 | ligne 9
 10328544 |    9 |        0 |    0 | (0,10) | 10 | ligne 10
(10 lignes

David voit donc que les lignes pour lesquels id=1,2,3,6,7,8 sont en cours de suppression au cours de la transaction 10328547 lancée par postgres.

Maintenant postgres commite le tout.

end transaction;

David rejout sa requête

performance=# SELECT xmin,cmin,xmax,cmax,ctid,id,data FROM test ORDER BY 6;
   xmin   | cmin | xmax | cmax |  ctid  | id |   data   
----------+------+------+------+--------+----+----------
 10328544 |    3 |    0 |    0 | (0,4)  |  4 | ligne 4
 10328544 |    4 |    0 |    0 | (0,5)  |  5 | ligne 5
 10328544 |    8 |    0 |    0 | (0,9)  |  9 | ligne 9
 10328544 |    9 |    0 |    0 | (0,10) | 10 | ligne 10
(4 lignes)