Locking in CockroachDB, Part 3 - Killing Sessions and Transactions

Download full source code.

This is the third in a series of posts on locking rows and CockroachDB. The first post showed how rows or whole tables could be locked, and how to avoid/minimize this. The second showed how to identify transaction that are locking rows.

This post will show how to terminate transactions locking rows.

Create some locks

First, we need to create some locks. Run the following in a SQL shell to CockroachDB -

begin; select * from products where product_id >= 5 for update; # don't commit or rollback

Finding the session

Transactions are not killed directly; instead, the session running the transaction is cancelled. To find the session associated with the transaction, join crdb_internal.cluster_locks and the crdb_internal.cluster_sessions tables.

Both tables have the transaction id. In the crdb_internal.cluster_locks table, it is called txn_id, and in the crdb_internal.cluster_sessions table, it is called kv_txn. Not only are the names different, but the formats are different - the txn_id is a UUID, while the kv_txn is a string, making a conversion necessary.

To find the session id for a transaction that is locking rows, use the following query:

select 
    cs.session_id, 
    cs.client_address, 
    cs.application_name, 
    cl.duration, 
    cs.user_name, 
    cl.table_name, 
    cl.schema_name, 
    cl.lock_key_pretty, 
    cl.txn_id, 
    cl.lock_strength, 
    cl.duration 
from 
    crdb_internal.cluster_locks as cl
join 
    crdb_internal.cluster_sessions as cs 
on  
    cast(cs.kv_txn as UUID) = cl.txn_id;

This will return all the locked rows, how long the lock has been held, and the session id holding the lock. The output will look like -

Session id                         User   Application name      Duration       Table      Schema    Lock key                 Txn id                         Lock strength
186a777f7700b1ad0000000000000001 - root - $ cockroach sql - 00:00:06.1331380 - products - public - /Table/123/1/5/0 - 6941ab73-f84a-4132-ad4b-2b943eb2a888 - Exclusive
186a777f7700b1ad0000000000000001 - root - $ cockroach sql - 00:00:06.1331350 - products - public - /Table/123/1/6/0 - 6941ab73-f84a-4132-ad4b-2b943eb2a888 - Exclusive
186a777f7700b1ad0000000000000001 - root - $ cockroach sql - 00:00:06.1331350 - products - public - /Table/123/1/7/0 - 6941ab73-f84a-4132-ad4b-2b943eb2a888 - Exclusive
186a777f7700b1ad0000000000000001 - root - $ cockroach sql - 00:00:06.1331300 - products - public - /Table/123/1/8/0 - 6941ab73-f84a-4132-ad4b-2b943eb2a888 - Exclusive
186a777f7700b1ad0000000000000001 - root - $ cockroach sql - 00:00:06.1331300 - products - public - /Table/123/1/9/0 - 6941ab73-f84a-4132-ad4b-2b943eb2a888 - Exclusive
186a777f7700b1ad0000000000000001 - root - $ cockroach sql - 00:00:06.1331290 - products - public - /Table/123/1/10/0 - 6941ab73-f84a-4132-ad4b-2b943eb2a888 - Exclusive

Killing the session

To kill the session, run -

cancel session 186a777f7700b1ad0000000000000001; # replace with your session id

That’s it!

This can easily be turned into an application or script.

C# application

I have attached a C# application.

Download full source code.