MySQL Forums
Forum List  »  InnoDB

Abort transaction when detecting lock
Posted by: Aba Botond
Date: August 09, 2016 03:16AM

Hi,

Is there a way to make a transaction immediately abort when it detects that another transaction has active locks on rows it targets, rather than reading a consistent snapshot or waiting for said locks to be lifted (depending on the isolation level/type of locking read)?

My thinking is the following: Imagine you have N records to update and the update incurs some work. N is big so you do it in batches of m.

This would look like:
0. begin
1. read m rows
2. do some work
3. update m rows
4. commit

Now if you have X number of transactions doing this then as far as I can tell there are 3 scenarios:

1. No locks: All TRs will happily read a consistent snapshot and all will update. Finally the last one "wins", ie. it's result is the one that is kept. This also means that all records have been processed X times, which is bad.

2. Shared lock: All TRs will happily read a consistent snapshot, but the quickest TR wins, the rest will fail. This is acceptable.

3. Exclusive lock: the first TR will win reading and locking the rows of the first batch. The rest will wait till the first TR is finished (or time out). Then sequentially they all do the same. Last one "wins" again (if didn't time out) and records are processed potentially X times.

Now if there would be a way to immediately abort a TR when there's a lock in place then as far as i can tell all X TRs can each process a batch different from all other TRs simultaneously.

Options: ReplyQuote


Subject
Views
Written By
Posted
Abort transaction when detecting lock
1708
August 09, 2016 03:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.