Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL Trigger Causing Lock

I have a trigger on a mysql table that inserts into another table (see trigger below).

It would appear this trigger is causing a lock on the other table that is then preventing other inserts into the same table that happen at the same time. If I delete the trigger the other inserts work as expected.

I need to find a way to ensure both inserts happen. Is there another way write my trigger that would help?

Some more background: The table this trigger is setup on is part of a Joomla website extension that is a form that inserts fields into this extension’s table – the trigger takes some of the new.fields and inserts them into a Joomla core table for custom fields (ch54b_fields_values). The extension itself also writes into the Joomla table for custom fields as part of the initial insert.

Any help is appreciated. I am no expert in this, so don’t want to play around with something and cause other issues.

IF (new.catid = '131')
THEN
INSERT INTO ch54b_fields_values (field_id, item_id, value) VALUES ('2', new.id, new.created_by);
END IF

I have tried executing the trigger both before and after insert and the result is the same.

Tags:

2

Answers


  1. 0

    It seems like you’re experiencing a table lock issue due to the trigger. This is a common issue when a trigger is used to modify another table, as it can lead to concurrent access problems. Here are a few suggestions that might help:

    • Use SELECT … FOR UPDATE Pattern: This pattern is used to read the latest available data, setting exclusive locks on each row it reads1. This could help prevent two triggers from running simultaneously and updating the same record.

    • Change the Storage Engine: If the table’s storage engine is MyISAM, it could be causing the lock. MyISAM uses table-level locking, which can lock the entire table during an operation. Changing the storage engine to InnoDB, which uses row-level locking, might resolve the issue2.

    • Use a Staging Table: An alternative solution is to use a “staging” table that holds the pending changes. You can then create an event in MySQL to read from this staging table and execute the desired changes3. This can help avoid the circular reference that would be caused by the trigger.

    • Rewrite the Trigger: You could consider rewriting the trigger to handle concurrency better. For example, you could use a BEFORE INSERT trigger to set the values you want to insert into ch54b_fields_values45.

    Here’s an example of how you might rewrite your trigger using the BEFORE INSERT method:

    CREATE TRIGGER before_insert_on_table
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
  IF NEW.catid = '131' THEN
    SET @field_id = '2', @item_id = NEW.id, @value = NEW.created_by;
  END IF;
END;

    And then, you can insert the values into ch54b_fields_values after the insert operation on your table is completed.

    Please note that these are just suggestions, and you should test them thoroughly to ensure they work as expected in your specific use case.

    Login or Signup to reply.
  2. 0

    You need to ask yourself:

    1. How do I know that the problem was caused by a lock?

    Your answer will likely be that all failed inserts happen when the insert is running. But:

    1. Is my error happening while the trigger is running because of a table lock or it just happens at the same time?

    Now that’s a crucial question you need to ask yourself. So, check your MySQL logs and see why the insert failed and what the insert was. If the error message clarifies your exact problem, then you can solve it. If not, then read on. Create a copy of your database and import somewhere where you can freely experiment. Your next questions to yourself:

    1. Is my insert succeeding on my copy without triggering my trigger? (you can even remove this trigger from your copy)

    If the insert succeeds, then it was likely a table lock issue.

    If it was a table lock issue, consider switching to InnoDB if you did not already do so. Also, your inserts that may be bound to fail could be put into a transaction and retry on failure. Of, if this trigger is causing too much headache, you might consider removing it and to reorganize your insertions and updates in your app code rather than triggers. You could create a queue with the changes to be done on the table where you had your conflicts.

    If the insert does not succeed even later, then it was not failing due to locks, but due to data-integrity constraints or the like. You might want to reinsert the same record with the same unique value (like the same id or the same unique foreign key) or you might intend to insert a record with a foreign key referring a record that no longer exists.

    Therefore, read logs, debug and experiment. Each experiment brings you one step closer to the solution, independently of the result of the experiment.

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

Back To Top
Search