CODEWITHSUNDEEP
mysqlsql
Paulius Stundžia
Paulius Stundžia

Reputation: 332

Is it worth wrapping a multiple row INSERT into a transaction

We have a service that collects and aggregates data from Kafka and then inserts it into MySQL with a multiple row insert, there are some values that are part of a unique key, so if those match it updates the non key values only, the statement looks something like this:

INSERT INTO table (c1, c2, c3, c4, c5, c6, c7, c8) VALUES 
                                              (?,?,?,?,?,?,?,?),
                                              (?,?,?,?,?,?,?,?),
                                              (?,?,?,?,?,?,?,?),
                                              ...
 ON DUPLICATE KEY UPDATE c6 = VALUES(c6), c7 = VALUES(c7), c8 = VALUES(c8)

My question is this: is it worth wrapping this statement into a transaction? I know there's a point in doing it if it were multiple INSERT statements, but what about a single multiple row statement? Isn't it wrapped implicitly? Can a row fail to insert, but others succeed? (It's important that either all rows get inserted/updated or none of them)

MySQL server:

Server version: 8.0.29-21.1 Percona XtraDB Cluster (GPL), Release rel21, Revision 250bc93, WSREP version 26.4.3

Upvotes: 0

Views: 311

Answers (1)

Oscar Robinson
Oscar Robinson

Reputation: 1003

You do not need a transaction if you are only running a single INSERT statement. A single statement is atomic in MySQL and will either be executed completely or not at all, even if it is an INSERT that is inserting multiple rows. This is stated in the docs:

By default, MySQL runs with autocommit mode enabled. This means that, when not otherwise inside a transaction, each statement is atomic, as if it were surrounded by START TRANSACTION and COMMIT. You cannot use ROLLBACK to undo the effect; however, if an error occurs during statement execution, the statement is rolled back.

However, you should be wary of doing inserts like this if there is a chance you may have many rows to insert at once. You might hit the max_allowed_packet limit for your MySQL server. If the number of rows to insert at a time is not limited elsewhere in your code, you might want to consider using batches for this.

Upvotes: 2

Related Questions