1

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?

7
  • what does select version(); show? I don't see anything in the doc about that start transaction syntax. Commented Jul 1, 2024 at 16:15
  • seems like keeping updateable info like LastLogin in a separate table (with its own FKIDPeron) would fix this Commented Jul 1, 2024 at 16:44
  • @ysth: 10.11.6-MariaDB-0+deb12u1 . In MariaDB the opening has to be split into START TRANSACTION and SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE, but I did not want to duplicate the whole bunch for this mere syntactical difference. Commented Jul 1, 2024 at 16:49
  • yeah, I don't see anything better to do than have separate PersonID and PersonData tables. you could make a Person view joining them to limit the number of queries you need to change, but cleaner just to change everything Commented Jul 2, 2024 at 15:31
  • oh, just saw the update; not sure what additional problems you are thinking of. if nothing changes Person except inserts and deletes, I don't see anything to worry about. removing the foreign key constraint from Log would work too. imo a table called Log shouldn't be deleted from except by age anyway, but a trigger could do it. Commented Jul 2, 2024 at 16:14

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.