SQLite Database Is Locked

Discussion in 'Spigot Plugin Development' started by MrWaffleman, May 31, 2016.

  1. For a while now, SQLite has been incredibly laggy on my server, I used SQLite and MySQL before, and it didn't cause this much lag.

    Code (Text):
    public static void createKit(Player p, int kitNumber, String items, boolean hasName, String name) {
            BukkitRunnable r = new BukkitRunnable() {
                @Override
                public void run() {
                    try {
                        if (hasName) {
                            PersonalKits.sqlite.query("INSERT INTO kits(player, items, number, name) VALUES('" + p.getUniqueId().toString() + "', '" + items + "', '" + kitNumber + "', '" + name + "')");
                        } else {
                            PersonalKits.sqlite.query("INSERT INTO kits(player, items, number, name) VALUES('" + p.getUniqueId().toString() + "', '" + items + "', '" + kitNumber + "', 'Kit " + kitNumber + "')");
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            };
            r.runTaskAsynchronously(PersonalKits.plugin);
        }
    Whenever this is queried, nothing regarding this plugin can work, I can't run any of the plugin commands, but I can do other things like open another plugin's features, chat, etc. It then prints an error stating the Database is locked, I don't need to point out any line numbers, but it's whenever I query in both of those scenarios (hasName, or "does not hasName"). I'm even running it on another thread...
     
  2. SQLite doesn't support concurrent acces, use a single thread executor. You can at most get concurrent read access with WAL mode enabled (write ahead logging).
     
  3. Seems like it's doing the exact same result with this code:
    Code (Text):
    Executor executor = Executors.newSingleThreadExecutor();
            executor.execute(new Runnable() {
                @Override
                public void run() {
                    try {
                        if (hasName) {
                            PersonalKits.sqlite.query("INSERT INTO kits(player, items, number, name) VALUES('" + p.getUniqueId().toString() + "', '" + items + "', '" + kitNumber + "', '" + name + "')");
                        } else {
                            PersonalKits.sqlite.query("INSERT INTO kits(player, items, number, name) VALUES('" + p.getUniqueId().toString() + "', '" + items + "', '" + kitNumber + "', 'Kit " + kitNumber + "')");
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            });
    I looked over this thread, but even those solutions don't seem to work, there are plenty of plugins that use SQLite, and even ones that I've used that don't do things like this. It seems like running 2 things on one thread creates massive holdup on all of the threads. But if they work separately they do it lightning fast, I could just do some synchronized() thing, and wait until the thread is finished, but I don't know if that would benefit if many players are accessing it, since it's putting stress on the server, not the client.
     
  4. @MrWaffleman creating a new executor each time you need to schedule a runnable is exactly the same as using BukkitScheduler#runTaskAsynchronously (and as you noticed, won't work). Create the executor once, and use that same instance for all your DB access.

    Well technically you can only run one thing on a thread at a time (execution is sequential: it will first execute the first task, and execute the second task after finishing the first)
    Not really, you simply won't notice the difference since the slower task (the database IO one) will not force the game thread to wait
    wait-notify (as it's called) is blocking - something you don't want to do to the main thread. Use callbacks or promises instead.