SQL: are multiple commands separated with ';' atomic or not?

Question

Hello.

Is it any information about commands separated with ';' inside one query - are they atomic or not? I'm interested in actual versions of popular databases: MySQL, MSSQL, SQLite etc? For example, if 100 clients will spam following query:

"insert into test ( name ) values ( '1' ); insert into test ( name ) values ( '2' )"

Will database contains items in sequence '1', '2', '1', '2' etc or it is possible for each command divided by ';' from 100 clients to race each over, resulting in '1', '1', '2', '1', '2', '2', '2' etc ?

Answer 1

Wrap it in a transaction. Otherwise, no, there is in general no guarantee that your statements will be atomic.

Answer 2

Within the batch they will execute in sequential order, but that's not your problem. If 100 clients emit that SQL at the same time there is no guarantee that the 2 INSERTS will execute one after the other without another INSERT executing in-between.

Answer 3

I guess it's a theoretical question, but why would you need the sequence?

You can't rely on the sequence in the database anyway, so add some sort of timestamp-column to your table, and let your clients initialize a timestamp variable which they add to their records when inserting.