SKIP LOCKED problems

[Vlad Khorsun]

Hi All,

Firebird 5 have a new nice feature - ability to skip rows that is locked
(modified) currently by concurrent transaction.

This feature exists in two forms:
- SELECT ... WITH LOCK SKIP LOCKED
- UPDATE|DELETE ... SKIP LOCKED

Unfortunately, I see some problems with its implementation and want to discuss it here.

First, lets look at order of execution of SELECT ... WITH LOCK SKIP LOCKED:

1. read the record, find the visible record version according to the
current transaction isolation level

2. try to lock the record, using dummy update
if not successful, skip the record

This works in general, but not in READ COMMITTED NO RECORD VERSION mode (RC NRV):
if record is locked by concurrent transaction, it is not skipped and throw read conflict
error (after some wait, depending on transaction options).

I think this is not correct behavior - why wait for locked record and return
error, if user asks to SKIP LOCKED records ? The error is raised at the step (1)
when engine attempts to read the record. At this step there is no information
about SKIP LOCKED option and record can't be skipped.


Next, lets look how UPDATE|DELETE SKIP LOCKED is executed. Engine creates
implicit cursor for UPDATE and DELETE statements and actually runs something like

FOR SELECT ... AS CURSOR C
DO UPDATE|DELETE ... WHERE CURRENT OF C;


Current implementation of SKIP LOCKED turns it into:

FOR SELECT ... WITH LOCK SKIP LOCKED AS CURSOR C
DO UPDATE|DELETE ... WHERE CURRENT OF C;

The order of execution is as follows:

1. read the record, find the visible record version according to the
current transaction isolation level

2. try to lock the record, using dummy update
if not successful, skip the record

3. refetch record
(RPB_refetch was set in VIO_writelock at step 2)

4. run BEFORE triggers

5. refetch record
(RPB_undo_read was set in VIO_refetch_record at step 3)

6. update_in_place

7. run AFTER triggers

Because of step (2) record is updated two times, which adds performance penalty.
Also, steps 3 and 5 is optional and usually could be avoided.


I offer to make following changes in SKIP LOCK implementation:

- add early check for SKIP LOCKED condition - when record is about to be read (at step 1
above), not only after attempt to lock record

This will make SKIP LOCKED works in all transaction isolation levels, including RC NRV

- avoid unnecessary lock for UPDATE and DELETE statements

This will remove performance penalty of current implementation.

To not delay release of v5 I offer to apply fix #7700 now and return to this issue
shortly after release.

Regards,
Vlad

PS I used branch work/gh-7700-skip-locked with current master merged into it.

[Karol Bieniaszewski]

Or simply throw error for skip locked in RC NRV, and implement it later.

 

Regards,

Karol Bieniaszewski

--

You received this message because you are subscribed to the Google Groups "firebird-devel" group.

To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-devel/a45a24c7-f7a0-9197-29fe-ab10f93bc9e9%40gmail.com.

 

[Dmitry Yemanov]

All,

Weirdly, but I didn't get the original message, I only see Karol's reply
to Vlad.

> I offer to make following changes in SKIP LOCK implementation:
>
> - add early check for SKIP LOCKED condition - when record is about to be
> read (at step 1 above), not only after attempt to lock record
>
> This will make SKIP LOCKED works in all transaction isolation
> levels, including RC NRV
>
> - avoid unnecessary lock for UPDATE and DELETE statements
>
> This will remove performance penalty of current implementation.

Sounds reasonable.

> To not delay release of v5 I offer to apply fix #7700 now and return
> to this issue shortly after release.

I don't mind.


Dmitry