From Eigenpedia

Jump to: navigation, search


Query/DML Rules

For execution of queries and DML, concurrency control in LucidDB follows a few simple rules:

  1. Only autocommit is supported (no user-level transactions).
  2. Queries never block DML statements (or other queries).
  3. DML statements never block queries.
  4. 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).
  5. DML statements do not block each other if they target different tables.
  6. 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).
  7. 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.

Page Multi-versioning

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


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 Rules

  1. DDL statements execute with an exclusive lock on the entire catalog.
  2. Query/DML preparation requires a shared lock on the entire catalog.
  3. Query execution does not lock the catalog unless the query references system tables.
  4. DML execution takes a very brief exclusive lock on the catalog at the end (to update target table rowcounts).
  5. 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.
  6. Note that DDL includes CREATE INDEX, which can be a very time-consuming operation for a table with existing data. (eigenjira:FRG-312: A future improvement will be to drop the catalog lock during execution of this statement.)
  7. 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:

  1. ANALYZE TABLE executes like a query, except that it requires a brief exclusive lock on the catalog at the end (to update stored statistics).
  2. ALTER TABLE REBUILD executes like a no-op DML statement.
  3. TRUNCATE TABLE executes like a DML statement.

JDBC Driver Rules

  1. 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.
  2. 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.
Personal tools