Question 308621 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – How to update a mysql table in 2 databases at once using php

I have a laravel application A with a mysql database db1 and a table p1 and a diffrent php application B with an equivalent table p2 in its mysql database db2.
I want to find best, secure and complete way to update at least from application B both p1 in db1 and p2 in db2.
I have some columns only in p1, some columns only in p2 and some common columns but the rows have a one on one equivalence never one to many. It is the same entity from real world.

  1. Main question

I have thought of doing an edit form in application B with a transaction in which i update both tables with their respective columns and the new values but will this block the records from both database ? What do I have to worry about?

  1. Extra scenario

2.1. start transaction
2.2. I update a fk column in p1 in db1 and it works
2.3. By the time i finish the transaction someone deletes in application a the row in fk coresponding table f1.id=p1.fk
2.4. I make the update in database db2 where there is no fk column ;
2.5. I should commit or will it give me an error (as it should) that the first update cannot be performed ?

  1. Clarification 1

What if i also want to implement and edit form in application A will same solution apply? Should i update only in one application ?

  1. Clarification 2

What settings like isolation level should I use and how ? A code example would be very helpful.

I am opened to suggestions on how to do this task. Thank you.

start transaction

UPDATE db1.p1 pp1 inner join db2.p2 pp2 on pp1.iddb2=pp2.id
    SET 
       pp1.fk_id=23,
       pp1.commoncol='a',
       pp2.commoncol='a',
       pp2.extracol='b'
WHERE pp2.id=123;

commit;
Tags:

2

Answers


  1. 0

    Making changes to data in two separate databases at the same time while ensuring that the data stays consistent can be a tricky job.

    If you’re utilizing Laravel’s Eloquent, you can utilize the DB::transaction method to group your updates into a transaction.

    Here’s an example in Laravel:

    use IlluminateSupportFacadesDB;

DB::transaction(function () {
    // Your database updates for application B
    // Update in db1.p1
    // Update in db2.p2
});

    In your scenario, if you modify the foreign key in p1 first, and someone deletes the corresponding row in db1 before updating p2 in db2, you may encounter difficulties in maintaining the association between them, referred to as referential integrity issues.

    Login or Signup to reply.
  2. 0

    I don’t think there is a problem if the two dbs are in the same MySQL instance, but…

    • A transaction is "all" or "none".

    • A transaction must be run from a single connection. And both databases must be in the same MySQL instance. (Let’s not talk about "two-phase commit".) If, by "database", you mean "server", then make that abundantly clear in the Question.

    • Other connections are prevented from modifying anything involved in the transaction.

    • You should not need to watch what other connections are doing, but…

    • You do need to check for errors. One connection can interfere with another, and the system will abort one transaction. Generally, start the killed transaction over.

    • Use the default transaction isolation.

    • Whether a column is a Foreign Key is irrelevant to this discussion.

    • If, inside a transaction, you need to SELECT some data before deciding what to do, use SELECT ... FOR UPDATE. (I don’t know how Laravel exposes this option.)

    (I agree with the Comments that having two databases and syncing them may be ‘over-thinking’ the task.)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search