[IGNITE-20453] Sql. Basic Multi Statement Support

• Type: Epic
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: sql
• Labels: ignite-3
• Epic Name: Basic Multi Statement Support
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Unassigned
• Reporter: Konstantin Orlov
• Created: 20/Sep/23 06:53
• Updated: 04/Jan/24 13:42

Description

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:

• It helps to decrease number of round trips between the application and the database server, which is positively affects performance (this can be achieved by using batching though)
• 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
• 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.

Let's support multi statement queries in Apache Ignite 3 to ease the migration to Ignite and improve UX by providing familiar and convenient way of working with database.

Requirements

1. It should be possible to start new transaction and commit it from a script
2. If there is no explicit active transaction (either passed as parameter to the API call or started from script), then every statement should be wrapped in its own transaction
3. It should not be possible to commit transaction passed as parameter to the API call
4. It should not be possible to start another transaction if there is an active transaction
5. It should not be possible to start transaction by executing a tx management statement in single statement mode
6. The execution of a multi statement query should emulates serial execution for all statements in the order they are enumerated in the script; as if statements had been executed one after another, serially, rather than concurrently
7. Multi statement query should do progress even if no one consumes the result

Issue Links

• is related to: IGNITE-19855 ODBC 3.0: Implement multiple queries execution
• mentioned in: Page Loading...