File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes SOLVED: SQL Drop Table; database always locked Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SOLVED: SQL Drop Table; database always locked" Watch "SOLVED: SQL Drop Table; database always locked" New topic

SOLVED: SQL Drop Table; database always locked

Joe Areeda
Ranch Hand

Joined: Apr 15, 2011
Posts: 331

The situation I have is a thick client that runs locally and get SOME of it's tables from a remote server. The tables it gets from the server are read only. I want to download what I need and work off line, then upload.

I can go into more detail about the design but it's not very relevant to the problem.

The problem is that in the latest version I added a column to one of the tables. Since they are read only, I figured the easiest way to deal with this would be to DROP, CREATE, download all the read only tables.

I sometimes have the command line sqlite3 program open but idle when debugging but even without it the DROP statement ("DROP TABLE IF EXISTS account") always returns "DATABASE LOCKED". Create and download work fine. If I enter that (those) drop statements into the command line program it works fine (cli program drops the tables and the create and download proceeds).

My googling produced this tidbit at:
Before executing a statement that modifies the database schema (i.e. a CREATE or DROP TABLE statement), a connection must obtain a write-lock on sqlite_master.

It's odd that the CREATE statement does this implicitly but the DROP doesn't but that may be the case.

Anybody know how to request such a lock?
Anybody have ANY suggestion on what to try? This is my "any idiot can see it's not my fault" write up which usually exposes my faulty logic but I still don't see what I'm doing wrong or not doing.

Thanks for taking the time to read this.


It's not what your program can do, it's what your users do with the program.
Joe Areeda
Ranch Hand

Joined: Apr 15, 2011
Posts: 331

I found the problem. The trick was to generate a small program to demonstrate I was obviously doing everything right.

Since that thing worked without any hint that I wasn't doing everything I needed to, I dug deeper and found it.

It seems all ResultSet(s) have to be closed. As far as I can tell it's not good enough to let them go out of scope although it was a lot easier to just close them when I was finished with them so I haven't really verified everyone went out of scope, I may have one declared as a field in the Class instead of a local variable.

Evidently DROP TABLE is pickier than CREATE, INSERT, and UPDATE which are pickier than SELECT. It makes sense from what I've read about the levels of locking.

I'm still not completely clear on how this locking stuff is handled. the SQL statement BEGIN EXCLUSIVE seems to request the highest level lock that's available to user programs but that doesn't return any indication something is left open.

Anyway, closing results and statements when we're done with them is not just a good idea, it's required in this case.

Even though I found the answer before anyone responded, I couldn't have done it without the discipline of trying to ask an intelligent question (notice I said try) and trying to back it up with a clean and short demo program.

Thanks Big Moose for being here.

I agree. Here's the link:
subject: SOLVED: SQL Drop Table; database always locked
It's not a secret anymore!