# SQLite database locked **URL:** \ **Category:** Ignition\ **Tags:** ignition80, database, ignition81, perspective\ **Created:** [July 15, 2024, 8:46am UTC](https://forum.inductiveautomation.com/t/sqlite-database-locked/93219 "2024-07-15T08:46:04Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Alexi\_Andrzejewski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alexi_andrzejewski/32/65032_2.png) [@Alexi\_Andrzejewski](https://forum.inductiveautomation.com/u/Alexi_Andrzejewski)\ **Post date:** [July 15, 2024, 8:46am UTC](https://forum.inductiveautomation.com/t/sqlite-database-locked/93219/1 "2024-07-15T08:46:04Z")
Hi all, * * * I have a perspective project using a **SQLite** DB for storing sensors **infos** and **historian** running on my gateway. However, from time to time the DB juste refuse to operate any changes and **only SELECT** querys are accepted. Here are the details of the logs : > **Logs summary** > > From the logs I could get the following error when running this from a web browser. > > ```python > 11. system.db.runNamedQuery("sensorActions/maintenanceMode", {"code":payload["num_capteur"]}) > > ``` > > Calling this named query : > > ```python > UPDATE Capteurs SET mode = "m" > WHERE code = :code AND mode = 'a' > > ``` > > On the following Capteurs table : > > | code | temp | press | hygro | room | spot | mode | > | --- | --- | --- | --- | --- | --- | --- | > | sens\_0 | 21.5 | 32.6 | -29.6 | PA6.00001 | spot 0 | a | > | sens\_1 | 19.4 | 31.3 | -30.6 | PA6.00004 | spot 1 | a | > | sens\_2 | 17.5 | 30.8 | -28.0 | PA6.00004 | spot 2 | a | > | sens\_3 | 51.7 | 30.1 | -29.7 | PA6.00004 | spot 3 | a | > | sens\_4 | 19.6 | 31.3 | -69.6 | PA6.00004 | | a | > | sens\_5 | 20.2 | 32.3 | -30.7 | PA6.00004 | | a | > | sens\_6 | 21.3 | 30.0 | -28.6 | PA5.00005 | | a | > | sens\_7 | 23.5 | 23.5 | -29.7 | PA5.00005 | | a | > | sens\_8 | 20.4 | 55.7 | -44.1 | PA6.00004 | | a | > | sens\_9 | 61.4 | 29.5 | -26.0 | PA6.00004 | | a | > > I don't think my error comes from the request though because half-way writing this Topic, my request have finaly worked for some reasons. > > ```python > INFO | jvm 1 | 2024/07/15 09:12:56 | W [c.i.p.ComponentModel] [09:12:56.636]: Error running sensor/tableau_labos@C/root/Table.onMessageReceived(self, payload): Traceback (most recent call last): > INFO | jvm 1 | 2024/07/15 09:12:56 | File "", line 11, in onMessageReceived > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:407) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:173) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:92) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.common.db.namedquery.SecuredNamedQueryManager.execute(SecuredNamedQueryManager.java:78) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.runNamedQuery(GatewayDBUtilities.java:419) > INFO | jvm 1 | 2024/07/15 09:12:56 | at jdk.internal.reflect.GeneratedMethodAccessor104.invoke(Unknown Source) > INFO | jvm 1 | 2024/07/15 09:12:56 | at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) > INFO | jvm 1 | 2024/07/15 09:12:56 | at java.base/java.lang.reflect.Method.invoke(Unknown Source) > INFO | jvm 1 | 2024/07/15 09:12:56 | java.lang.Exception: java.lang.Exception: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked) > INFO | jvm 1 | 2024/07/15 09:12:56 | > INFO | jvm 1 | 2024/07/15 09:12:56 | com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): > INFO | jvm 1 | 2024/07/15 09:12:56 | File "", line 11, in onMessageReceived > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:407) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:173) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:92) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.common.db.namedquery.SecuredNamedQueryManager.execute(SecuredNamedQueryManager.java:78) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.runNamedQuery(GatewayDBUtilities.java:419) > INFO | jvm 1 | 2024/07/15 09:12:56 | at jdk.internal.reflect.GeneratedMethodAccessor104.invoke(Unknown Source) > INFO | jvm 1 | 2024/07/15 09:12:56 | at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) > INFO | jvm 1 | 2024/07/15 09:12:56 | at java.base/java.lang.reflect.Method.invoke(Unknown Source) > INFO | jvm 1 | 2024/07/15 09:12:56 | java.lang.Exception: java.lang.Exception: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked) > INFO | jvm 1 | 2024/07/15 09:12:56 | > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.python.core.Py.JavaError(Py.java:545) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.python.core.Py.JavaError(Py.java:536) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.python.core.PyReflectedFunction. __call__ (PyReflectedFunction.java:192) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction. __call__ (ScriptManager.java:553) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.python.core.PyObject. __call__ (PyObject.java:477) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.python.core.PyObject. __call__ (PyObject.java:481) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.python.pycode._pyx699.onMessageReceived$1(:21) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.python.pycode._pyx699.call_function() > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.python.core.PyTableCode.call(PyTableCode.java:173) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.python.core.PyBaseCode.call(PyBaseCode.java:306) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.python.core.PyFunction.function ___call__ (PyFunction.java:474) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.python.core.PyFunction. __call__ (PyFunction.java:469) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.python.core.PyFunction. __call__ (PyFunction.java:464) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:847) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:829) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runFunction(ProjectScriptLifecycle.java:868) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.common.script.ScriptManager$ScriptFunctionImpl.invoke(ScriptManager.java:1010) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$AutoRecompilingScriptFunction.invoke(ProjectScriptLifecycle.java:950) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.perspective.gateway.script.ScriptFunctionHelper.invoke(ScriptFunctionHelper.java:161) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.perspective.gateway.script.ScriptFunctionHelper.invoke(ScriptFunctionHelper.java:98) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.perspective.gateway.model.MessageHandlerCollection$MessageHandlerImpl$1.lambda$invoke$0(MessageHandlerCollection.java:86) > INFO | jvm 1 | 2024/07/15 09:12:56 | at java.base/java.util.concurrent.FutureTask.run(Unknown Source) > INFO | jvm 1 | 2024/07/15 09:12:56 | at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) > INFO | jvm 1 | 2024/07/15 09:12:56 | at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.perspective.gateway.threading.BlockingWork$BlockingWorkRunnable.run(BlockingWork.java:58) > INFO | jvm 1 | 2024/07/15 09:12:56 | at java.base/java.lang.Thread.run(Unknown Source) > INFO | jvm 1 | 2024/07/15 09:12:56 | Caused by: org.python.core.PyException: java.lang.Exception: java.lang.Exception: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked) > INFO | jvm 1 | 2024/07/15 09:12:56 | ... 26 common frames omitted > INFO | jvm 1 | 2024/07/15 09:12:56 | Caused by: java.lang.Exception: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:407) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:173) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:92) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.common.db.namedquery.SecuredNamedQueryManager.execute(SecuredNamedQueryManager.java:78) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.runNamedQuery(GatewayDBUtilities.java:419) > INFO | jvm 1 | 2024/07/15 09:12:56 | at jdk.internal.reflect.GeneratedMethodAccessor104.invoke(Unknown Source) > INFO | jvm 1 | 2024/07/15 09:12:56 | at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) > INFO | jvm 1 | 2024/07/15 09:12:56 | at java.base/java.lang.reflect.Method.invoke(Unknown Source) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.python.core.PyReflectedFunction. __call__ (PyReflectedFunction.java:190) > INFO | jvm 1 | 2024/07/15 09:12:56 | ... 23 common frames omitted > INFO | jvm 1 | 2024/07/15 09:12:56 | Caused by: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.sqlite.core.DB.newSQLException(DB.java:1179) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.sqlite.core.DB.newSQLException(DB.java:1190) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.sqlite.core.DB.execute(DB.java:985) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.sqlite.core.DB.executeUpdate(DB.java:1054) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.sqlite.jdbc3.JDBC3PreparedStatement.lambda$executeLargeUpdate$2(JDBC3PreparedStatement.java:119) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.sqlite.jdbc3.JDBC3Statement.withConnectionTimeout(JDBC3Statement.java:454) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.sqlite.jdbc3.JDBC3PreparedStatement.executeLargeUpdate(JDBC3PreparedStatement.java:118) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.sqlite.jdbc3.JDBC3PreparedStatement.executeUpdate(JDBC3PreparedStatement.java:100) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) > INFO | jvm 1 | 2024/07/15 09:12:56 | at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeUpdate(SRConnectionWrapper.java:981) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepUpdate(SRConnectionWrapper.java:181) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.runPrepQuery(NamedQueryExecutor.java:507) > INFO | jvm 1 | 2024/07/15 09:12:56 | at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:399) > INFO | jvm 1 | 2024/07/15 09:12:56 | ... 31 common frames omitted > > ``` * * * ### File locked When I searched which process was locking my file.db i found it was opened by **javaw.exe** (and not from DB Browser SQLite which already happend to me lol and would have locked the file). I understand SQLite use a **single-threaded** query method. Therefore I guess I may have a query that is either taking ages or beeing executed a lot in a short time. This is what i get by checking `Status> Connections> Databases> Details` : Before the red line is when I had issues after is when I don't anymore : ![Longest Recent Queries](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/2/52d611bad59a2129a8e9a004d2918895f045cf2e.png) * * * ### DB Connections I also noticed the amount of Database connections was 1 when having my errors and now it has changed to 0. ![DB Connections count](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/b/2b401906c9aebb46b29094bda0236887c545075f.png) **Maybe is there a way to check who exactly is using a connection for debugging purposes ?** My database connection connect URL is `jdbc:sqlite:${data}/db/laboratoires.db` So it's in localhost. * * * Any suggestions for how to resolve this issue would be greatly appreciated. Thank you! ---
**Author:** ![Martin\_Chauveliere](https://avatars.discourse-cdn.com/v4/letter/m/51bf81/32.png) [@Martin\_Chauveliere](https://forum.inductiveautomation.com/u/Martin_Chauveliere)\ **Post date:** [July 15, 2024, 9:24am UTC](https://forum.inductiveautomation.com/t/sqlite-database-locked/93219/2 "2024-07-15T09:24:24Z")
Hi Alexi, I have the same issue. If someone could help, it would be very appericated Martin. ---
**Author:** ![Hayden\_Watson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/hayden_watson/32/48792_2.png) [@Hayden\_Watson](https://forum.inductiveautomation.com/u/Hayden_Watson)\ **Post date:** [July 15, 2024, 9:42am UTC](https://forum.inductiveautomation.com/t/sqlite-database-locked/93219/3 "2024-07-15T09:42:03Z")
You may want to configure the max connections to 1 in this case as SQLite only allows 1 connection at any time. Is there any other program trying to connect to the database maybe, that is taking its only available connection? ---
**Author:** ![Samuel\_Sueur](https://avatars.discourse-cdn.com/v4/letter/s/e36b37/32.png) [@Samuel\_Sueur](https://forum.inductiveautomation.com/u/Samuel_Sueur)\ **Post date:** [July 15, 2024, 9:42am UTC](https://forum.inductiveautomation.com/t/sqlite-database-locked/93219/4 "2024-07-15T09:42:07Z")
Hi Alexi and Martin, (your names sound familiar 😉 ) Perhaps try to limit the number of connections to the DB by following this : Go to your DB connection in the "Config" Tab, then scroll at the bottom and tick "Advanced Properties", then change the "Max Active" field in the Connection Pooling to 1 ? Do the same for Max Idle Not sure if that's gonna solve your issue. ---
**Author:** ![Alexi\_Andrzejewski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alexi_andrzejewski/32/65032_2.png) [@Alexi\_Andrzejewski](https://forum.inductiveautomation.com/u/Alexi_Andrzejewski)\ **Post date:** [July 15, 2024, 9:59am UTC](https://forum.inductiveautomation.com/t/sqlite-database-locked/93219/5 "2024-07-15T09:59:07Z")
> [@Hayden\_Watson](#): > > Is there any other program trying to connect to the database This is what i thought at first but only Ignition seem to be editing the \*.db file ---
**Author:** ![Alexi\_Andrzejewski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alexi_andrzejewski/32/65032_2.png) [@Alexi\_Andrzejewski](https://forum.inductiveautomation.com/u/Alexi_Andrzejewski)\ **Post date:** [July 15, 2024, 10:06am UTC](https://forum.inductiveautomation.com/t/sqlite-database-locked/93219/6 "2024-07-15T10:06:30Z")
> [@Samuel\_Sueur](#): > > change the "Max Active" field Hey Samuel, Tanks for showing the location, Unfortunately, I found out setting the max connection to 1 disable the ability to have both designer and web browser displaying the data fetched in my DB, My bad 😅 ---
**Author:** ![Samuel\_Sueur](https://avatars.discourse-cdn.com/v4/letter/s/e36b37/32.png) [@Samuel\_Sueur](https://forum.inductiveautomation.com/u/Samuel_Sueur)\ **Post date:** [July 15, 2024, 10:42am UTC](https://forum.inductiveautomation.com/t/sqlite-database-locked/93219/7 "2024-07-15T10:42:34Z")
Honestly, I know what you're working on as I'm doing the same thing ahah You should really move to a POSTGRESQL database, for tests you can set it up on the same machine as you're currently using and also download pgadmin for easy visualisation. I checked a little, you're also storing history data to the SQLite DB, this is far from ideal as SQLite DB is better suited for storing configuration data only. I suspect performing inserts or updates while also storing history tags might cause issues. When storing tag history to a DB, consider moving to something better suited, such as postgre (free) or MySQL/SQL Server. + if at some point we have to merge our work together, postgresql will be our go-to. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [July 15, 2024, 12:17pm UTC](https://forum.inductiveautomation.com/t/sqlite-database-locked/93219/8 "2024-07-15T12:17:18Z")
> [@Alexi\_Andrzejewski](#): > > using a **SQLite** DB for storing sensors **infos** and **historian** This is the problem. SQLite is a single-threaded database designed to store slowly-changing (or **never** -changing) configuration information. It is utterly unsuited to use as a historian. You cannot "fix" this. If you need something free, use PostgreSQL or MariaDB. Full stop.