MySQL Forums
Forum List  »  Newbie

InnoDB transaction interleaving and atomicity
Posted by: SONAM JAIN
Date: March 31, 2023 09:50PM

Consider a hypothetical logging system that groups the incoming logs into `LogIndex` periodically. At a time, there is only one `Active` index which can receive new logs.
There is a periodic job which transitions `LogIndex` from `Active` -> `Closed` state, if no new logs arrive within last 1hr.

The DB schema is as follows:
```
1. LogIndex (Id, State [Active|Closed])

2. Log (Id, LogIndexId, LogLine, LogTimestamp)
```

There could be a scenario where 2 DB transactions run in parallel:

**Transaction 1 : To insert a new log line**
```
Query1. Select Id from LogIndex where State='Active' LOCK IN SHARE MODE;
Query2. Insert into Log values(Id, LongIndex.Id, LogLine, LogTimestamp);
```

**Transaction 2 : Periodic checker to determine if the index needs to be closed**
```
Query1. Select Id from LogIndex where State='Active' LOCK IN SHARE MODE;
Query2. Select LogTimestamp from Log where LogIndexId='<someLogId>' order by LogTimestamp desc Limit 1;

<If the LogTimestamp is more than 1 hour old, then>

Query3. Update LogIndex set State='Closed' where Id='<someLogId>'
```

I have few questions starting with very basic ones:

**Q1:** When the `Select` is running with `LOCK IN SHARE MODE`, would the queries inside the transactions still execute in interleaved manner?

**Q2:** Assuming interleave execution happens:
If we want to avoid using exclusive locks like `Select...LOCK FOR UPDATE`, then how can we serialize these transactions to avoid the inconsistent state due to following sequence:

t1: `Transaction1 - Query1` -> Selects the LogIndex in `Active` state. There is only 1 LogIndex in `Active` state at a time.

t2: `Transaction2 - Query1` -> Selects the LogIndex in `Active` state

t3: `Transaction2 - Query2` -> Lists all the logs from the LogIndex

t4: `Transaction2 - Query3` -> Updates LogIndex as `Closed`

t5: `Transaction1 - Query2` -> Inserts Log record for LogIndex.Id just set as `Closed`


*Or another possible scenario:*

t1: `Transaction1 - Query1` -> Selects the LogIndex in `Active` state. There is only 1 LogIndex in `Active` state at a time.

t2: `Transaction2 - Query1` -> Selects the LogIndex in `Active` state

t3: `Transaction2 - Query2` -> Lists all the logs from the LogIndex where last log was more than 1 hour old

t4: `Transaction1 - Query1` -> Inserts Log record for LogIndex.Id which is in `Active` state

t5: `Transaction2 - Query3` -> Updates LogIndex as `Closed`, even though there is a new log inserted at t4.

DB in question is MySQL InnoDB with default isolation level set to REPEATABLE READ.
Thanks!

Options: ReplyQuote


Subject
Written By
Posted
InnoDB transaction interleaving and atomicity
March 31, 2023 09:50PM


Sorry, only registered users may post in this forum.

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.