D
D
Denis Ogurtsov2015-11-23 14:19:55
MySQL
Denis Ogurtsov, 2015-11-23 14:19:55

How to update a record that refers to another database (which is in a transaction at the moment)?

There are two databases: DB1, DB2.
And these databases are in the table: DB1.groups[id, name] and DB2.keywords[id, name, group_id].
DB1.groups.id is a foreign key for DB2.keywords.group_id
The task is: get data -> process -> update DB1.groups -> update DB2.keywords.
Implementation pseudocode:

//begin transaction for DB1;
begin_transaction('db1');
try {
    $a = get_data()
    $a = handle1($a);
    insert_to_db1($a);
    insert_to_db2($a); // вот тут и происходит завис запроса и ошибкой  “Lock wait timeout exceeded; try restarting transaction” 
    commit_transaction('db1');
} catche(\Exception $e) {
    rollback_transaction('db1');
}

The reason is that I have locked the DB1 database with a transaction and when updating a table in the second DB2 database, it hangs because I have a foreign key in it to the locked DB1 database (the request is waiting for DB1 to be unlocked). If you remove the foreign key, then everything is quickly completed.
How to proceed? I understand that transactions are atomic for one database, but it's not enough for me to be atomic for one database.
I will not remove foreign keys. It's hard to use without a transaction.
Who will solve the problem.
From technologies: php mysql yii2
PS 1: Thank you Optimus Pyan , not the database and not the table are locked, but only the record that was upplayed. Sense just also that if the record in the first table changed - to change it in the second.
As a solution to write a trigger on the event: AFTER_UPDATE to the first table, but this works with different databases and the second database can have a different name ....

PS2: I can read, update a record in a table that has an external field that refers to a locked field - I can’t

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question