If you are using InnoDB or any row-level transactional RDBMS, then it is possible that any write transaction can cause a deadlock, even in perfectly normal situations. Larger tables, larger writes, and long transaction blocks will often increase the likelihood of deadlocks occurring. In your situation, it’s probably a combination of these. The only way … Read more
I would interpret the message as a deadlock on some combination of Lock resources or Communication Buffer resources. “Lock resources” are ordinary object locks, and “Communication Buffer resources” are exchangeEvents used for combining results of parallel queries. These are described further in https://blogs.msdn.microsoft.com/bartd/2008/09/24/todays-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks/ where the relevant paragraph is: An “exchangeEvent” resource indicates the presence of … Read more
As far as I know, there’s no way to accomplish this directly through the UPDATE statement; the only way to guarantee lock order is to explicitly acquire locks with a SELECT … ORDER BY ID FOR UPDATE, e.g.: UPDATE Balances SET Balance = 0 WHERE ID IN ( SELECT ID FROM Balances WHERE ID IN … Read more
In PostgreSQL the rows will be locked as they are updated — in fact, the way this actually works is that each tuple (version of a row) has a system field called xmin to indicate which transaction made that tuple current (by insert or update) and a system field called xmax to indicate which transaction … Read more
The problem you describe is not handled by the database, and from my experience is not entirely handled by Hibernate either. You have to take explicit steps to avoid it being a problem. Hibernate does some of the work for you. As per the previous answer, Hibernate ensures that within an isolated flush the inserts, … Read more
How about something like this: public T DeadlockRetryHelper<T>(Func<T> repositoryMethod, int maxRetries) { int retryCount = 0; while (retryCount < maxRetries) { try { return repositoryMethod(); } catch (SqlException e) // This example is for SQL Server, change the exception type/logic if you’re using another DBMS { if (e.Number == 1205) // SQL Server error code … Read more
Wait-Die scheme It is a non-preemptive technique for deadlock prevention. When transaction Tn requests a data item currently held by Tk, Tn is allowed to wait only if it has a timestamp smaller than that of Tk (That is Tn is older than Tk), otherwise Tn is killed (“die”). In this scheme, if a transaction … Read more