0

I have a table (table1) and I'm performing a REPLACE statement on it. I have triggers on this table on BEFORE INSERT, AFTER INSERT and BEFORE DELETE, and in these triggers I modify another table (table2). I don't want any other session to edit table2 while the REPLACE statement and the operations inside the triggers are running.

I know I can use LOCK TABLES on table1 and tables inside the triggers will also lock. But I'm doing the modification inside a stored procedure so, therefore, I cant use LOCK TABLES. (Based on this)

How can I make sure that table1 and table2 stay locked and no other sessions could access them until the REPLACE statement has finished without using LOCK TABLES?

I have also read this. But it is impossible to return a ResultSet from a trigger and also I'm already returning another ResultSet from my stored procedure and I don't want to return such a large ResultSet just because I want to lock the table.

UPDATE:

Here is an example of what's happening in a trigger:

  1. selects row1 from table2
  2. selects row2 from table2
  3. inserts a row in table2, based on the selected values
  4. updates row1 and inserts or updates some other rows from table2 based on the selected values
  5. Inserts another row to table2

Let's say I use START TRANSACTION and assume that both transactions are working with the same rows. Now transaction1 and transaction2 have finished the second step, meaning that they have read their needed values, then transaction1 goes into the next steps and locks transaction2 until transaction1 is completely finished, then transaction2 goes into step 3, but now the selected value from step 1 is not right because transaction1 has updated row1. Therefore, it's going to insert and update with wrong values.

How can I avoid this scenario?

(Also if this scenario is impossible to happen, what other scenarios could happen based on the steps that are happening in the trigger? Because I AM SURE that something wrong is happening when using this code with more than 1000 inserts at the same time from different transactions)

Soheil Rahsaz
  • 719
  • 7
  • 22
  • Why do you want to lock entirer tabkes and not just the rows affected by your statements? – Shadow Sep 19 '20 at 12:50
  • 1
    I'm guessing you are using the word "atomic" when you mean "without waiting for a lock." – Bill Karwin Sep 19 '20 at 20:53
  • Are you not using InnoDB? `LOCK TABLES` is passe. `REPLACE` is passe; see if you can switch to IODKU. – Rick James Sep 19 '20 at 23:37
  • @Shadow Because the modifications on `table2`, inserted rows and updates, are based on other rows, so in the middle of modifying `table2`, another session should not change any value or else session1's values will be corrupted. @BillKarwin my mistake, Yes I mean other sessions should wait for all modifications to take place. @RickJames IODKU is definitely better and I SHOULD use it, but it will not solve my problem! I still want modifications inside the trigger to lock the table and other sessions. Because there are multiple inserts, selects, and updates in each trigger. – Soheil Rahsaz Sep 20 '20 at 06:33
  • That's actually the point of a (serializable) transaction: prevent a 2nd transaction t2 to do something that would have a different outcome compared to running t1 completely before or after t2 (serialized = one ofter the other). You are currently trying to force t2 to run after t1 (e.g. just because t1 started at 12:00h, you prevent t2 to do something at 12:01h that would have been fine at 11:59h without changing the outcome of t1). You "just" need to use transactions properly (althought "just" isn't trivial), e.g. use proper isolation level, `for update`, ... (and be prepared to repeat t1). – Solarflare Sep 20 '20 at 09:45
  • @Solarflare, I explained why I couldn't use the `select ... for update` in my procedure. If There's a particular way to do this or I'm wrong in my description, please provide me a more detailed solution. I'd really appreciate it. – Soheil Rahsaz Sep 21 '20 at 07:32
  • Yes, you could do that, e.g. with `select min(col) into @dummy from table2 for update` for an unindexed(!!) column should lock the table without a resultset. But that wasn't my point. Lets say your procedure (transaction t1) until now only used row 1 for its calculations (with `for update`), but not row 2 at all. There is no harm if t2 modifies row 2 after t1 started - as it could have done it *before* t1 started. It cannot modify row 1. That is what transactions would do. You are trying to prevent a row 2 modification - which you may not need, which is what I tried to express with my comment. – Solarflare Sep 21 '20 at 08:35
  • @Solarflare, I updated the question with an example and a scenario. Would you be so kind to read it? Thanks – Soheil Rahsaz Sep 21 '20 at 11:10
  • Sorry, I am really not sure where the problem here is. Obviously, you would need to use "select ... for update" here (or serializable isolation level), so t2 will not be able to do step 1 without waiting for t1 to finish. You explained that you cannot use `for update` because you do not want to return more rows, but if you select anyway, that argument doesn't hold, or did I miss something? Again: all I wanted to say is: it looks like what you are trying to do/prevent here is done/can be done already by using transactions, as this is literally what transactions are supposed to do. – Solarflare Sep 21 '20 at 12:39

0 Answers0