Is Oracle DB immune to the InnoDB deadlocks found in MySQL?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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)