[moe]

InnoDB isn't too hot on transactions either.

Google for "InnoDB deadlock".

[mootothemax]

I've googled, but am seeing the same kind of results that I see when I try "oracle deadlock", "postgresql deadlock" and "db2 deadlock."

Still being relatively new to InnoDB, what particular deadlock dangers make it more troublesome than other DBMS engines?

[pkteison]

There are three possible ways that I know of to handle concurrency issues on shared data: Use some sort of a journaling approach (google keywords 'snapshots' or MVCC), or use locks, or ignore the problem.

If it ignores the problem, it's not a database.

Locks suck for volume. Locks cause much more deadlock than other options. Locks are fast in the simple case. Locks are easier to program and take less resources.

InnoDB uses locks.

SQL Server also defaults to locks. People often specify 'ignore the problem' mode (nolock/read uncommitted). There is a new journaling approach available, but it was only introduced in 2005 and I don't think many people are using it yet. Which is a shame, it's a great feature.

Oracle and Postgres both do a journaling approach. They will have less deadlock problems because readers and writers don't need to block each other. With InnoDB or default sql server, read locks block writes, which sucks. See http://dev.mysql.com/doc/refman/5.0/en/innodb-lock-modes.htm...

[mbirk]

By default, InnoDB doesn't acquire read locks when doing queries. It "runs queries as nonlocking consistent reads by default, in the style of Oracle." See http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-mo...

[pkteison]

Wow, looks like I got that completely wrong w.r.t. innodb specifically. Wish I could still edit my first post. :( Thank you.

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-rea...