{"action":"created","issue":{"url":"https://api.github.com/repos/davidmoten/rxjava-jdbc/issues/45","labels_url":"https://api.github.com/repos/davidmoten/rxjava-jdbc/issues/45/labels{/name}","comments_url":"https://api.github.com/repos/davidmoten/rxjava-jdbc/issues/45/comments","events_url":"https://api.github.com/repos/davidmoten/rxjava-jdbc/issues/45/events","html_url":"https://github.com/davidmoten/rxjava-jdbc/issues/45","id":112041211,"number":45,"title":"Problems with SQLite and File-locking","user":{"login":"thomasnield","id":7420801,"avatar_url":"https://avatars.githubusercontent.com/u/7420801?v=3","gravatar_id":"","url":"https://api.github.com/users/thomasnield","html_url":"https://github.com/thomasnield","followers_url":"https://api.github.com/users/thomasnield/followers","following_url":"https://api.github.com/users/thomasnield/following{/other_user}","gists_url":"https://api.github.com/users/thomasnield/gists{/gist_id}","starred_url":"https://api.github.com/users/thomasnield/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/thomasnield/subscriptions","organizations_url":"https://api.github.com/users/thomasnield/orgs","repos_url":"https://api.github.com/users/thomasnield/repos","events_url":"https://api.github.com/users/thomasnield/events{/privacy}","received_events_url":"https://api.github.com/users/thomasnield/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":15,"created_at":"2015-10-18T21:06:24Z","updated_at":"2015-10-23T12:53:16Z","closed_at":"2015-10-22T22:39:38Z","body":"Hi David, \r\nI've been playing a lot with SQLite and RxJava-JDBC this weekend. This may just be a limitation of SQLite and the fact it is serverless and file-based, so obviously it has a limitation with concurrent file-locking . \r\n\r\nSuppose I create a SQLite database with these two simple tables, where `TABLE_ONE` is the parent and `TABLE_TWO` is the child.  \r\n```\r\nCREATE TABLE TABLE_ONE (\r\n    ID    INTEGER PRIMARY KEY\r\n                  NOT NULL,\r\n    VALUE INTEGER NOT NULL\r\n);\r\n\r\nCREATE TABLE TABLE_TWO (\r\n    ID         INTEGER NOT NULL\r\n                       PRIMARY KEY,\r\n    FOREIGN_ID INTEGER NOT NULL\r\n                       REFERENCES TABLE_ONE ([KEY]),\r\n    VALUE      INTEGER NOT NULL\r\n);\r\n```\r\nI then create an RxJava-JDBC procedure to insert a record into `TABLE_ONE`, and reactively take the new ID and use it to populate a `TABLE_TWO` record. \r\n```\r\npublic final class Launcher {\r\n\r\n    public static void main(String[] args) {\r\n\r\n       final Database db = Database.builder()\r\n                .url(\"jdbc:sqlite:C:/Users/Thomas/test.db\")\r\n                .pool(1, 5)\r\n                .build();\r\n\r\n        db.update(\"INSERT INTO TABLE_ONE (VALUE) VALUES (100)\")\r\n                .returnGeneratedKeys()\r\n                .getAs(Integer.class)\r\n                .flatMap(k -> db.select(\"SELECT * FROM TABLE_ONE WHERE ID = ?\")\r\n                        .parameter(k)\r\n                        .get(rs -> new Type1(rs.getInt(\"ID\"), rs.getInt(\"VALUE\")))\r\n                ).flatMap(t1 -> db.update(\"INSERT INTO TABLE_TWO (FOREIGN_ID,VALUE) VALUES (?,?)\")\r\n                        .parameter(t1.id)\r\n                        .parameter(t1.value)\r\n                        .returnGeneratedKeys()\r\n                        .getAs(Integer.class)\r\n                ).flatMap(k -> db.select(\"SELECT * FROM TABLE_TWO WHERE ID = ?\")\r\n                        .parameter(k)\r\n                        .get(rs -> new Type2(rs.getInt(\"ID\"), rs.getInt(\"FOREIGN_ID\"), rs.getInt(\"VALUE\")))\r\n\r\n        ).subscribe(System.out::println, Throwable::printStackTrace);\r\n    }\r\n    private static final class Type1 {\r\n        private final int id;\r\n        private final int value;\r\n        private Type1(int id, int value) {\r\n            this.id = id;\r\n            this.value = value;\r\n        }\r\n    }\r\n    private static final class Type2 {\r\n        private final int id;\r\n        private final int foreignId;\r\n        private final int value;\r\n        private Type2(int id, int foreignKey, int value) {\r\n            this.id = id;\r\n            this.foreignId = foreignKey;\r\n            this.value = value;\r\n        }\r\n        @Override\r\n        public String toString() {\r\n            return \"Type2{\" +\r\n                    \"id=\" + id +\r\n                    \", foreignId=\" + foreignId +\r\n                    \", value=\" + value +\r\n                    '}';\r\n        }\r\n    }\r\n}\r\n```\r\nThe insertion for `TABLE_ONE` executes successfully. But when the second insertion for `TABLE_TWO` occurs I get a classic SQLite `[SQLITE_BUSY]` error. \r\n\r\n```\r\nCaused by: java.sql.SQLException: [SQLITE_BUSY]  The database file is locked (database is locked)\r\n\tat org.sqlite.core.DB.newSQLException(DB.java:890)\r\n\tat org.sqlite.core.DB.newSQLException(DB.java:901)\r\n\tat org.sqlite.core.DB.execute(DB.java:807)\r\n\tat org.sqlite.core.DB.executeUpdate(DB.java:847)\r\n\tat org.sqlite.jdbc3.JDBC3PreparedStatement.executeUpdate(JDBC3PreparedStatement.java:86)\r\n\tat com.zaxxer.hikari.proxy.PreparedStatementProxy.executeUpdate(PreparedStatementProxy.java:61)\r\n\tat com.zaxxer.hikari.proxy.HikariPreparedStatementProxy.executeUpdate(HikariPreparedStatementProxy.java)\r\n\tat com.github.davidmoten.rx.jdbc.QueryUpdateOnSubscribe.performUpdate(QueryUpdateOnSubscribe.java:225)\r\n\t... 309 more\r\n```\r\nI had a theory that the monad was to blame and the `onCompleted()` was never called after the first INSERT, causing the connection/file to still be occupied and locked. So on the second INSERT,  the statement was not yet closed from the first INSERT, causing the `[SQLITE_BUSY]` error. \r\n\r\nI think my theory proved correct. When I blocked the first INSERT and pulled the resulting first object out of the monad, I was able to successfully use it to drive the second INSERT. \r\n\r\n```\r\n       final Database db = Database.builder()\r\n                .url(\"jdbc:sqlite:C:/Users/Thomas/test.db\")\r\n                .pool(1, 5)\r\n                .build();\r\n\r\n        Type1 type1 = db.update(\"INSERT INTO TABLE_ONE (VALUE) VALUES (100)\")\r\n                .returnGeneratedKeys()\r\n                .getAs(Integer.class)\r\n                .flatMap(k -> db.select(\"SELECT * FROM TABLE_ONE WHERE ID = ?\")\r\n                        .parameter(k)\r\n                        .get(rs -> new Type1(rs.getInt(\"ID\"), rs.getInt(\"VALUE\")))\r\n                ).toBlocking().first();\r\n\r\n                db.update(\"INSERT INTO TABLE_TWO (FOREIGN_ID,VALUE) VALUES (?,?)\")\r\n                        .parameter(type1.id)\r\n                        .parameter(type1.value)\r\n                        .returnGeneratedKeys()\r\n                        .getAs(Integer.class)\r\n                        .flatMap(k -> db.select(\"SELECT * FROM TABLE_TWO WHERE ID = ?\")\r\n                                .parameter(k)\r\n                                .get(rs -> new Type2(rs.getInt(\"ID\"), rs.getInt(\"FOREIGN_ID\"), rs.getInt(\"VALUE\")))\r\n                        ).subscribe(System.out::println, Throwable::printStackTrace);\r\n\r\n```\r\nObviously this is not an optimal workaround because we don't want to break the monad. Considering SQLite is such a ubiquitous database solution especially with Android developers, I propose we remedy this if possible. I can try to help if we want to investigate this. \r\n\r\nWhat do you think?\r\n\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/davidmoten/rxjava-jdbc/issues/comments/150562466","html_url":"https://github.com/davidmoten/rxjava-jdbc/issues/45#issuecomment-150562466","issue_url":"https://api.github.com/repos/davidmoten/rxjava-jdbc/issues/45","id":150562466,"user":{"login":"thomasnield","id":7420801,"avatar_url":"https://avatars.githubusercontent.com/u/7420801?v=3","gravatar_id":"","url":"https://api.github.com/users/thomasnield","html_url":"https://github.com/thomasnield","followers_url":"https://api.github.com/users/thomasnield/followers","following_url":"https://api.github.com/users/thomasnield/following{/other_user}","gists_url":"https://api.github.com/users/thomasnield/gists{/gist_id}","starred_url":"https://api.github.com/users/thomasnield/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/thomasnield/subscriptions","organizations_url":"https://api.github.com/users/thomasnield/orgs","repos_url":"https://api.github.com/users/thomasnield/repos","events_url":"https://api.github.com/users/thomasnield/events{/privacy}","received_events_url":"https://api.github.com/users/thomasnield/received_events","type":"User","site_admin":false},"created_at":"2015-10-23T12:53:16Z","updated_at":"2015-10-23T12:53:16Z","body":"Exactly my thoughts. Thanks again, ill take care of today.\n________________________________\nFrom: Dave Moten<mailto:notifications@github.com>\nSent: ‎10/‎23/‎2015 12:34 AM\nTo: davidmoten/rxjava-jdbc<mailto:rxjava-jdbc@noreply.github.com>\nCc: Thomas Nield<mailto:thomasnield@live.com>\nSubject: Re: [rxjava-jdbc] Problems with SQLite and File-locking (#45)\n\nNo worries, small help only. A paragraph on use with SQLLite would be a\ngreat addition to the README.md. Perhaps right at the bottom?\n\nOn 23 October 2015 at 09:40, Thomas Nield <notifications@github.com> wrote:\n\n> Let me know if you want to document this on the Readme, and thanks for\n> your help on this. Your singleton connection solution was necessary to rid\n> half the problem.\n>\n> —\n> Reply to this email directly or view it on GitHub\n> <https://github.com/davidmoten/rxjava-jdbc/issues/45#issuecomment-150376180>\n> .\n>\n\n\n---\nReply to this email directly or view it on GitHub:\nhttps://github.com/davidmoten/rxjava-jdbc/issues/45#issuecomment-150477749\n"}}