IBM are close to releasing the next version of their DB2 database, codenamed ‘Viper’, which they plan will steal some of Oracle’s market share.
Unless they have changed things though, I am not a fan of DB2 from experience developing a web-based application over the last 2 years that has DB2 at the backend. The problem with DB2 is that it has what I consider to be an ‘old-school’ (or traditional?) locking strategy that just does not work well with today’s web-based applications with many (thousands) of concurrent users. Even when using an optimistic locking strategy controlled either by the application or an ORM tool like Hibernate, it’s still hard to avoid DB2’s pessimistic locking, and therfore unavoidable that at some point you will have users or even processes deadlock against each other.
I don’t claim to be a DBA, but I know from working with Oracle for many years that deadlocks were never an issue, and no, we were not using an uncommitted read isolation level, we were still using read committed.
What I have found out more recently is that ‘modern’ RDBMSs (I say that tongue in cheek and now expect to get flamed from the DB2 croud) such as Oracle, InterBase and it’s derivitives such as FireBird, and other open source database engines such as PostreSQL and MySQL (with the InnoDB engine), use a mechanism called Multiversion Concurrency Control. What this does is it achieves transaction isolation not by using pessimistic locks which we know is an approach that does not scale well for systems with many concurrent users or processes, but by using snapshots of the data taken for each transaction so that each transaction has a copy of the data being read or written, plus the use of a version value or timestamp on the current row, similar to how optimistic locking works if implemented in application code or using ORM tools like Hibernate. If a transaction attempts to update data that has already been updated by another process since the initial data was read, the version values will not match and the transaction will have to be repeated by the user.
Using this approach, readers do not block writers and writers do not block readers. This approach is far more applicable for usage in online systems today where we need to support large numbers of concurrent processes.
Does anyone know if DB2 Viper will be using Multiversion Concurrency?