For execution of queries and DML, concurrency control in LucidDB follows a few simple rules:
- Only autocommit is supported (no user-level transactions).
- Queries never block DML statements (or other queries).
- DML statements never block queries.
- Any query or DML statement sees a snapshot-consistent view of the database as of the point in time at which the statement started execution (or as of a particular label if one is set for the statement's session; see LucidDbWarehouseLabels for more information on this feature).
- DML statements do not block each other if they target different tables.
- DML statements which target the same table cause a conflict; the DML statement which starts later (while an earlier DML statement is still in progress on the same table) will fail immediately (no wait).
- DML statements are atomic; if they fail, they are completely rolled back, leaving behind no partial effect on the target table. LucidDbSessionParameters describes parameters which can be set to allow row-level errors to be treated as warnings; these do not count as failure unless the requested threshold is reached.
Snapshot consistency in LucidDB is defined as follows:
Given a fixed schema, a statement X (either DML or query) which starts at time T sees the state of the database as static for its duration, with the state seen defined as the cumulative result of all DML and TRUNCATE statements which committed before time T. Effects of concurrent DML statements are not visible from the context of X, regardless of whether those statements commit before X does.
LucidDB implements the concurrency control semantics above via a page versioning technique. When a DML statement modifies an existing page, it creates a new version rather than updating in place. The old page version is left behind in case concurrent readers still need it as part of their snapshot.
Old page versions are not reclaimed automatically, meaning that as DML statements are executed, old unneeded page versions will accumulate. Their storage can be reclaimed via the ALTER SYSTEM DEALLOCATE OLD statement (a LucidDB-specific command). This statement reclaims any old page versions which are no longer needed. It can be executed concurrently with any other statement. Its operation is quite fast and efficient because it only needs to scan physical metadata to decide which pages can be reclaimed; it does not need to look at the contents of any data pages. Best practice is to schedule it to run as a final step after ETL jobs or maintenance updates, and in particular after data removal operations such as
- DROP TABLE
- TRUNCATE TABLE
- ALTER TABLE REBUILD
Note that once LucidDB's data file grows in the filesystem, it never shrinks, regardless of whether pages have been reclaimed within it and not yet reused. More information is available in LucidDbDataStorageAndAccess.
- DDL statements execute with an exclusive lock on the entire catalog.
- Query/DML preparation requires a shared lock on the entire catalog.
- Query execution does not lock the catalog unless the query references system tables.
- DML execution takes a very brief exclusive lock on the catalog at the end (to update target table rowcounts).
- The catalog lock has wait-as-long-as-needed semantics; it is safe to request any of these operations concurrently without worrying about unexpected timeout/deadlock.
- Note: DDL includes CREATE INDEX, which keeps the catalog locked while it runs; CREATE INDEX can take a significant amount of time to run for a table with existing data. (eigenjira:FRG-312: A future improvement will be to drop the catalog lock during execution of this statement.)
- A DDL statement which attempts to DROP or REPLACE an object referenced by a prepared or executing query or DML statement will fail immediately. (This is enforced via an "object-in-use" state maintained for each object; the catalog lock is not involved.)
Special optimizations are made for certain quasi-DDL statements:
- ANALYZE TABLE executes like a no-op DML statement.
- ALTER TABLE REBUILD executes like a no-op DML statement.
- TRUNCATE TABLE executes like a DML statement.
- ALTER TABLE ADD COLUMN executes like a DML statement, with additional nuances as explained in the design docs.
As with DML statements, these special statements acquire a brief exclusive lock on the catalog at the end of their execution to update information such as stored statistics and timestamps.
For backup/restore concurrency notes, see LucidDbBackupRestore.
JDBC Driver Rules
- The LucidDB JDBC driver is thread-safe across connections, but NOT thread-safe within a connection. What this means is that if a client creates connection C1 and uses it in thread T1, and creates another connection C2 and uses it in thread T2, all will be well. But if threads T1 and T2 both make calls on C1 concurrently, undefined behavior will occur. Note that in JDBC, a Connection spans a family of related objects; for example, a cursor is represented by a ResultSet. For threading purposes, all sub-objects created directly or indirectly via a connection should be considered to be part of that connection.
- As mentioned above, each SQL statement is executed as an individual autocommit transaction. The JDBC spec implies that this includes overlapping statements and queries from within the same session, but LucidDB prevents multiple statements from being active on the same session. So, for example, if a client creates a connection and opens a cursor, a new transaction starts as a result. If the client then attempts to execute an INSERT statement on the same connection, the INSERT will fail with an error about a cursor still being open. The same is true for an attempt to create a second cursor while the first one is still open; this will fail with the same error message. This behavior is non-conforming, but avoids the possibility of a session "locking itself out". Applications should close cursors as soon as all results have been fetched, and should use multiple connections in the case where simultaneous cursors are needed.