Motivation

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:

1. It helps to decrease number of round trips between the application and the database server
   
   
2. It may significantly improve UX: during maintaining, user may submit an entire migration/initialization script to the database server without need to split this script on independent statements by hand
   
   
3. In distributed system, some features (like shared mutable state, system and user defined variables) are easier to introduce for multi-statement only, rather than for general case

Most popular RDBMS systems, such as Oracle, MySQL, and PostgreSQL, already support multi-statement execution.

Description

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:

<start transaction statement> ::=
    START TRANSACTION [ <transaction access mode> ]

<transaction access mode> ::= READ ONLY | READ WRITE

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.

Tickets

Unable to render Jira issues macro, execution error.