Concurrency and locks¶

Locks are used in SQream DB to provide consistency when there are multiple concurrent transactions updating the database.

Read only transactions are never blocked, and never block anything. Even if you drop a database while concurrently running a query on it, both will succeed correctly (as long as the query starts running before the drop database commits).

Locking modes¶

SQream DB has two kinds of locks:

exclusive - this lock mode prevents the resource from being modified by other statements

This lock tells other statements that they’ll have to wait in order to change an object.

DDL operations are always exclusive. They block other DDL operations, and update DML operations (insert and delete).
inclusive - For insert 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 insert or delete data from a table, but they’ll have to wait in order to run DDL.

When are locks obtained?¶

Operation	SELECT	INSERT	DELETE, TRUNCATE_IF_EXISTS	DDL
SELECT	Concurrent	Concurrent	Concurrent	Concurrent
INSERT	Concurrent	Concurrent	Concurrent	Wait
DELETE, TRUNCATE_IF_EXISTS	Concurrent	Concurrent	Wait	Wait
DDL	Concurrent	Wait	Wait	Wait

Statements that wait will exit with an error if they hit the lock timeout. The default timeout is 3 seconds, see statementLockTimeout.

Global locks¶

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

CREATE DATABASE

CREATE ROLE

CREATE TABLE

ALTER ROLE

ALTER TABLE

DROP DATABASE

DROP ROLE

DROP TABLE

GRANT

REVOKE

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:

Identify which statement has obtained locks
Understand if the statement is itself stuck, or waiting for another statement
Try to abort the offending statement
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.