Concurrency and locks

Statements in SQream DB can lock resources that are written or modified.

The locking system is the method of concurrency control within SQream DB. It stops transactions from overwriting each other and creating inconsistencies.

When a pending change from one statement conflicts with another, the later statement must wait for the earlier transaction to complete.

Locking modes

SQream DB has two locking modes:

  • exclusive - this lock mode prevents the resource from being read or modified by other statements (it excludes the object). It is usually obtained to modify the data or structure.

    This lock tells other statements that they’ll have to wait in order to read or write to the object.

    DDL operations are always exclusive.

  • inclusive - For some read operations, an inclusive lock is obtained on a specific object. This prevents other statements from obtaining an exclusive lock on the object.

    This lock allows other statements to read data from the object, but they’ll have to wait in order to modify it.

When are locks obtained?

Operation SELECT DML (INSERT) DML (DELETE, TRUNCATE) DDL
SELECT Concurrent Concurrent Concurrent Concurrent
DML (INSERT) Concurrent Concurrent Concurrent Block
DML (DELETE, TRUNCATE) Concurrent Concurrent Wait Block
DDL Concurrent Block Block Block

Note

  • A DDL operation will block all other statements from requesting a lock, and the statement will fail with an error.
  • DML operations will cause other statements to wait, rather than block. Because most locks are short-lived, SQream DB will wait for a period of 3 seconds before giving up and returning an error. This parameter is called statementLockTimeout and is modifiable.

Global locks

Some operations require exclusive global locks at the cluster level. These usually short-lived locks will be obtained for the following operations:

Monitoring locks

Monitoring locks across the cluster can be useful when transaction contention takes place, and statements appear “stuck” while waiting for a previous statement to release locks.

The utility SHOW_LOCKS can be used to see the active locks.

In this example, we create a table based on results (CREATE TABLE AS), but we are also effectively dropping the previous table (by using OR REPLACE which also drops the table). Thus, SQream DB applies locks during the table creation process to prevent the table from being altered during it’s creation.

t=> SELECT SHOW_LOCKS();
statement_id | statement_string                                                                                | username | server       | port | locked_object                   | lockmode  | statement_start_time | lock_start_time
-------------+-------------------------------------------------------------------------------------------------+----------+--------------+------+---------------------------------+-----------+----------------------+--------------------
287          | CREATE OR REPLACE TABLE nba2 AS SELECT "Name" FROM nba WHERE REGEXP_COUNT("Name", '( )+', 8)>1; | sqream   | 192.168.1.91 | 5000 | database$t                      | Inclusive | 2019-12-26 00:03:30  | 2019-12-26 00:03:30
287          | CREATE OR REPLACE TABLE nba2 AS SELECT "Name" FROM nba WHERE REGEXP_COUNT("Name", '( )+', 8)>1; | sqream   | 192.168.1.91 | 5000 | globalpermission$               | Exclusive | 2019-12-26 00:03:30  | 2019-12-26 00:03:30
287          | CREATE OR REPLACE TABLE nba2 AS SELECT "Name" FROM nba WHERE REGEXP_COUNT("Name", '( )+', 8)>1; | sqream   | 192.168.1.91 | 5000 | schema$t$public                 | Inclusive | 2019-12-26 00:03:30  | 2019-12-26 00:03:30
287          | CREATE OR REPLACE TABLE nba2 AS SELECT "Name" FROM nba WHERE REGEXP_COUNT("Name", '( )+', 8)>1; | sqream   | 192.168.1.91 | 5000 | table$t$public$nba2$Insert      | Exclusive | 2019-12-26 00:03:30  | 2019-12-26 00:03:30
287          | CREATE OR REPLACE TABLE nba2 AS SELECT "Name" FROM nba WHERE REGEXP_COUNT("Name", '( )+', 8)>1; | sqream   | 192.168.1.91 | 5000 | table$t$public$nba2$Update      | Exclusive | 2019-12-26 00:03:30  | 2019-12-26 00:03:30

Troubleshooting locks

Sometimes, a rare situation can occur where a lock is never freed.

The workflow for troubleshooting locks is:

  1. Identify which statement has obtained locks
  2. Understand if the statement is itself stuck, or waiting for another statement
  3. Try to abort the offending statement
  4. Force the stale locks to be removed

Example

We will assume that the statement from the previous example is stuck (statement #287). We can attempt to abort it using STOP_STATEMENT:

t=> SELECT STOP_STATEMENT(287);
executed

If the locks still appear in the SHOW_LOCKS utility, we can force remove the stale locks:

t=> SELECT RELEASE_DEFUNCT_LOCKS();
executed

Warning

This operation can cause some statements to fail on the specific worker on which they are queued. This is intended as a “last resort” to solve stale locks.