Hello! I have made a SQLite database for a plugin where stores info about player's selection in a GUI. All works great when one player chooses something, but when the second player chooses another effect in the GUI, the server starts lagging for a few seconds and then sends an error in the console. I've never worked with databases before. Can someone help, please? My database code: Code: public class DatabaseManager { public static final DatabaseManager instance = new DatabaseManager(); private final Connection connection; private final Statement statement; private DatabaseManager() { try { connection = DriverManager.getConnection("jdbc:sqlite:milkyanimations-db.sqlite"); statement = connection.createStatement(); statement.execute("CREATE TABLE IF NOT EXISTS Effects(player_name TEXT, effect TEXT);"); } catch (SQLException e) { throw new RuntimeException(e); } } public void updateChoice(Player player, String effect) { try { if (getSelectedEffect(player) != null) { PreparedStatement statement = connection.prepareStatement("UPDATE Effects SET effect = ? WHERE player_name = ?"); statement.setString(1, effect); statement.setString(2, player.getName()); statement.executeUpdate(); } else { PreparedStatement statement = connection.prepareStatement("INSERT INTO Effects(player_name, effect) VALUES(?, ?)"); statement.setString(1, player.getName()); statement.setString(2, effect); statement.executeUpdate(); } } catch (SQLException e) { throw new RuntimeException(e); } } public String getSelectedEffect(Player player) { String effect = null; try { PreparedStatement statement = connection.prepareStatement("SELECT effect FROM Effects WHERE player_name = ?"); statement.setString(1, player.getName()); ResultSet resultSet = statement.executeQuery(); if (resultSet.next()) { effect = resultSet.getString("effect"); } } catch (SQLException e) { e.printStackTrace(); } return effect; } } Error: Code: [18:29:00 WARN]: Can't keep up! Is the server overloaded? Running 5979ms or 119 ticks behind [18:29:03 ERROR]: Could not pass event InventoryClickEvent to mAnimations v5.2 java.lang.RuntimeException: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked) at milkyland.milkyanimations.database.DatabaseManager.updateChoice(DatabaseManager.java:43) ~[MilkyAnimations.jar:?] at milkyland.milkyanimations.commands.MilkyAnimationsCommand.saveSelectedEffect(MilkyAnimationsCommand.java:166) ~[MilkyAnimations.jar:?] at milkyland.milkyanimations.commands.MilkyAnimationsCommand$4.onClick(MilkyAnimationsCommand.java:87) ~[MilkyAnimations.jar:?] at milkyland.milkyanimations.utils.gui.events.GUIEvents.onClick(GUIEvents.java:20) ~[MilkyAnimations.jar:?] at com.destroystokyo.paper.event.executor.asm.generated.GeneratedEventExecutor275.execute(Unknown Source) ~[?:?] at org.bukkit.plugin.EventExecutor.lambda$create$1(EventExecutor.java:75) ~[paper-api-1.18.2-R0.1-SNAPSHOT.jar:?] at co.aikar.timings.TimedEventExecutor.execute(TimedEventExecutor.java:80) ~[paper-api-1.18.2-R0.1-SNAPSHOT.jar:git-Paper-388] at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:70) ~[paper-api-1.18.2-R0.1-SNAPSHOT.jar:?] at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:669) ~[paper-api-1.18.2-R0.1-SNAPSHOT.jar:?] at net.minecraft.server.network.ServerGamePacketListenerImpl.handleContainerClick(ServerGamePacketListenerImpl.java:2861) ~[?:?] at net.minecraft.network.protocol.game.ServerboundContainerClickPacket.handle(ServerboundContainerClickPacket.java:58) ~[?:?] at net.minecraft.network.protocol.game.ServerboundContainerClickPacket.handle(ServerboundContainerClickPacket.java:23) ~[?:?] at net.minecraft.network.protocol.PacketUtils.lambda$ensureRunningOnSameThread$1(PacketUtils.java:51) ~[?:?] at net.minecraft.server.TickTask.run(TickTask.java:18) ~[paper-1.18.2.jar:git-Paper-388] at net.minecraft.util.thread.BlockableEventLoop.doRunTask(BlockableEventLoop.java:153) ~[?:?] at net.minecraft.util.thread.ReentrantBlockableEventLoop.doRunTask(ReentrantBlockableEventLoop.java:24) ~[?:?] at net.minecraft.server.MinecraftServer.doRunTask(MinecraftServer.java:1400) ~[paper-1.18.2.jar:git-Paper-388] at net.minecraft.server.MinecraftServer.d(MinecraftServer.java:188) ~[paper-1.18.2.jar:git-Paper-388] at net.minecraft.util.thread.BlockableEventLoop.pollTask(BlockableEventLoop.java:126) ~[?:?] at net.minecraft.server.MinecraftServer.pollTaskInternal(MinecraftServer.java:1377) ~[paper-1.18.2.jar:git-Paper-388] at net.minecraft.server.MinecraftServer.pollTask(MinecraftServer.java:1370) ~[paper-1.18.2.jar:git-Paper-388] at net.minecraft.util.thread.BlockableEventLoop.managedBlock(BlockableEventLoop.java:136) ~[?:?] at net.minecraft.server.MinecraftServer.tickServer(MinecraftServer.java:1448) ~[paper-1.18.2.jar:git-Paper-388] at net.minecraft.server.MinecraftServer.runServer(MinecraftServer.java:1226) ~[paper-1.18.2.jar:git-Paper-388] at net.minecraft.server.MinecraftServer.lambda$spin$0(MinecraftServer.java:316) ~[paper-1.18.2.jar:git-Paper-388] at java.lang.Thread.run(Thread.java:842) ~[?:?] Caused by: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked) at org.sqlite.core.DB.newSQLException(DB.java:1030) ~[sqlite-jdbc-3.36.0.3.jar:?] at org.sqlite.core.DB.newSQLException(DB.java:1042) ~[sqlite-jdbc-3.36.0.3.jar:?] at org.sqlite.core.DB.execute(DB.java:881) ~[sqlite-jdbc-3.36.0.3.jar:?] at org.sqlite.core.DB.executeUpdate(DB.java:922) ~[sqlite-jdbc-3.36.0.3.jar:?] at org.sqlite.jdbc3.JDBC3PreparedStatement.executeUpdate(JDBC3PreparedStatement.java:98) ~[sqlite-jdbc-3.36.0.3.jar:?] at milkyland.milkyanimations.database.DatabaseManager.updateChoice(DatabaseManager.java:40) ~[MilkyAnimations.jar:?] ... 25 more
public void saveSelectedEffect(Player player, String effect) {DatabaseManager.instance.updateChoice(player, effect); }
First, you shouldn't save a reference to a Statement object like you are in the constructor. Second, whenever you finish processing results from a query, you should close the associated statement. So for example, in the constructor, call "statement.close()" after the call to "statement.execute(...)" - this will free up any database resources associated with the statement. Do the same thing in the updateChoice and getEffect methods. I recommend using a try-with-resources statement - this will automatically close open resources (in this case, the Statement/PreparedStatement objects). I'm not sure if this is the problem, but let me know if it works.
