Mysql transaction deadlock?

InnoDB has a number of locking modes We're seeing a row-level lock here, but it's failing.

InnoDB has a number of locking modes. We're seeing a row-level lock here, but it's failing. Transaction #1 is waiting for an exclusive lock: RECORD LOCKS ... page no 232059 ... lock_mode X locks rec but not gap waiting Transaction #2 already has a shared lock on the same row: RECORD LOCKS ... page no 232059 ... lock mode S locks rec but not gap While #2 has the shared lock, it also wants an exclusive lock: RECORD LOCKS ... page no 232059 ... lock_mode X locks rec but not gap waiting Both #1 and #2 need the same exclusive lock, but neither lock can be granted until #2 releases the shared lock, but #2 can't release the shared lock until it's upgraded to exclusive, but that will never happen.

This causes a deadlock, and InnoDB will kill one of the two transactions and perform a rollback. A similar situation is described on the documentation page I linked. You have two ways to handle this.

The first and worst way would be to perform table locking instead of using a transaction. This will prevent multiple writers (or optionally readers) from modifying the table at once. This will probably impact performance, and opens up even worse deadlock situations.

Further, you can't mix table locks and transactions. The second and better way is to modify your application to deal with deadlocks elegantly. This means making sure that the rollback happened and either trying again or presenting an appropriate error to the user.

Because transaction 2 holds a S(hared) lock on fixtrades id=10, acquired when it read it, so transaction 1's attempt to get an eXclusive lock to modify the record cannot succeed. I think you might be able to solve this by changing the insert to this: insert into fixtradeshistory (select null, fixtrades. * from fixtrades where id=10 FOR UPDATE).

You may also be able to solve the problem by doing the UPDATEs before the INSERTs. This causes transaction 1 to get an exclusive lock the first time around, rather that than the shared lock. Transaction 2 will still be stuck waiting for transaction 1 to complete.

But transaction 1 will be able to complete because it will already have the exclusive lock it needs. See vimeo.com/12941188 for a more complete explanation.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.