A multi-statement query is a collection of SQL statements that can be executed in one request. Supporting multi-statement queries may result in several benefits:
Most popular RDBMS systems, such as Oracle, MySQL, and PostgreSQL, already support multi-statement execution.
As a first step, the basic support will be introduced: regular queries with extension to manage transactions from the script. That is, two new statements should be introduced: START TRANSACTION and COMMIT. The former will start a new transaction, while the latter will complete it and release acquired resources (locks). The complete syntax of START TRANSACTION is as follow:
Since nested transactions currently are not allowed, an attempt to start a transaction from script when there is an active transaction (regardless if it was started from script earlier or passed explicitly via Java API) will result in error.
An attempt to invoke COMMIT when there is an explicit transaction will result in an error.
In native API, if a transaction is neither provided explicitly nor started explicitly by transaction management statement, the script will be executed in auto commit mode. That is, every statement will be executed in their own transaction.
In JDBC, transaction management statements will be allowed in auto commit mode only. An attempt to invoke a transaction management statement with disabled auto commit will result in an error.
In general, statements will be executed one by one in the order they are specified in the script. Independent statements (statements, that won't affect each other, like a group of SELECT statements; contrary, an example of dependent statements is CREATE TABLE and following INSERT to this table) may be executed concurrently.
Due to the lazy nature of SQL engine, the moment when the current statement is "complete" depends on the user who drains the cursor. To avoid dependency on a user's actions, it proposed to consider statement being "complete" as soon as first page is ready to be returned to the user (by "page" I mean some number of rows kept on heap in query cursor; currently, the cursor keeps 512 rows; this defined by constant, but may be moved to configuration and\or parameters of the query). In case of small result set (taking less than a single page) statements, the observable execution will be as if the next statement is executed strictly after the previous one is complete. In the case of a large result set (taking a few pages) statements anomalies are possible. Namely, results of a SELECT statement may be affected by following INSERT or DELETE.