Wednesday, May 11, 2011

sqlite database locked

If you are using sqlite you will notice that locking issues are frequent. While doing some sqlite from Spring JdbcTemplate I noticed that from time to time the table would get locked:
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO employee (`first`, `email`) VALUES (?, ?)]; SQL state [null]; error code [0]; database locked; nested exception is java.sql.SQLException: database locked

The statement was run from a loop and sometimes the total of 70 records were inserted but from time to time only a fraction. Only one thread was actually accessing the table but I could only get the issue reproduced when using Jakarta JMeter to simulate several users. This made me think that somehow the driver version (3.7.2) I am using (Xerial SQLite JDBC) has a problem. Further investigation allowed me to conclude that the code was missing the @Transactional annotation and once I added that I could not longer replicate the database locking issue. Furthermore the INSERTs go way faster as well
@Transactional(rollbackFor = Exception.class, isolation = Isolation.READ_UNCOMMITTED)
    public void populateDB(...) {

As you can imagine the reason why this is solved this way is that nothing gets committed up until the last record is inserted avoiding that way a possible locking coming from the underlying "setAutoCommit(false)/setAutoCommit(true)" operations.

Spring abstracts once again a big problem from the developer. Handling this locking issues could take your breath away but with the use of Spring you just rely on an annotation.

There is a second issue I have seen related to the way Data Sources are instantiated. Basically be careful and sure you instantiate the Data Source just once.

Reusing datasources advice might look trivial and someone might say I got that under control as I use JNDI. The problem is when your datasource is to be discovered on the fly.

I have a project where a sqlite database is used per user. Clearly I cannot use JNDI but instantiate the data sources instead. I need the datasource to populate data and at the same time to allow Jasper Reports use that data. In order to accomplish that I instantiate the datasource from a Controller using a Service and I pass the Datasource to my Report Services and Jasper Engine.

No comments: