Can MySQL UPDATE locks starve due to continuous SHARE locks?-CodePudding

I have 2 different transactions where one is using read locks (FOR SHARE) for SELECT statements and the other uses write locks (FOR UPDATE).

Let's say they are trying to acquire the lock on the same row. Here's the scenario I'm trying to understand what's happening.

Let's say I have continuous stream of requests using the read locks and occasionally I need to acquire the write lock.

Are these locks using FIFO strategy to avoid starvation or some other strategy such as read locks would be acquired as long as it can acquire the lock and write lock would wait all the reads to drain (even the new ones in this case).

I'm suspecting 2nd might be happening but I'm not 100% sure.

I'm investigating an issue and couldn't find a good documentation about this.

CodePudding user response：

If you lack documentation, you can try an experiment:

Window 1:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tablename for share;
 --------------------- 
| ldt                 |
 --------------------- 
| 1969-12-31 16:00:00 |
 --------------------- 
1 row in set (0.00 sec)

Window 2:

mysql> update tablename set ldt=now();
(hangs, waiting for lock)

Window 3:

mysql> select * from tablename for share;
(hangs, also waiting for lock)

This indicates that the X-lock request is blocking subsequent S-lock requests.

50 seconds passes, and then:

Window 2:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

And then immediately:

Window 3:

mysql> select * from tablename for share;
 --------------------- 
| ldt                 |
 --------------------- 
| 1969-12-31 16:00:00 |
 --------------------- 
1 row in set (41.14 sec)

The select in window 3 was blocked while waiting for the update in window 2. When the update timed out, then the select in window 3 was able to proceed.