The following sequences of SQL statements are executed on two different connections to the same database.
1: START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1: UPDATE Person SET LastLogin=NOW() WHERE IDPerson=1
[...paused...]
2: START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2: INSERT INTO Log (FKIDPerson, Hash) VALUES (1, '95baa69a26a95db2');
2: COMMIT
[...continued...]
1: COMMIT
Column Log.FKIDPerson references the primary key Person.IDPerson (ON UPDATE NO ACTION ON DELETE CASCADE). This runs in PostgreSQL without trouble. I am trying to port it to MariaDB for a customer (only difference in this minimum example is a slight syntax change in opening the transaction), but the INSERT statement of the second connection runs into a lock.
I need exactly this sequence of statements and - for reasons beyond the scope of this small example - I need them nested. How can I instruct MariaDB that this is not a problem (changing the isolation level all the way down to READ UNCOMMITED does not help)?
Update:
While moving LastLogin into a separate table does fix the current problem it cures the symptom not the cause; Log must be writable in separate transactions; I might end up in future versions with moving many or all attributes of Person to a separate table.
How about breaking up the referential integrity here (only in the MySQL version) and replacing it with a trigger on Person deleting obsolete Log records? It feels horrible to me, but after all the only real risk are some stale records which - in case - could easily be found and cleaned. Any opinions?
select version();show? I don't see anything in the doc about that start transaction syntax.10.11.6-MariaDB-0+deb12u1. In MariaDB the opening has to be split intoSTART TRANSACTIONandSET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE, but I did not want to duplicate the whole bunch for this mere syntactical difference.