Locking in CockroachDB, Part 2 - Finding Locked Rows
This is part 2 of a series on locking in CockroachDB. The first post showed how you can end up with locked rows or tables, and how to keep your locks to the fewest number of rows by using indexes.
But even with the best use of indexes, you can still end up with rows that get locked. This can be worsened if you have some other issue, like a statement or transaction that is slow or doesn’t complete, these locks will be held for too long.
Here is an example that will result in some rows being locked.
My table
I have a products table with indexes on product_id, product_category and price.
The value are created by a library called Bogus, so they are random, but realistic enough for my purposes.
product_id | name | product_category | price | code
-------------+----------------------------+------------------+-------+-----------
1 | Ergonomic Granite Sausages | 1 | 13.91 | 65662748
2 | Gorgeous Cotton Computer | 1 | 88.60 | 36621408
3 | Gorgeous Frozen Hat | 4 | 51.57 | 51949419
4 | Awesome Rubber Chips | 3 | 60.88 | 23445857
5 | Incredible Plastic Pants | 3 | 9 | 02021362
6 | Handmade Fresh Keyboard | 2 | 68.05 | 59125532
7 | Licensed Concrete Soap | 4 | 63.24 | 10179406
8 | Generic Rubber Bacon | 1 | 72.31 | 07966279
9 | Fantastic Wooden Fish | 4 | 73.05 | 68922436
10 | Unbranded Metal Shirt | 4 | 78.59 | 29417476Locking some rows
Just like in the previous post, locking rows is done inside a transaction that is kept open -
BEGIN; SELECT * FROM products WHERE product_id <= 5 FOR UPDATE; -- don't commit or rollback yetTo find what is locked, run -
select * from crdb_internal.cluster_locks;This will return 5 rows. There are quite a few columns, so I have removed some for clarity -
table_id | schema_name | table_name | index_name | lock_key_pretty | txn_id | lock_strength | duration | isolation_level
-----------+-------------+------------+------------+------------------+--------------------------------------+---------------+-----------------+------------------
123 | salesdb | products | | /Table/123/1/1/0 | cea02774-e2b1-44aa-8e14-78a9d31c8a6e | Exclusive | 00:05:09.895328 | SERIALIZABLE
123 | salesdb | products | | /Table/123/1/2/0 | cea02774-e2b1-44aa-8e14-78a9d31c8a6e | Exclusive | 00:05:09.895319 | SERIALIZABLE
123 | salesdb | products | | /Table/123/1/3/0 | cea02774-e2b1-44aa-8e14-78a9d31c8a6e | Exclusive | 00:05:09.895316 | SERIALIZABLE
123 | salesdb | products | | /Table/123/1/4/0 | cea02774-e2b1-44aa-8e14-78a9d31c8a6e | Exclusive | 00:05:09.895308 | SERIALIZABLE
123 | salesdb | products | | /Table/123/1/5/0 | cea02774-e2b1-44aa-8e14-78a9d31c8a6e | Exclusive | 00:05:09.895306 | SERIALIZABLEThere is a lot of useful information here -
- table_id, this will help later when looking up indexes.
- schema_name and table_name identify the table that has locks.
- index_name, when the query uses the primary key alone, this is blank.
- lock_key_pretty, identifies the rows that are locked. For this one, its table 123, index 1, and rows with the primary key 1,2,3,4,5. I don’t know what the last 0 refers to, and Cockroach Labs has not made it easy to figure out.
- txn_id, the id of the transaction that is locking the rows, will be useful when cancelling the lock in the next post.
- duration, how long the lock has been in place.
More on lock_key_pretty
Some of its values are easy enough to figure out. But not everything.
When locking with a query that uses a non-primary key index, the format changes a little.
But first find the indexes on a table, there are at least two ways.
The first is -
show index from products;But for my purposes here, I prefer -
select index_id, index_name, index_type from crdb_internal.table_indexes where descriptor_name = 'products';And this is the result -
index_id | index_name | index_type
-----------+------------------------------+-------------
1 | pk_products | primary
2 | ix_products_price | secondary
4 | ix_products_product_category | secondaryThe primary key index always seems to be number 1, the price index is number 2, and the product_category index is number 4.
Now run a query like this -
BEGIN; SELECT * FROM products@ix_products_price WHERE price > 60 FOR UPDATE;My query returns 7 rows -
product_id | name | product_category | price | code
-------------+--------------------------+------------------+-------+-----------
2 | Gorgeous Cotton Computer | 1 | 88.60 | 36621408
4 | Awesome Rubber Chips | 3 | 60.88 | 23445857
6 | Handmade Fresh Keyboard | 2 | 68.05 | 59125532
7 | Licensed Concrete Soap | 4 | 63.24 | 10179406
8 | Generic Rubber Bacon | 1 | 72.31 | 07966279
9 | Fantastic Wooden Fish | 4 | 73.05 | 68922436
10 | Unbranded Metal Shirt | 4 | 78.59 | 29417476Then run -
select * from crdb_internal.cluster_locks;Again, here is a trimmed-down set of the results -
table_id | schema_name | table_name | index_name | lock_key_pretty | txn_id | duration
-----------+-------------+------------+-------------------+-------------------------+--------------------------------------+------------------
123 | salesdb | products | | /Table/123/1/2/0 | 54737f41-5e14-4fb3-adc5-2bf3e8a99c36 | 00:04:34.448656
123 | salesdb | products | | /Table/123/1/4/0 | 54737f41-5e14-4fb3-adc5-2bf3e8a99c36 | 00:04:34.448651
123 | salesdb | products | | /Table/123/1/6/0 | 54737f41-5e14-4fb3-adc5-2bf3e8a99c36 | 00:04:34.448649
123 | salesdb | products | | /Table/123/1/7/0 | 54737f41-5e14-4fb3-adc5-2bf3e8a99c36 | 00:04:34.448649
123 | salesdb | products | | /Table/123/1/8/0 | 54737f41-5e14-4fb3-adc5-2bf3e8a99c36 | 00:04:34.448648
123 | salesdb | products | | /Table/123/1/9/0 | 54737f41-5e14-4fb3-adc5-2bf3e8a99c36 | 00:04:34.448647
123 | salesdb | products | | /Table/123/1/10/0 | 54737f41-5e14-4fb3-adc5-2bf3e8a99c36 | 00:04:34.448646
123 | salesdb | products | ix_products_price | /Table/123/2/60.88/4/0 | 54737f41-5e14-4fb3-adc5-2bf3e8a99c36 | 00:04:34.450732
123 | salesdb | products | ix_products_price | /Table/123/2/63.24/7/0 | 54737f41-5e14-4fb3-adc5-2bf3e8a99c36 | 00:04:34.450728
123 | salesdb | products | ix_products_price | /Table/123/2/68.05/6/0 | 54737f41-5e14-4fb3-adc5-2bf3e8a99c36 | 00:04:34.450727
123 | salesdb | products | ix_products_price | /Table/123/2/72.31/8/0 | 54737f41-5e14-4fb3-adc5-2bf3e8a99c36 | 00:04:34.450726
123 | salesdb | products | ix_products_price | /Table/123/2/73.05/9/0 | 54737f41-5e14-4fb3-adc5-2bf3e8a99c36 | 00:04:34.450725
123 | salesdb | products | ix_products_price | /Table/123/2/78.59/10/0 | 54737f41-5e14-4fb3-adc5-2bf3e8a99c36 | 00:04:34.450724
123 | salesdb | products | ix_products_price | /Table/123/2/88.6/2/0 | 54737f41-5e14-4fb3-adc5-2bf3e8a99c36 | 00:04:34.450723Some things to note -
- there are 14 rows returned, twice the number of rows returned by the actual query.
- the first seven, seem to use the primary key index. In
lock_key_pretty, the index 1 is referenced. - the second set of seven rows use the
ix_products_priceindex. Thelock_key_prettyhere follows a slightly different format than the shown above.- 123 refers to the table as before
- 2 refers to the index; this matches the price index on the list of indexes found earlier
- the next section is the price as stored in the row
- the following one is the primary key value
- the last, I don’t know
- txn_id is the transaction holding the lock
- duration, how long the lock has been held.
If your primary key, or the index column is a GUID, a truncated and harder to read representation of the will be shown in the lock_key_pretty, but with some effort you can figure out which row it refers to.
Locking the whole table
If the query doesn’t use an index, a table scan will be performed, and the whole table will be locked.
The code column is not indexed.
BEGIN; SELECT * FROM products where code = '36621408' FOR UPDATE;Now check for locked rows -
select * from crdb_internal.cluster_locks; table_id | schema_name | table_name | index_name | lock_key_pretty | txn_id | duration
-----------+-------------+------------+------------+-------------------+--------------------------------------+------------------
123 | salesdb | products | | /Table/123/1/1/0 | 05843b20-6149-4276-bb06-ebb2e16e51b3 | 00:03:16.075405
123 | salesdb | products | | /Table/123/1/2/0 | 05843b20-6149-4276-bb06-ebb2e16e51b3 | 00:03:16.075393
123 | salesdb | products | | /Table/123/1/3/0 | 05843b20-6149-4276-bb06-ebb2e16e51b3 | 00:03:16.075388
123 | salesdb | products | | /Table/123/1/4/0 | 05843b20-6149-4276-bb06-ebb2e16e51b3 | 00:03:16.075383
123 | salesdb | products | | /Table/123/1/5/0 | 05843b20-6149-4276-bb06-ebb2e16e51b3 | 00:03:16.075379
123 | salesdb | products | | /Table/123/1/6/0 | 05843b20-6149-4276-bb06-ebb2e16e51b3 | 00:03:16.075374
123 | salesdb | products | | /Table/123/1/7/0 | 05843b20-6149-4276-bb06-ebb2e16e51b3 | 00:03:16.075369
123 | salesdb | products | | /Table/123/1/8/0 | 05843b20-6149-4276-bb06-ebb2e16e51b3 | 00:03:16.075364
123 | salesdb | products | | /Table/123/1/9/0 | 05843b20-6149-4276-bb06-ebb2e16e51b3 | 00:03:16.07536
123 | salesdb | products | | /Table/123/1/10/0 | 05843b20-6149-4276-bb06-ebb2e16e51b3 | 00:03:16.075357The whole table is locked, but like when the primary key is used, there is no index_name, and the index id in the lock_key_pretty shows 1.
Conclusion
You should now know how to find locked data and identify what index was being used in the query that caused the lock.
If no index_name is listed, it is likely the primary key index, or no index was used in the query.
If no index was used in the query, the whole table will be locked. If the primary key was used, there should be a subset of rows locked.
This should help you identify the code that caused the lock.
In the next post, I’ll show how to use this information along with the the session, role, and application that caused the lock, to kill the transaction that is holding the rows.