5

It seems odd to me that MySQL would not handle this internally: "Deadlock found when trying to get lock; try restarting transaction"

Does Oracle DB work around this issue? After all, Oracle owns InnoDB.

3 Answers 3

12

No database can possibly work around deadlock errors in general-- in Oracle, a deadlock indicates a bug in the application, not in the database. The Oracle database will detect the deadlock condition (i.e. session A has a lock that session B is waiting on and session B has a lock that session A is waiting on) and terminate one of the blocked statements to resolve the problem. Your application ought to avoid creating a deadlock in the first place-- one option is to always generate locks in the same order in every session.

7
  • Nothing beats the straightforward answer of describing to principle of deadlock scenarios and the resolution of them. +1 !! Aug 9, 2011 at 17:29
  • The idea that the application ought to prevent them is new to me. MyISAM does not have this requirement. I have been using an auto retry for now, (it only happens 1-2% of the time) but I will take a look at the indexing and perhaps reduce or eliminate the occurrences of deadlocks. Aug 9, 2011 at 19:00
  • 3
    @GeorgeBailey - Of course, MyISAM does not have to worry about that because each INSERT, UPDATE, and DELETE performs a full table lock on a first-come, first-server basis. Aug 9, 2011 at 21:34
  • 3
    @Justin +1 for the phrase 'a deadlock indicates a bug in the application, not the database'. I will be using this phrase instead of the the lengthy explanations on what a deadlock is. This if for when I have to explain to support staff when the application throws "SQL" errors for deadlocks. Aug 10, 2011 at 5:21
  • 1
    @Richard - If there are transactions, that combine InnoDB and MyISAM, all tables involved take on the most pessimistic behavior. In such a case, that would be full table locking because of the presence of one or more MyISAM tables. Same goes with MEMORY tables. However, you are right in terms of InnoDB not doing table locks. Just a bunch of single row locks. If the clustered index get thrown into the mix, multiple locks against it will then rear its ugly head. Aug 12, 2011 at 14:44
6

Deadlock Detection and Resolution Issues has been around as long as RDBMSs have. Even though Oracle owns InnoDB, do not expect Oracle to fix InnoDB. Most applications are to blame for deadlocks, not so much the RDBMS. Regardless of Oracle, MySQL or any other RDBMS, a Deadlock Error can rear its ugly head.

Oracle acquired InnoDB October 7, 2005 when the partnership between InnoBase Oy and MySQL expired and MySQL was lax in renewing. IMHO, Oracle tried to stem the tide of MySQL by doing so. Sure, it has made commitments to improve InnoDB and MySQL. It has no choice now due to PR and possible antitrust issues. In light of this, we should not expect InnoDB to have improvements that would overshadow or become comparable to Oracle.

Getting away from business politics now, look at InnoDB on its own. It has the variable innodb_lock_wait_time. That option is there to provide for increasing or decreasing the length of time to permit a lock to succeed, and nothing more.

If deadlocks occur internally, I would normally look to the clustered index as the victim in InnoDB because non-clustered indexes drag clustered index pointers as well. Updating a row in InnoDB where indexed columns are updated, I expect a deadlock to occur due to the clustered index. Performing SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE could exacerbate the problem more.

How Oracle handles it internally (if they have handled it) would be one of among the long list of things that give it the edge over MySQL. I do not expect Oracle to provide that same edge to an Open Source/Freeware product unless it is Oracle XE.

1
  • Nice info about Innodb and Oracle's plans. I just hope Oracle does not sabotage MySQL in any way, and continue to keep it open source. Aug 10, 2011 at 5:24
5

Deadlocks are a fact of life on all databases, and Oracle is no exception. There is no magic that can be done in this situation - it is a fundamental consequence of concurrency (letting multiple users access the data at the same time without harming integrity):

create table t(id integer primary key);

--session 1
insert into t(id) values(1);

--session 2:
insert into t(id) values(2);    --completes immediately
insert into t(id) values(1);    --waits for session 1 to commit or rollback

--session 1
insert into t(id) values(2);

--session 1 gets: 
SQL Error: ORA-00060: deadlock detected while waiting for resource

What Oracle has had for a long time is an excellent implementation of MVCC - read about it in the very useful Concepts guide.

I'll just add that Oracle owning InnoDB is unlikely to make much difference to how InnoDB works deep down at the level of concurrency control - although as I understand it the way they both implement MVCC achieves basically the same thing (as long as you are not mixing tables using other storage engines in your MySQL queries - though you couldn't fault InnoDB in that case)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.