[k7d]

The article didn't mention the main advantage of storing queues in DB - transactions. Say you need to update other records in DB while processing a job with 100% consistency. If it's all in the same DB you can update both job as well as data in a single transaction.

[Devilboy]

MySQL is not too hot on Transactions

[mootothemax]

MySQL != MyISAM. Check out InnoDB :)

[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...