There is a need to check whether an entry exists with certain data in the table.

Then in php, depending on the result, write data to another table, or to the same one.

Thus I do the following:

  1. In the php-script created a connection to the database.

  2. Trash the table

    LOCK TABLES `table1` WRITE, `table2` WRITE

  3. I do sampling and all necessary actions.

  4. Do unlock

     UNLOCK TABLES

  5. I close connection with a DB.

How can I check the performance of the lock?

I had two options:

  1. Remove unlock from the php script and close the connection. Run the script twice in a row. I thought that the second time the sample did not work because of the first lock, but for some reason it worked. I can assume that due to the fact that once the script first lok the tables, then, starting the second time the script, I cancel the previous lock (because the repeated lock removes all previous locks), but, on the other hand, At the beginning of the script, a new connection to the database is created, and because of the new lock, the unlock should not occur.

  2. Remove unlock from php script and close connection. Run the script. Query the table in phphMyAdmin. Here, too, everything went well, i.e. The request was executed despite blocking. Perhaps sql so smart and he unlocks the table?

Where is the guarantee that the lock / unlock still works? How to check it? Perhaps the type of table affects the work of the lock, or something else?

  • And the script, where do you block it, has already worked before the restart? or works further? and why so difficult? There are transactions, he does the lock itself. If innodb, then not at the table level, but at the row level, which would be much better - BOPOH
  • the script by this time is already working, but it seemed to me that this should not affect, because I do not unlock and do not close the connection, except that the connection is, if it closes itself, after the execution of the script. I did not read much about transactions, in theory I knew about locks, so I implemented them. Thank you, read about them. In other scripts I used begin-commit, but here this option is not very suitable, because depending on the results of the first query, a stack of others is performed. - user3742070
  • "the script has completed its work" is almost always equal to "the connection to the database is closed", and once the connection is closed, other connections can work further. You can "check" - in your script after LOCK TABLES, you pause for 20 seconds and during this time you start the second script - it will wait until the first script runs. And it is not entirely clear what exactly btgin / commit does not fit. Nevertheless, requests can be executed in one transaction, which means that requests will have access to the same data - BOPOH
  • Many thanks for the help, now I understood what the problem was in my experiences :) As for the transaction, I probably misunderstood something, I will study this point in more detail. Thanks again! - user3742070
  • @BOPOH Please post your comments as an answer. - Nicolas Chabanovsky ♦

0