La question de savoir si une instruction INSERT INTO t2 SELECT * FROM t1; verrouille la table t1 lors de la sélection nécessite une analyse approfondie. Avant d'examiner le comportement des verrous dans GreatSQL, il est crucial de comprendre que les niveaux d'isolation des transactions influencent différemment l'application des verrous.
Configuration expérimentale
Créons deux tables, t1 et t2, pour nos tests. La table t1 aura une clé primaire sur la colonne id et un index normal sur la colonne c1.
greatsql> CREATE TABLE t1 (id INT PRIMARY KEY, c1 VARCHAR(10), c2 DATETIME, KEY idx_c1(c1));
greatsql> CREATE TABLE t2 LIKE t1;
Ensuite, générons des données de test pour la table t1 à l'aide d'une procédure stockée.
greatsql> DELIMITER //
CREATE OR REPLACE PROCEDURE p1()
BEGIN
DECLARE counter INT DEFAULT 0;
WHILE counter < 5 DO
INSERT INTO t1 (id, c1, c2) VALUES (counter * 2, ROUND(RAND() * 10000), NOW());
SET counter = counter + 1;
END WHILE;
END //
DELIMITER ;
greatsql> CALL p1();
greatsql> SELECT * FROM t1;
+----+------+---------------------+
| id | c1 | c2 |
+----+------+---------------------+
| 0 | 2660 | 2024-02-21 15:45:00 |
| 2 | 4627 | 2024-02-21 15:45:00 |
| 4 | 5158 | 2024-02-21 15:45:00 |
| 6 | 1907 | 2024-02-21 15:45:00 |
| 8 | 4061 | 2024-02-21 15:45:00 |
+----+------+---------------------+
Niveau d'isolation REPEATABLE READ
Vérifions le niveau d'isolation actuel de la transaction.
greatsql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
Dans la Connexion 1, nous débutons une transaction et exécutons l'instruction INSERT...SELECT.
greatsql> SELECT ps_current_thread_id(); -- Obtenir l'ID de la connexion actuelle (ex: 92)
greatsql> BEGIN;
greatsql> INSERT INTO t2 SELECT * FROM t1;
Pendant ce temps, dans la Connexion 2, nous commençons une autre transaction et tentons d'insérer une nouvelle ligne dans t1.
greatsql> SELECT ps_current_thread_id(); -- Obtenir l'ID de la connexion actuelle (ex: 93)
greatsql> BEGIN;
greatsql> INSERT INTO t1 (id, c1) VALUES (1, 'a');
En examinant les verrous actifs dans la Connexion 3, nous constatons le comportement suivant :
greatsql> SELECT THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM data_locks;
+-----------+-------------+------------+-----------+------------------------+-------------+------------------------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+-------------+------------+-----------+------------------------+-------------+------------------------+
| 93 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| 93 | t1 | PRIMARY | RECORD | X,GAP,INSERT_INTENTION | WAITING | 2 |
| 92 | t2 | NULL | TABLE | IX | GRANTED | NULL |
| 92 | t1 | NULL | TABLE | IS | GRANTED | NULL |
| 92 | t1 | PRIMARY | RECORD | S | GRANTED | supremum pseudo-record |
| 92 | t1 | PRIMARY | RECORD | S | GRANTED | 0 |
| 92 | t1 | PRIMARY | RECORD | S | GRANTED | 2 |
| 92 | t1 | PRIMARY | RECORD | S | GRANTED | 4 |
| 92 | t1 | PRIMARY | RECORD | S | GRANTED | 6 |
| 92 | t1 | PRIMARY | RECORD | S | GRANTED | 8 |
+-----------+-------------+------------+-----------+------------------------+-------------+------------------------+
Dans le niveau REPEATABLE READ, l'instruction INSERT...SELECT sur t1 applique des verrous Shared (S) sur chaque enregistrement lu, ainsi que sur le pseudo-enregistrement supremum. Ces verrous, de type Nextkey lock, empêchent la Connexion 2 d'insérer une nouvelle ligne dans t1, car les verrous S sont incompatibles avec les verrous X,GAP,INSERT_INTENTION requis pour l'insertion. Le système ne s'appuie pas sur le MVCC pour cette opération, mais utilise plutôt des verrous pour garantir la cohérence. L'objectif est d'empêcher les lectures non répétables et les lectures fantômes durant l'exécution de l'instruction.
Niveau d'isolation READ COMMITTED
Changeons le niveau d'isolation de la transaction.
greatsql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
greatsql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ COMMITTED |
+-----------------------+----------------+
Dans la Connexion 1, nous exécutons à nouveau l'instruction INSERT...SELECT.
greatsql> BEGIN;
greatsql> INSERT INTO t2 SELECT * FROM t1;
Dans la Connexion 2, nous tentons la même insertion.
greatsql> BEGIN;
greatsql> INSERT INTO t1 (id, c1) VALUES (1, 'a');
En vérifiant les verrous dans la Connexion 3 :
greatsql> SELECT THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM data_locks;
+-----------+-------------+------------+-----------+-----------+-------------+-----------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+-------------+------------+-----------+-----------+-------------+-----------+
| 104 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| 103 | t2 | NULL | TABLE | IX | GRANTED | NULL |
+-----------+-------------+------------+-----------+-----------+-------------+-----------+
Dans le niveau READ COMMITTED, la Connexion 2 n'est pas bloquée. Les deux transactions n'acquièrent que des verrous d'intention exclusifs (IX) sur leurs tables respectives. Cela démontre une différence significative dans la gestion des verrous.
Conclusion
L'application de verrous par une instruction INSERT...SELECT sur la table source dépend du niveau d'isolation de la transaction. Au niveau REPEATABLE READ, des verrous partagés (S), de type Nextkey lock, sont appliqués sur les enregistrements de la table source, ce qui peut bloquer les opérations DML d'autres transacitons sur cette table. En revanche, au niveau READ COMMITTED, aucun verrou n'est appliqué sur la table source, permettant ainsi aux autres transactions d'effectuer des opérations DML sans blocage.