my dog learned polymorphism*
The moose likes JDBC 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


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SOLVED: SQL Drop Table; database always locked" Watch "SOLVED: SQL Drop Table; database always locked" New topic
Author

SOLVED: SQL Drop Table; database always locked

Joe Areeda
Ranch Hand

Joined: Apr 15, 2011
Posts: 294
    
    2

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: http://www.sqlite.org/sharedcache.html
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.

Joe


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: 294
    
    2

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.

Joe
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SOLVED: SQL Drop Table; database always locked
 
Similar Threads
Using ant create-tables in NetBeans 5.5
MySql db: how to backup data only and backup tables only
More on locking
-1 to lock the db
Using ant create-tables in NetBeans 5.5