Yenya's World

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.

Section: /computers (RSS feed) | Permanent link | 5 writebacks


Yenya's World: Linux and beyond - Yenya's blog.


RSS feed

Jan "Yenya" Kasprzak

The main page of this blog



Blog roll:

alphabetically :-)