Jan Lindström <jan.lindstrom@skysql.com> writes:

1) In innodb deadlock detection it is not certain that there is actually a deadlock, deadlock detection is done when a lock request is issued. At that point yes, we can see from lock manager that there is a deadlock, however if waiting transaction is rolled back e.g. by user request concurrently, the deadlock detected is not valid anymore.

Hm, interesting. It was my impression that the deadlock detection algorithm only considers transactions that are already waiting for a row lock. That is, if we detect a simple deadlock between two transactions T1 and T2, then it is because T2 is waiting for a row lock held by T1 while executing some user query, and T1 is executing another user query that requests another row lock held by T2. So since both T1 and T2 are already executing user queries, and will not complete until the deadlock is resolved, how could the user roll either of them back concurrently?

2) It is fine to roll back all transactions participating to deadlock, this is correct. Note that there are some preferences which transaction is rolled back on normal InnoDB server and this could be different when we have Galera

3) How this new needs to wait work with Galera where we have so called brute force transactions that may not wait anything ?

Note that this is only for resolving deadlocks between replicated transactions from a single master. If T1 and T2 are both replicated from the same master, we might decide to kill T2 to let T1 commit first. If I understand correctly, "brute force" transactions are only for transactions that are replicated from other Galera nodes, and which _must_ be committed in a specific order to preserve correctness. Thus, a brute-force transaction would not be affected by a deadlock kill from parallel replication. Galera code might cause a parallel replication transaction to be rolled back and retried, but this should be fine. Note also that in general for MySQL/MariaDB style replication, it is assumed (and it is the user's responsibility to ensure it) that replicated transactions will not conflict with anything done on the local node. This might also help to provide that Galera and parallel replication will not conflict. If Galera had been written with a proper general high-level API that could work with different storage-engines, then we could probably have used the "brute force transaction" mechanism to solve this problem in parallel replication also. But when I checked it, it seemed that it was hardcoded inside InnoDB with lots of Galera-specific code and data structures. If work were to start to fix this lack of proper API in Galera, then perhaps it would be interesting to work together to design a general API for enforcing a specific commit order... - Kristian.