Is there any way to solve this without locking the entire table ? (Want to try avoid table level locks) Why ? (The transaction aquires lock, right ?) If I execute the same query but without transaction block around it will work without error with many concurrent calls. SQLSTATE: Serialization failure: 1213 Deadlock found when trying to get lock try restarting transaction (SQL: update user_chats set updated_at = 10:07:13 where id = 1) If two requests are being done at the same time results in the following error pattern: Manual control over connection's transaction level using Connection::beginTransaction(), Connection::commit() and Connection::rollBack() would be discouraged in favour of Connection::newTransaction(), though still possible and remain the same.I provide below the raw MySQL query and also the code in which I do that programatically. In most cases Connection::transaction() would still be a preferred choice, but when more control is needed you would use Connection::newTransaction(). should throw an exception as this can not be committed $outerTransaction-> commit() will not execute any queries as transaction level is already 0 at this point and // $innerTransaction knows it should rollback to level 2 $innerTransaction-> rollBack() $ this-> assertInstanceOf( PDOException::class, $caughtException) $ this-> assertNotNull( $caughtException) Cause a deadlock on this connection User:: whereKey( $secondUserKey) Savepoint was created and $innerTransaction knows it's level is 2 $caughtException = null Transaction has begun and $outerTransaction knows it's level is 1 // Create a savepoint $innerTransaction = DB:: newTransaction() $outerTransaction = DB:: newTransaction() To battle this, we could introduce a Transaction class, which is aware of it's own level and hence can commit only if current connection's level is same or greater, otherwise throw an exception: However, if deadlock does happen, this line will rollback the transaction, not the savepoint, as transaction level has already been set to 0 by the moment it tries to rollback.Īnd that's not what most developers would expect to happen. Normally, you would expect this line to rollback savepoint trans2 - that's exactly what would happen if no deadlocks were occurred. Since we would now be decrementing transaction level under the hood regardless of whether exception is caught by developer or not, caught deadlock exceptions would cause unexpected flow: This will fix the problem, but that's not the whole story. Return $ this-> tryAgainIfCausedByLostConnection( Protected function handleQueryException( QueryException $e, $query, $bindings, Closure $callback) It will throw PDOException : SQLSTATE: Syntax error or access violation: 1305 SAVEPOINT trans2 does not exist when it obviously shouldn't have.
0 Comments
Leave a Reply. |