Understanding lock_scanned_ranges hint of Cloud Spanner
Google Cloud Spanner is a modern and fully managed relational database that offers the highest availability and consistency at any scale. It is easy to adopt for database developers due to its familiarity with Ansi SQL language and now it offers PostgreSQL compatible interface as well.
Hints have always been one of the core features databases offer to influence various options like join order, method or index to use. Spanner supports many such hint, in this blog we will discuss on LOCK_SCANNED_RANGES hint. Acquiring exclusive locks is needed for transactions that upgrade locks from shared to exclusive within a transaction or have observed high write contention and abort of transaction scenarios.
lock_scanned_ranges hint helps us acquire exclusive locks on rows as part of select and releases once transaction is complete. lock_scanned_ranges have some important considerations to be aware of, before implementing it.
Let’s get into action and understand it better, will our sample. Table tblsample with some dummy records and using lock_scanned_rows on NAME filter.
CREATE TABLE tblsample (
ID INT64 NOT NULL,
NAME STRING(44) NOT NULL,
TYPE STRING(44) NOT NULL,
CREATED_DATE TIMESTAMP,
MODIFIED_DATE TIMESTAMP
)PRIMARY KEY(ID);INSERT INTO tblsample (ID,NAME, TYPE, CREATED_DATE)
SELECT *
FROM UNNEST ([(1,'APPLE', 'FRUIT', CURRENT_TIMESTAMP),
(2,'CAR', 'VEHICLE', CURRENT_TIMESTAMP),
(3,'MANGO', 'FRUIT', CURRENT_TIMESTAMP),
(4,'BANANA', 'FRUIT', CURRENT_TIMESTAMP),
(5,'CRICKET', 'SPORTS', CURRENT_TIMESTAMP),
(6,'FOOTBALL', 'SPORTS', CURRENT_TIMESTAMP),
(7,'POTATO', 'VEGETABLE', CURRENT_TIMESTAMP),
(8,'KIWI', 'FRUIT', CURRENT_TIMESTAMP)
]);
We will use spanner-cli for connecting to Spanner and execute our sample queries.
Using lock_scanned_ranges, we will implement exclusives locks on concerned rows and block exclusive access on rows for another session.
In session1, we acquire exclusive lock using sql with filter name=’apple’ that returned only one row. In session 2, we are trying to acquire locks but with different filter name=’mango’ that would also return only 1 rows but it is waiting to acquire lock.
To understand why session 2 was not able to acquire a lock, we can look into the execution plan and build our observations.
Highlighting some key observations based on execution plan and lock_scanned_ranges hint.
Access pattern on table tblsample is Full scan due to no index on NAME column
Rows scanned(8 row) is high as compared on rows returned(1 row)
lock_scanned_ranges as name implies, is not based on rows returned but based on rows scanned.
It’s important to design optimal access patterns to minimize rows scanned, primarily when using lock_scanned_ranges hints. Scanning rows not getting returns can create hot lock contention and impact scaling due to row locks on scanned rows.
Let’s go ahead and create a secondary index on the name column for our sample table.
spanner-session1>create index idx_test1 on tblsample(NAME);
Query OK, 0 rows affected (15.86 sec)
Now if we retry previous scenario, In session1, we acquire exclusive locks using sql with filter name=’apple’ that return only one row. In session 2, we are trying to acquire locks but with different filter name=’mango’ that will return only 1 rows and lock will be succeed.
Due to new index, rows scanned are reduced to only filtered rows and if another session needs to lock on other rows it will be successful.
Conclusion
Using lock_scanned_ranges for exclusive locks needs to be driven by a detailed understanding of the rows scanned and underlying access patterns within the execution plan. It should be used on Filters having index access pattern to minimize rows scanned.
