Tag Info

New answers tagged

0

I guess it's a combination of MySQL not being very smart and your less-than-optimal application design. You have two concurrent transactions trying to access the same block: *** (1) TRANSACTION: TRANSACTION 570934008, ACTIVE 0 sec inserting ... *** (2) TRANSACTION: TRANSACTION 570934009, ACTIVE 0 sec inserting Your trigger makes them both attempting to ...


6

The deadlock diagram is: The particular issue here is that (4) IX is not compatible with (1) SIU, whereas it is compatible with the more usual IU lock seen in this situation. The SIU lock occurs because somewhere between selecting the row in question with the UPDLOCK hint (which takes IX at the object level, IU at the page level, and U at the row level), ...


4

There are doubtless many blogs that discuss this, but this post by Kendra Little (at BrentOzar.com) discussed the issues that concern you. See: http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/ Kendra discusses some of the problems that arise and how you can test for potential ...


3

Since your problem is a conflict between constant updates and being able to run reports without facing deadlocks, I would recommend you consider using READ_COMMITTED_SNAPSHOT isolation set ON. This also has overhead, but usually prevents most deadlocking situations. (Nothing is magic, of course.) You should read the details at: ...


8

Is there a way to prevent the deadlock while maintaining the same queries? The deadlock graph shows that this particular deadlock was a conversion deadlock associated with a bookmark lookup (an RID lookup in this case): As the question notes, the general deadlock risk arises because the queries may obtain incompatible locks on the same resources in ...


0

I have a similar issue that occurs occasionally and here is the approach I take. Add set deadlock priority low; to the select. This will cause this query to be the deadlock victim when a deadlock occurs. Setup retry logic within your application to automatically retry the select if it fails because of deadlock (or timeout), after waiting / sleeping for ...


0

There is a setting that was introduced in MySQL 5.5.30 : innodb_print_all_deadlocks When this option is enabled, information about all deadlocks in InnoDB user transactions is recorded in the mysqld error log. Otherwise, you see information about only the last deadlock, using the SHOW ENGINE INNODB STATUS command. An occasional InnoDB deadlock is not ...



Top 50 recent answers are included