[MDEV-21314] Range Locking: individual rows are locked when scanning PK Created: 2019-12-13  Updated: 2021-02-15

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - RocksDB
Affects Version/s: N/A
Fix Version/s: 10.2

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None

Attachments: PNG File screenshot-1.png    
Issue Links:
PartOf
is part of MDEV-15603 Gap Lock support in MyRocks Stalled
Relates
relates to MDEV-21186 Benchmark range locking - nov-dec 2019 Closed

 Description    

create table ten(a int primary key);
 
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
 
 
create table one_k(a int primary key);
 
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
 
 
 
create table t1 (
 
  pk int primary key,
 
  a int
 
);
 
insert into t1 select a,a from one_k;


begin;
 
update t1 set a=a+10 where pk between 10 and 20;
 
select * from information_schema.rocksdb_locks;


+------------------+----------------+-----------------------------------------+------+                                                       
| COLUMN_FAMILY_ID | TRANSACTION_ID | KEY                                     | MODE |                                                       
+------------------+----------------+-----------------------------------------+------+                                                       
|                0 |              6 | 000000010680000015                      | X    |                                                       
|                0 |              6 | 000000010680000014                      | X    |                                                       
|                0 |              6 | 000000010680000014                      | X    |                                                       
|                0 |              6 | 000000010680000013                      | X    |                                                       
|                0 |              6 | 000000010680000013                      | X    |                                                       
|                0 |              6 | 000000010680000012                      | X    |                                                       
|                0 |              6 | 000000010680000012                      | X    |                                                       
|                0 |              6 | 000000010680000011                      | X    |                                                       
|                0 |              6 | 000000010680000011                      | X    |                                                       
|                0 |              6 | 000000010680000010                      | X    |                                                       
|                0 |              6 | 000000010680000010                      | X    |                                                       
|                0 |              6 | 00000001068000000f                      | X    |                                                       
|                0 |              6 | 00000001068000000f                      | X    |                                                       
|                0 |              6 | 00000001068000000e                      | X    |                                                       
|                0 |              6 | 00000001068000000e                      | X    |                                                       
|                0 |              6 | 00000001068000000d                      | X    |
 
|                0 |              6 | 00000001068000000d                      | X    |
 
|                0 |              6 | 00000001068000000c                      | X    |
 
|                0 |              6 | 00000001068000000c                      | X    |
 
|                0 |              6 | 00000001068000000b                      | X    |
 
|                0 |              6 | 00000001068000000b                      | X    |
 
|                0 |              6 | 00000001068000000a                      | X    |
 
|                0 |              6 | 00000001068000000a                      | X    |
 
|                0 |              6 | 00000001068000000a - 010000010680000014 | X    |
 
+------------------+----------------+-----------------------------------------+------+

The output shows overlapping ranges because we're in STO-mode.

 Comments   
Comment by Sergei Petrunia [ 2019-12-13 ]

The statement

update t1 set a=a+10 where pk between 10 and 20;

It is executed as scan on PK in [10..20].. and for each found row, MySQL calls GetForUpdate(). GetForUpdate acquires a point lock on the row it is reading.

Comment by Sergei Petrunia [ 2019-12-13 ]

But it doesn't stop there.

TransactionBaseImpl::Put will make this call:

 Status s = TryLock(column_family, key, false /* read_only */,
 
                true /* exclusive */, do_validate, assume_tracked);

With point locking, PessimisticTransaction::TryLock will reach this:

  // Lock this key if this transactions hasn't already locked it or we require
 
  // an upgrade.
 
  if (!previously_locked || lock_upgrade) {
 
    s = txn_db_impl_->TryLock(this, cfh_id, key_str, exclusive);
 
  }

where it it will not try to re-acquire the lock.

With Range Locking we'll get here instead:

  // If we are not doing key tracking, just get the lock and return (this
 
  // also assumes locks are "idempotent")
 
  if (!do_key_tracking_) {
 
    return txn_db_impl_->TryLock(this, cfh_id, key_str, exclusive);
 
  }

and WILL acquire the lock another time.

Comment by Sergei Petrunia [ 2019-12-13 ]

Re-running MDEV-21186 benchmark after fixing this:

Threads	orig	range-locking	range-locking-improved
 
1	21902.28	22048.75	22070.61
 
5	98446.24	84649.32	90926.11
 
10	164867.06	128401.14	141532.38
 
20	188024.51	113273.4	151987.21
 
40	205406.05	106224.2	142495.34
 
60	213448.2	102567.94	138292.55
 
80	214158.72	99551.03	135784.59
 
100	215291.05	97012.06	134099.73

Comment by Sergei Petrunia [ 2019-12-13 ]

This is a 30-40% speedup for high-concurrency workloads:

Threads	range-locking	range-locking-improved	speedup
 
1	22048.75	22070.61	1.001
 
5	84649.32	90926.11	1.074
 
10	128401.14	141532.38	1.102
 
20	113273.4	151987.21	1.342
 
40	106224.2	142495.34	1.341
 
60	102567.94	138292.55	1.348
 
80	99551.03	135784.59	1.364
 
100	97012.06	134099.73	1.382

Generated at Thu Feb 08 09:06:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.