Does a unique index need to be locked pessimistically during the execution of DML statements in a pessimistic transaction?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: DML 语句执行悲观事务过程中,唯一索引是否需要被加悲观锁

| username: ylldty

We conducted tests to verify the behavior of the SELECT FOR UPDATE statement:

Result Rows LOCK Count RowID Lock Unique Key Lock SQL
Primary Key Search 1 1 1 0 SELECT * FROM T WHERE ID=1
Unique Index Search 1 2 1 1 SELECT * FROM T WHERE UNIQUE_INDEX=1
Normal Index Search 1 1 1 0 SELECT * FROM T WHERE INDEX=1
No Index Search 1 1 1 0 SELECT * FROM T WHERE COLUMN=1
No Data Hit 0 0 0 0 SELECT * FROM T WHERE C1=0 AND C1=1
Multiple Data Hits 2 2 2 0 SELECT * FROM T
Multiple Data Hits + Unique Index Search 2 4 2 2 SELECT * FROM T WHERE UNIQUE_INDEX IN (1,2)

We observed a peculiar phenomenon: if the WHERE clause of the SQL statement hits a unique index, TIDB will also lock the unique index. In other cases, the unique index is not locked.

Is it necessary for the SELECT FOR UPDATE statement to lock the unique index?

Personally, I don’t think it is necessary because the RowID is already locked, so the locked row data cannot be updated.

Can other row data update the unique key? No, they cannot. For example:

CREATE TABLE `MANAGERS_UNIQUE` (
  `MANAGER_ID` int(11) NOT NULL, `FIRST_NAME` varchar(45) NOT NULL,`LAST_NAME` varchar(45) NOT NULL,`LEVEL` int(11) DEFAULT NULL,
  PRIMARY KEY (`MANAGER_ID`) /*T![clustered_index] CLUSTERED */,
  UNIQUE KEY `FIRST` (`FIRST_NAME`),
  KEY `level` (`LEVEL`)
)

+------------+------------+-----------+-------+
| MANAGER_ID | FIRST_NAME | LAST_NAME | LEVEL |
+------------+------------+-----------+-------+
| 14273      | Brad7      | Craven7   |     7 |
| 14274      | Brad8      | Craven8   |     8 |
+------------+------------+-----------+-------+

Transaction 1:
BEGIN PESSIMISTIC;
mysql> select * from MANAGERS_UNIQUE where MANAGER_ID=14273;
+------------+------------+-----------+-------+
| MANAGER_ID | FIRST_NAME | LAST_NAME | LEVEL |
+------------+------------+-----------+-------+
| 14273      | Brad7      | Craven7   |     7 |
+------------+------------+-----------+-------+

Transaction 2:
BEGIN PESSIMISTIC;
update MANAGERS_UNIQUE SET FIRST_NAME ="Brad7" WHERE MANAGER_ID = 14274;

In this case, Transaction 2 will first check the uniqueness of the unique index FIRST_NAME. Upon finding that there is already a unique index “Brad7”, it will return an error directly. Therefore, it seems that locking the unique key is not necessary to achieve this functionality. (In fact, the should_not_exist parameter of the acquire_pessimistic_lock interface in TIKV serves this purpose.)

Are there other reasons or scenarios where locking the unique index is necessary?

Besides the SELECT FOR UPDATE statement, the DELETE statement also places a pessimistic lock on the unique index, and the UPDATE statement places a pessimistic lock on the old unique index. For more details, see: 专栏 - TIKV 分布式事务--加锁的 KEY 是什么 | TiDB 社区

What are the core reasons for these locking operations? In pessimistic lock scenarios, if only the RowID is locked and the unique index is not locked, can it reduce some system pressure?

| username: WinterLiu | Original post link

You can consider using in-memory pessimistic locking.

| username: dba远航 | Original post link

In a high concurrency environment, it is necessary to use pessimistic locking.

| username: ylldty | Original post link

@dbaYuanhang @WinterLiu
The issue here is that during the use of pessimistic locking, TiDB’s implementation not only locks the rowID but also locks the unique index. What is the core reason for doing this? Besides inserting new unique index values and updating new unique indexes, it seems unnecessary to lock the unique index; locking the rowID alone should suffice to achieve pessimistic locking.

| username: redgame | Original post link

Personal understanding is still to ensure the correctness of the database.

| username: ylldty | Original post link

Adding more locks definitely improves correctness, but the corresponding system pressure also increases. Moreover, it’s hard to think of any scenarios where adding locks is necessary to ensure correctness.

| username: zhaokede | Original post link

It might be for an extra layer of security.