TRUNCATE, concurrence simple

Tronquer en travaillant

      La documentation officielle de PostgreSQL émet un avertissement concernant TRUNCATE. Cette commande n’est pas sûre au niveau de MVCC, le mécanisme permettant de gérer la concurrence et le respect des niveaux d’isolation des transactions ! Mais, en suivant le lien, le texte nous rassure...TRUNCATE n’est pas sûre si vous essayez, dans une transaction en mode REPEATABLE READ ou SERIALIZABLE, de lire une table qui a été tronquée et que vous n’avez pas préalablement fait une lecture sur la table. Bref en READ COMMITED (le mode par défaut), pas de problème.
      Tout d’abord un test sous PostgreSQL 9.6 dans un cas simple avec uniquement des transactions en mode READ COMMITED :

SELECT version(); version ------------------------------------------------------------------------------------------ PostgreSQL 9.6.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit (1 ligne) CREATE TABLE t1(c11 SERIAL PRIMARY KEY, C12 INTEGER); CREATE TABLE INSERT INTO t1(c12) VALUES(generate_series(1,999999)); INSERT 0 999999 COMMIT; COMMIT -- Dans toutes les sessions AUTOCOMMIT à off -- SESSION 1 SELECT count(*) FROM t1 t11 JOIN t1 t12 ON (t11.c11 = t12.c12) JOIN t1 t13 ON (t12.c11 = t13.c12) JOIN t1 t14 ON (t13.c11 = t14.c12) JOIN t1 t15 ON (t14.c11 = t15.c12) JOIN t1 t16 ON (t15.c11 = t16.c12) JOIN t1 t17 ON (t16.c11 = t17.c12) JOIN t1 t18 ON (t17.c11 = t18.c12) JOIN t1 t19 ON (t18.c11 = t19.c12) JOIN t1 t20 ON (t19.c11 = t20.c12); -- SESSION 2, alors que le SELECT de la sessions 1 est en cours : TRUNCATE TABLE t1; -- le truncate ne se termine pas -- SESSION 1 répond : count -------- 999999 (1 ligne) -- SESSION 2, TRUNCATE toujours en attente -- SESSION 1 COMMIT; COMMIT -- SESSION 2, dans la foulée du COMMIT de la session 1 on a le résultat suivant : TRUNCATE TABLE -- SESSION 1 SELECT count(*) FROM t1; -- le select ne se termine pas -- SESSION 2 COMMIT; COMMIT -- SESSION 1, dans la foulée du COMMIT de la session 2 on a le résultat suivant : count ------- 0 (1 ligne)

      PostgreSQL gère comme prévu cette commande DDL. Un TRUNCATE ne perturbe pas un SELECT un cours. Après que le résultat soit arrivé, il faut encore que la session du SELECT relâche son verrou (access share) sur la table pour que le TRUNCATE aboutisse. Dans l’autre sens il faut ensuite que la session du TRUNCATE valide pour qu’un nouveau SELECT ne soit pas mis en attente.
      A présent Oracle en version 11.2.0.2 avec un test similaire :

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production CREATE TABLE t1(c11 INTEGER PRIMARY KEY, C12 INTEGER); Table created. INSERT INTO t1(c11, c12) SELECT rownum, rownum FROM dual CONNECT BY LEVEL < 1000000; 999999 rows created. COMMIT; Commit complete. -- SESSION 1 SELECT count(*) FROM t1 t11 JOIN t1 t12 ON (t11.c11 = t12.c12) JOIN t1 t13 ON (t12.c11 = t13.c12) JOIN t1 t14 ON (t13.c11 = t14.c12) JOIN t1 t15 ON (t14.c11 = t15.c12) JOIN t1 t16 ON (t15.c11 = t16.c12) JOIN t1 t17 ON (t16.c11 = t17.c12) JOIN t1 t18 ON (t17.c11 = t18.c12) JOIN t1 t19 ON (t18.c11 = t19.c12) JOIN t1 t20 ON (t19.c11 = t20.c12); -- SESSION 2, alors que le SELECT de la sessions 1 est en cours : TRUNCATE TABLE t1; Table truncated -- SESSION 1 répond : * ERROR at line 2: ORA-08103: object no longer exists

      Là c’est clair : Oracle et PostgreSQL ne se comportent pas de la même façon. Ne tentez pas de faire un TRUNCATE sur une table alors que des sessions ont des requêtes en cours sur la table en question avec Oracle car le résultat risque de ne pas être sympathique ! C’est également souhaitable de ne pas lancer de TRUNCATE avec PostgreSQL alors que d’autres sessions travaillent. Vous n’aurez pas d’erreurs mais vous risquez les blocages illustrés par le test.
      Si vous souhaitez aller plus loin, refaites le test sous PostgreSQL en remplaçant TRUNCATE par DELETE pour vous convaincre que TRUNCATE n’est décidément pas l’équivalent d’un super DELETE sans clause WHERE au sens transactionnel. Je vous donne le nom de l’assassin avant la fin du film : vous n’aurez pas de "blocages" avec une session effectuant un DELETE et une autre effectuant un SELECT.

Mise à jour : 23/10/2016