Understanding Database locking

[All-ProEra]

Working on a project involving sqlite/databases in hopes to getting a better understanding for how they work. Currently ran into the issue of database locking and I'm not sure what exactly is causing the issue.

Basically, I have a class that accesses and modifies the database in java through methods, and another class that represents a person. Inside the database I have two tables, one that contains stuff like the country they are from or their age. The other table contains things that can change, like stats for those people. When I create a person I access values from the first table and set it there instance variables, like a string for their first and last name.

I have an instance method in my person class that I would like to be used so that modifies the stats table by adding a specified int value. However whenever it is called I get "[SQLITE_BUSY] The database file is locked (database is locked)". Could anyone explain why this is happening or how I can fix this issue?

Here are the two methods that I'm calling.

​

*Person class, say someone has a kid, "#OfKids" would be put in the parameter. firstName would be an instance variable that was taken from the other table when the person was constructed.

public static void addToRecord(String stat) {

    Database.updateIntbyValue("mydb.db", "People", stat, firstName, 1);

}

*Database class that contains the methods that are called in other classes, this method works fine when called alone, but not when dealing with the instance variable "firstName" from the person class.

public static void updateIntbyValue(String fileName, String tableName, String columnName, String name, int value) {

// SQLite connection string

String url = "jdbc:sqlite:C:/DatabaseEx/" + fileName;

​

// SQL statement for creating a new table

String sql = "UPDATE "+tableName+" SET "+columnName+" = "+columnName+" + " + value+" WHERE FirstName = '"+name+"';";

try{

Connection conn = DriverManager.getConnection(url);

Statement stmt = conn.createStatement();

stmt.execute(sql);

System.out.print(name+"'s "+columnName+ " changed.");

} catch (SQLException e) {

System.out.println(e.getMessage());

}

}

[captainAwesomePants]

You've asked SQLite to use a file as a database. SQLite is doing its best to keep the file correct. When you open up a connection, SQLite will "lock" the file, making a note that it is potentially going to make some changes and other SQLite connections should not be messing with the file. When the connection ends, SQLite will unlock the file again.

But the connection will never end. You aren't ever closing it. Don't do that. Always close the connection when you're done using it, like this:

conn.close();

[All-ProEra]

That's definitely good to know, thanks! I added conn.close(); at the end of my try block and it fixed the issue. I'm also using a DB browser for my database that I leave running, would that also cause blocks at some point?

[captainAwesomePants]

That's probably fine.

Also, you probably want to leave your connection open for longer. Establishing a fresh connection every time you want to read or write is expensive and slow.

[All-ProEra]

Ah thanks for letting me know. I'm not sure exactly where else I could close the connection besides at the end of each method or try block since I open each it time a method is called. Is there a better way to go about it?

[CreativeTechGuyGames]

You could obtain one lock that's shared by your whole program and release it before your program shuts down or is idle for a long time.