Thu, 19 Nov 2009
Database Woes
Using the SQL database for keeping one's data gives an excellent environment, maintaining the data integrity, providing the transactional behaviour, providing the remote access to the data, and so on. Even the locking properties can be something which one can get used to. That is, in the ideal world.
However, our world is not ideal. The huge problem of SQL databases is their implementation. For example, after rewriting the IS MU mailserver back-end to do a parallel delivery, it started to generate big load spikes on the Oracle DB server. The problem turned out to be the cache of SQL queries: when several processes tried to do exactly the same query in parallel, the DB server locked up on the access to the SQL cache, and a simple "select row by its primary key" query took as long as three minutes to handle.
Another example is the Oracle problem with foreign key locking which I have
recently ran into: I have a long transactions running in parallel,
modifying various rows of a single table (but each session touches
a different set of rows, so the access should be deadlock-free).
After creating another table with foreign key to the original one,
I started to get "deadlock detected" errors in DELETE commands.
Apparently Oracle locks not only the appropriate
row in the foreign-key table, but the whole block in this table.
So I have been getting the deadlocks when trying to delete the row
with primary key N, where another session added a row to the table
with foreign key referencing the primary key N+1 or N-1.
Replacing DELETE with UPDATE ... SET status='deleted'
and deleting afterwards from the single session fixed the problem for me.
The SQL databases are pile of rubbish, which can always surprise you not only with their by-definition properties, but often also with their implementation-dependent behaviour. Oracle is an excellent example of this.