queuing multiple SQL queries as one string, server behaviour?

When you have eg. 3 queries in a row that you submit to a SQL database as a single string, separated by semicolons, and anot开发者_高级运维her user does that exact same thing at the same time, will our queries be seen as a "group" or "queue" of queries which will always execute in the same, logical order per user, or can the queue for these queries get "mixed up", ending up with both our queries being fired one by one?

So basically I want to know which one of the following situations both MySQL and SQL Servers will behave like when this occurs:

• execution path situation 1:

• user 1 query 1, user 1 query 2, user 1 query 3, user 2 query 1, user 2 query 2, user 2 query 3

• execution path situation 2:

• user 1 query 1, user 2 query 1, user 1 query 2, user 2 query 2, user 1 query 3, user 2 query 3

This depends on whether your database sees this as a number of separate queries or as a single transaction. If it sees it as a transaction, the order won't matter. If you want to ensure it sees it as a single transaction, there are ways to signal to the database that it should create a transaction for your queries. The database (should) ensure four basic properties (ACID):

• Atomic - The entire transaction succeeds or fails.
• Consist - The database will always be left in a consistent state, regardless of whether a transaction succeeds or fails.
• Isolated - Multiple transactions being processed at the same time cannot interfere with each other.
• Durable - The database must be able to recover committed data after software and hardware failures.

Isolation is the one relevant to your case. Your queries should not be able to sabotage another person's, so you should not need to worry about other users submitting queries at the same time.

Also, I doubt that your database is running off a single processor. It's likely that the linear paths you described wouldn't happen simply because the database can handle multiple queries simultaneously.

For safety's sake, I'd start your query with a BEGIN TRANSACTION and end it with COMMIT TRANSACTION (or whatever syntax your database requires). If the database does see it as a number of separate queries, you could have problems if they do any writing to the database. Best to use a transaction.

It depends on the database engine, the locking mechanism in place, transactions, and other factors. Really, it doesn't matter... as long as they are all select queries. If some or all of the queries include write operations, you should encase each batch in a transaction to ensure atomicity.

For SQL Server the queries for a particular user will be run in the order specified, but they can be interleaved with the statements of other users, subject to when resources become available. Inside a transaction you can guarantee consistency of the data, but your statements can still potentially be interleaved with statements from other users. The types of statements and locks you use will determine whether other statements are able to access the same tables and rows that you're working with in your queries.