4/9/2023 0 Comments Sql deadlock errorHowever, this seems ugly and I’d have to litter a lot of my code with mechanisms for restarting transactions in places that should not (I think) have to deal with such issues. saveWithTransaction() ) and in the original method (save() ) use a for loop to run the transaction method and catch any DeadlockExceptions and retry if necessary. What would be the best method to do this? The only solution I can see is to move the code from each method that uses transaction to another method (e.g. some other more db operations involving INSERT, UPDATE and DELETEĮach of the above operations can potentially end up in deadlock and I’d have to start from scratch. DELETE old entries in some other table. UPDATE a table which holds products indexed for search engine INSERT a few rows to product_accessory table INSERT a few rows to product_category table I have to start the whole transaction again. When I start transaction, insert a new row and the next query causes deadlock then the new row can be lost and I can’t simply retry the second query and continue. This was easy to implement because I have a central Db class but it does only retry a single query not the whole transaction. One thing I have done so far is I have made the query() method in my Db class detect deadlock error #1213 and retry the query up to 3 times. So here’s my question - how to restart transactions elegantly in PHP? So I am now at this stage - deadlocks happen rarely but still they do happen and I want to be prepared for that. Moreover, the MySQL manual says that deadlocks are a natural thing in complex databases and the client simply needs to restart the transaction and if this doesn’t occur often then this doesn’t pose the problem. that are executed within various transactions and coordinating all those queries so that they do not cause deadlocks in any used scenario seems like an almost impossible task. However, as the application grows more and more complex it’s difficult to guarantee deadlock-free transactions since there are many different functions, methods, modules, stored procedures, etc. I’ve searched information on deadlocks and I know there are certain steps that can be taken to minimize their occurrence like updating in a certain order, etc. But sometimes a query in a transaction ends up with this error: Deadlock found when trying to get lock try restarting transaction #1213 I like to enclose portions of SQL updates, inserts and deletes in a transaction to ensure the operation is atomic and I don’t end up with partially executed action if something goes wrong. More and more often I come across a problem of deadlocks in mysql.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |