0

I need to take row level lock for update and at the same time allow other select queries to get their intended row which is not locked.

What I observed was if I take a lock on row1, no other select queries are allowed which are searching for other rows.

I have below table schema -

CREATE TABLE lock_test(
    id int NOT NULL DEFAULT unique_rowid(),
    col_with_unique_index text NOT NULL,
    other_col text NOT NULL,
    CONSTRAINT "primary" PRIMARY KEY (id ASC),
    UNIQUE INDEX unique_idx (col_with_unique_index ASC),
    FAMILY "primary"(id, col_with_unique_index, other_col));

Inserted below 2 rows -

insert into lock_test(col_with_unique_index, other_col) values('val1', 'some_val');
insert into lock_test(col_with_unique_index, other_col) values('val2', 'some_val');

Opened 2 terminals -

1st terminal -

begin;
select * from lock_test where col_with_unique_index = 'val1' for update;

2nd terminal -

select * from lock_test where col_with_unique_index = 'val2';

Expected 2nd terminal to show the result for val2 but it did not(went into waiting), instead after I executed commit in 1st terminal 2nd terminal showed the result.

I tried changing my where clause from col_with_unique_index to id which is the primary key here, and this time 2nd terminal did not wait and displayed the expected result.

I'm unable to understand the behaviour here. Can I only take row level locks if I have primary key in my where clause?

Shawnbam
  • 39
  • 7

3 Answers3

1

I tried changing my where clause from col_with_unique_index to id which is the primary key here, and this time 2nd terminal did not wait and displayed the expected result.

I'm unable to understand the behaviour here. Can I only take row level locks if I have primary key in my where clause?

I suspect what happened here is that the number of rows in the table was very small and the optimizer determined that it would be cheaper to scan the primary index than to look at the unique index. We have tuned these hueristics a bit lately to account for this contention. You can force a scan against the secondary index using: select * from lock_test@unique_idx where col_with_unique_index = 'val2'; Or you can add more data and run stats (should run automatically).

ajwerner
  • 191
  • 3
1

I have tested row-level locking with cockroachDB v22.1.

In terminal 1:

SELECT version();

                                        version
----------------------------------------------------------------------------------------
  CockroachDB CCL v22.1.12 (x86_64-pc-linux-gnu, built 2022/12/12 19:53:40, go1.17.11)
(1 row)

CREATE TABLE my_table (
    id      INT PRIMARY KEY,
    number  INT
);

INSERT INTO my_table(id, number)
VALUES
  (1, 100),
  (2, 200),
  (3, 300);

BEGIN TRANSACTION;

SELECT * FROM my_table WHERE id = 1 FOR UPDATE;

-- stop here

In terminal 2:

SELECT * FROM my_table; -- It will stop here

In terminal 1:

COMMIT TRANSACTION;

In terminal 2:

The output will continue.

  id | number
-----+---------
   1 |    100
   2 |    200
   3 |    300
(3 rows)
yoonghm
  • 4,198
  • 1
  • 32
  • 48
0

I redo the test yoonghm did, found there is another interesting thing.

While terminal1 is in TRANSACTION status, I issue the command in terminal2

SELECT * FROM my_table where id=2;

It works to return the result directly.

But if I issue the command in terminal2

SELECT * FROM my_table where number = 200;

It was hold until the transaction got commit.

Rethink about serialization isolation level, I believe it is expected behavior, the reason is below:

  1. SERIALIZATION isolation level guarantee no phantom read, so if the transaction's change will change the current select result, the current select should be hold until the transaction get completed(COMMIT or ROLLBACK)
  2. WHILE we select * FROM my_table; this select cannot get the updated result until the transaction completion, so it should be hold
  3. IF we select based on pKey, as the lock is added to the another pKey, the search result will not be changed no matter the transaction do the change or rollback, so it can return the result directly.
  4. If we select based on the other column, while it can be changed by the transaction, such like in transaction it update the my_table set number=200 where id=1 , it should change the select result, so this kind of query will be blocked as well.

TL,DR: in SERIALIZATION isolation level, the row lock will be mostly like the table lock, the only probability to use it as row lock is all select update and delete on the table is based on one unique key

Justin
  • 106
  • 1
  • 3