SQLite Forum

SQLite in-memory database encounters SQLITE_LOCKED_SHAREDCACHE intermittently
Login

SQLite in-memory database encounters SQLITE_LOCKED_SHAREDCACHE intermittently

(1) By live to learn (live_to_learn) on 2020-08-12 04:22:15

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**

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

The `db.url` is correct according to this [test class](https://github.com/xerial/sqlite-jdbc/blob/f948552fa06d475c4f1e72fdc2f72cbb18269217/src/test/java/org/sqlite/ConnectionTest.java)

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](https://github.com/xerial/sqlite-jdbc/issues/521) 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 quote below. 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!!


Quote from [link](https://sqlite.org/rescode.html#locked_sharedcache):

For example, if the other database connection is holding an exclusive lock on the database, then the database connection that receives this error will be unable to read or write any part of the database file unless it has the read_uncommitted pragma enabled.


Attachments:

The exception is below

```java
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)
```

(2) By live to learn (live_to_learn) on 2020-08-21 02:07:57 in reply to 1 [link]

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?

(3) By Larry Brasfield (LarryBrasfield) on 2020-08-21 09:59:52 in reply to 2 [link]

No, that is not the cause of the problem.  A trigger simply causes additional things to sometimes (conditionally) be done with a connection, inside of an active transaction.

What you have not mentioned in your post(s) is any reason to believe or disbelieve that more than one connection is being held on the DB or more than one thread is using the one known connection.  Since these possibilities are part of the most likely cause of your problem, they should be resolved before other conjectures are explored. The error code/message is informing you that the instigating DB activity could not proceed because the attempt lost in the contention for an exclusive resource. The question then is "What other activity won in that contention?"

(4) By Dan Kennedy (dan) on 2020-08-21 10:13:35 in reply to 1 [link]

If a writer is holding open a transaction that has modified the database schema, any readers on the same shared-cache will see SQLITE_LOCKED_SHAREDCACHE. Even if they're using "PRAGMA read_uncommitted = 1".

(5) By live to learn (live_to_learn) on 2020-10-21 10:08:28 in reply to 4 [link]

Thanks Larry and Dan for your replies. It's very helpful for me to know the problem better. 

I finally located the root cause for this issue by myself. And I share it below in case anyone else comes across similar issue in the future. For the workaround, it's highly appreciated that if there is better one. 

First of all, my program does have multiple threads, but only one thread is inserting data, others are reading data and there is no problem of that. The unexpected thing we fail to think of is the reading thread would incur a **begin intermediate** operation to change the setting of **autoCommit** thus causing contention and inevitable winners and losers thereafter. 

I share the link below since there are screenshots to illustrate this issue detailedly and I find no good way to organize them here.

[workaround](https://stackoverflow.com/questions/63361787/sqlite-in-memory-database-encounters-sqlite-locked-sharedcache-intermittently/64461243#64461243)