InnoDB transaction interleaving and atomicity

[SONAM JAIN]

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!