MariaDB Prepared Statements, Transactions and Multi-Row Inserts

Last week at the MariaDB/MySQL Developer Training we had one participant asking some tricky questions I did not know the answer by heart.

Also MariaDB documentation was not too verbose (here and here).

So time to do some experiments:

Prepared Statements and Multi-Row Inserts

SQL> PREPARE stmt1 FROM 'INSERT INTO `test`.`test` (`data`) VALUES (?), (?), (?)';
Statement prepared
SQL> SET @d1 = 'Bli';
SQL> SET @d2 = 'Bla';
SQL> SET @d3 = 'Blub';
SQL> EXECUTE stmt1 USING @d1, @d2, @d3;
Query OK, 3 rows affected (0.010 sec)
Records: 3  Duplicates: 0  Warnings: 0
SQL> DEALLOCATE PREPARE stmt1;
SQL> SELECT * FROM test;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | Bli  | 2019-04-15 17:26:22 |
|  2 | Bla  | 2019-04-15 17:26:22 |
|  3 | Blub | 2019-04-15 17:26:22 |
+----+------+---------------------+

Prepared Statements and Transactions

SQL> SET SESSION autocommit=Off;
SQL> START TRANSACTION;
SQL> PREPARE stmt2 FROM 'INSERT INTO `test`.`test` (`data`) VALUES (?)';
Statement prepared

SQL> SET @d1 = 'BliTrx';
SQL> EXECUTE stmt2 USING @d1;
Query OK, 1 row affected (0.000 sec)

SQL> SET @d1 = 'BlaTrx';
SQL> EXECUTE stmt2 USING @d1;
Query OK, 1 row affected (0.000 sec)
SQL> COMMIT;

-- Theoretically we should do a START TRANSACTION; here again...
SQL> SET @d1 = 'BlubTrx';
SQL> EXECUTE stmt2 USING @d1;
Query OK, 1 row affected (0.000 sec)
SQL> ROLLBACK;

SQL> DEALLOCATE PREPARE stmt2;
SQL> SELECT * FROM test;
+----+---------+---------------------+
| id | data    | ts                  |
+----+---------+---------------------+
| 10 | BliTrx  | 2019-04-15 17:33:30 |
| 11 | BlaTrx  | 2019-04-15 17:33:39 |
+----+---------+---------------------+

Prepared Statements and Transactions and Multi-Row Inserts

SQL> SET SESSION autocommit=Off;
SQL> START TRANSACTION;
SQL> PREPARE stmt3 FROM 'INSERT INTO `test`.`test` (`data`) VALUES (?), (?), (?)';
Statement prepared

SQL> SET @d1 = 'Bli1Trx';
SQL> SET @d2 = 'Bla1Trx';
SQL> SET @d3 = 'Blub1Trx';
SQL> EXECUTE stmt3 USING @d1, @d2, @d3;
Query OK, 3 rows affected (0.000 sec)
SQL> COMMIT;

-- Theoretically we should do a START TRANSACTION; here again...
SQL> SET @d1 = 'Bli2Trx';
SQL> SET @d2 = 'Bla2Trx';
SQL> SET @d3 = 'Blub2Trx';
SQL> EXECUTE stmt3 USING @d1, @d2, @d3;
Query OK, 3 rows affected (0.000 sec)
SQL> ROLLBACK;

-- Theoretically we should do a START TRANSACTION; here again...
SQL> SET @d1 = 'Bli3Trx';
SQL> SET @d2 = 'Bla3Trx';
SQL> SET @d3 = 'Blub3Trx';
SQL> EXECUTE stmt3 USING @d1, @d2, @d3;
Query OK, 3 rows affected (0.001 sec)
SQL> COMMIT;

SQL> DEALLOCATE PREPARE stmt3;
SQL> SELECT * FROM test;
+----+----------+---------------------+
| id | data     | ts                  |
+----+----------+---------------------+
|  1 | Bli1Trx  | 2019-04-15 17:37:50 |
|  2 | Bla1Trx  | 2019-04-15 17:37:50 |
|  3 | Blub1Trx | 2019-04-15 17:37:50 |
|  7 | Bli3Trx  | 2019-04-15 17:38:38 |
|  8 | Bla3Trx  | 2019-04-15 17:38:38 |
|  9 | Blub3Trx | 2019-04-15 17:38:38 |
+----+----------+---------------------+

Seems all to work as expected. Now we know it for sure!