SQLite in-memory database encounters SQLITE_LOCKED_SHAREDCACHE intermittently

Question

I am using mybatis 3.4.6 along with org.xerial:sqlite-jdbc 3.28.0. Below is my configuration to use an in-memory database with shared mode enabled

db.driver=org.sqlite.JDBC
db.url=jdbc:sqlite:file::memory:?cache=shared

The db.url is correct according to this test class

And I managed to setup the correct transaction isolation level with below mybatis configuration though there is a typo of property read_uncommitted according to this issue which is reported by me as well

<environment id="${db.env}">
    <transactionManager type="jdbc"/>
    <dataSource type="POOLED">
        <property name="driver" value="${db.driver}" />
        <property name="url" value="${db.url}"/>
        <property name="username" value="${db.username}" />
        <property name="password" value="${db.password}" />
        <property name="defaultTransactionIsolationLevel" value="1" />
        <property name="driver.synchronous" value="OFF" />
        <property name="driver.transaction_mode" value="IMMEDIATE"/>
        <property name="driver.foreign_keys" value="ON"/>
    </dataSource>
</environment>

This line of configuration

  <property name="defaultTransactionIsolationLevel" value="1" />

does the trick to set the correct value of PRAGMA read_uncommitted

I am pretty sure of it since I debugged the underneath code which initialize the connection and check the value has been set correctly

However with the above setting, my program still encounters SQLITE_LOCKED_SHAREDCACHE intermittently while reading, which I think it shouldn't happen according the description highlighted in the red rectangle of below screenshot. I want to know the reason and how to resolve it, though the occurring probability of this error is low.

Any ideas would be appreciated!!

The debug configurations is below


===CONFINGURATION==============================================
 jdbcDriver                     org.sqlite.JDBC
 jdbcUrl                        jdbc:sqlite:file::memory:?cache=shared
 jdbcUsername                   
 jdbcPassword                   ************
 poolMaxActiveConnections       10
 poolMaxIdleConnections         5
 poolMaxCheckoutTime            20000
 poolTimeToWait                 20000
 poolPingEnabled                false
 poolPingQuery                  NO PING QUERY SET
 poolPingConnectionsNotUsedFor  0
 ---STATUS-----------------------------------------------------
 activeConnections              5
 idleConnections                5
 requestCount                   27
 averageRequestTime             7941
 averageCheckoutTime            4437
 claimedOverdue                 0
 averageOverdueCheckoutTime     0
 hadToWait                      0
 averageWaitTime                0
 badConnectionCount             0
===============================================================

Attachments:

The exception is below

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.apache.ibatis.transaction.TransactionException: Error configuring AutoCommit.  Your driver may not support getAutoCommit() or setAutoCommit(). Requested setting: false.  Cause: org.sqlite.SQLiteException: [SQLITE_LOCKED_SHAREDCACHE]  Contention with a different database connection that shares the cache (database table is locked)
### The error may exist in mapper/MsgRecordDO-sqlmap-mappering.xml
### The error may involve com.super.mock.platform.agent.dal.daointerface.MsgRecordDAO.getRecord
### The error occurred while executing a query
### Cause: org.apache.ibatis.transaction.TransactionException: Error configuring AutoCommit.  Your driver may not support getAutoCommit() or setAutoCommit(). Requested setting: false.  Cause: org.sqlite.SQLiteException: [SQLITE_LOCKED_SHAREDCACHE]  Contention with a different database connection that shares the cache (database table is locked)

Is it possible that it's due to I use a `trigger` on that table? Will `trigger` hold on an exclusive lock on that table? — machinarium, Aug 21 '20 at 02:09

score 1 · Accepted Answer · answered Oct 21 '20 at 10:06

I finally resolved this issue by myself and share the workaround below in case someone else encounters similar issue in the future.

First of all, we're able to get the completed call stack of the exception shown below

Going through the source code indicated by the callback, we have below findings.

SQLite is built-in with auto commit enabled by default which is contradict with MyBatis which disables auto commit by default since we're using SqlSessionManager
MyBatis would override the auto commit property during connection initialization using method setDesiredAutoCommit which finally invokes SQLiteConnection#setAutoCommit
SQLiteConnection#setAutoCommit would incur a begin immediate operation against the database which is actually exclusive, check out below source code screenshots for detailed explanation since we configure our transaction mode to be IMMEDIATE

<property name="driver.transaction_mode" value="IMMEDIATE"/>

So until now, An apparent solution is to change the transaction mode to be DEFERRED. Furthermore, the solution of making the auto commit setting the same between MyBatis and SQLite has been considered as well, however, it's not adopted since there is no way to set the auto commit of SQLiteConnection during initialization stage, there would be always switching (from true to false or vice versa) and switch would cause the above error probably if transaction mode is not set properly

SQLite in-memory database encounters SQLITE_LOCKED_SHAREDCACHE intermittently

1 Answers1