Re: Simple transactions cause deadlocks for each other by UPDATE queries locking the primary key

[David Raimosson]

Thanks for your reply.

"What else is in the transaction? (Or are you running with autocommit=1?)"
> There's nothing else in transaction 1, it's a single query and yes, autocommit is enabled. All queries included in transaction 2 are listed in the first post.

"Do you have a SELECT ... WHERE MemberID=229101 earlier in the transaction? If so, tack on FOR UPDATE. This _might_ turn the deadlock into a "lock wait"."
> As transaction 1 has no other queries in it there is no earlier SELECT query. Otherwise it would have been a good idea to use the FOR UPDATE clause.

"If you can't get rid of the deadlock, deal with it: Replay the transaction from the BEGIN."
> It might be the only way out. I wanted to avoid that.