Database Locked against DELETE statements

[wonea]

I've been playing around with some code, and notice I cannot use
DELETE statements on my database even though I can use SELECT
statements freely without any problems. Why would this be the case?
The database says it locked, and I've checked no eternal database apps
where running, though usually SQLite Manager Firefox extension.

Here's the error;

Error: [Exception... "Component returned failure code: 0x8052000e
(NS_ERROR_FILE_IS_LOCKED) [mozIStorageStatement.executeStep]"
nsresult: "0x8052000e (NS_ERROR_FILE_IS_LOCKED)" location: "JS
frame :: chrome://myapp/content/main.js :: startApp :: line 631"
data: no]
Source File: chrome://myapp/content/main.js
Line: 631

Sample of my code;

var storageService = Components.classes["@mozilla.org/storage/service;
1"]
.getService(Components.interfaces.mozIStorageService);
var mDBConn = storageService.openDatabase(file);

statement = mDBConn.createStatement("DELETE FROM student WHERE
studentid = '3';");
statement.executeStep();

Now I've run this query in SQLite Manager with no problem. Just
wondering what I'm doing wrong, is there some kind of administration
rights I have to enable. Alternatively I was investigating the idea
of maybe the SQL Storage templates were conflicting.

I did some digging around and found a similar problem with Mozilla
Weave, "places code and weave are trying to access the database at the
same time" https://labs.mozilla.com/forum/comments.php?DiscussionID=1652

I'm really not sure, hence my post. Tried putting statement.reset
before the statements to no avail. What can I do to avoid this
problem?

Thanks in advance for any help

Gareth

[Arivald]

wonea pisze:

Error: NS_ERROR_FILE_IS_LOCKED explain what happens.

Probably You have active SELECT statement when you try to DELETE. This
SELECT probably implicitly locks current row or table, so You cant do
DELETE until You free/release SELECT.

To do non-locking SELECT You may use something like this:

SELECT * FROM TABLENAME WITH(NOLOCK) ...

"WITH(NOLOCK)" is a hint for DB to do not lock row while selecting data.
Also it should read this row even if it is locked already (by other
SELECT or UPDATE).

BTW, I do not know table hints ("WITH(NOLOCK)") are supported by
SQLite... If not, You need to store record id, close SELECT, and then do
DELETE.

--
Arivald

[Philip Chee]

On Wed, 28 Jan 2009 10:53:29 +0100, Arivald wrote:

> Error: NS_ERROR_FILE_IS_LOCKED explain what happens.

> Probably You have active SELECT statement when you try to DELETE. This
> SELECT probably implicitly locks current row or table, so You cant do
> DELETE until You free/release SELECT.

Why would SELECT lock anything? Any RDBMS with read consistency
shouldn't need a lock when a SELECT is issued. And as far as I know
SQLite is ACID compliant.

Phil (Oracle DBA)

--
Philip Chee <phi...@aleytys.pc.my>, <phili...@gmail.com>
http://flashblock.mozdev.org/ http://xsidebar.mozdev.org
Guard us from the she-wolf and the wolf, and guard us from the thief,
oh Night, and so be good for us to pass.
[ ]"Apple I" (c) Copyright 1767, Sir Isaac Newton.
* TagZilla 0.066.6

[Arivald]

Philip Chee pisze:

> On Wed, 28 Jan 2009 10:53:29 +0100, Arivald wrote:
>
>> Error: NS_ERROR_FILE_IS_LOCKED explain what happens.
>
>> Probably You have active SELECT statement when you try to DELETE. This
>> SELECT probably implicitly locks current row or table, so You cant do
>> DELETE until You free/release SELECT.
>
> Why would SELECT lock anything? Any RDBMS with read consistency

I don't know why. I just have this problem with MSSQL 2000 via ODBC.
Sometimes it ends in deadlock. On the other hand, I have no such problem
with MSSQL 2005, nor with Sybase ASA. So maybe it was MSSQL 2000 <=>
ODBC issue?

--
Arivald

[Igor Tandetnik]

Philip Chee <phili...@gmail.com> wrote:
> On Wed, 28 Jan 2009 10:53:29 +0100, Arivald wrote:
>
>> Error: NS_ERROR_FILE_IS_LOCKED explain what happens.
>
>> Probably You have active SELECT statement when you try to DELETE.
>> This SELECT probably implicitly locks current row or table, so You
>> cant do DELETE until You free/release SELECT.
>
> Why would SELECT lock anything? Any RDBMS with read consistency
> shouldn't need a lock when a SELECT is issued. And as far as I know
> SQLite is ACID compliant.

SQLite implements a multiple-readers-single-writer lock on the whole
database. While a reader is in progress, a write transaction cannot be
committed. For more details, see http://sqlite.org/atomiccommit.html

Igor Tandetnik

[Philip Chee]

On Wed, 28 Jan 2009 12:40:12 -0500, Igor Tandetnik wrote:

> SQLite implements a multiple-readers-single-writer lock on the whole
> database. While a reader is in progress, a write transaction cannot be
> committed. For more details, see http://sqlite.org/atomiccommit.html

Ah, thanks. I am used to the Oracle read consistency model where readers
do not block writers and writers do not block readers.

Phil

--
Philip Chee <phi...@aleytys.pc.my>, <phili...@gmail.com>
http://flashblock.mozdev.org/ http://xsidebar.mozdev.org
Guard us from the she-wolf and the wolf, and guard us from the thief,
oh Night, and so be good for us to pass.

[ ]If a program is useful, it must be changed.
* TagZilla 0.066.6

[wonea]

> SQLite implements a multiple-readers-single-writer lock on the whole
> database. While a reader is in progress, a write transaction cannot be
> committed. For more details, seehttp://sqlite.org/atomiccommit.html

I was under impression that once a query was ran and executed it was
finished. Anyway, there's nothing being written to my database at
all. Gone through my code. Only about two dozen SELECT statements
which make up the GUI. I've hunted through my code looking and
failing to find any UPDATE/INSERT/DELETE code. So as far as I'm
aware, there should be no writer block.

What's my best course of action? Help debugging this problem! Can I
see the SQL status in Venkman, and what's blocking it?

Also, nothing has been changed in the locking mode so it should be
functioning as normal. - http://www.sqlite.org/pragma.html

Gareth

[wonea]

Igor, thanks for the advice. After reading Mozilla documentation is
there another way around this?

I mean can I clear or pause all SELECT statements currently locking
the database, shoot my SQL code into the mix, then restart things off
again? I know, it's a long shot! Just don't fancy shoving everything
to a temporary table, then writing my data later.

[wonea]

Just thinking because of this statement on the MDC Storage
documentation "database locks come into play (with multiple
connections) in addition to table locks. Connection 2 (modifying
connection) will not be able to modify (write to) the database while
the Connection 1 (reading connection) is reading it. Connection 2 will
require an exclusive lock to execute a modifying SQL command, which it
cannot get as long as Connection 1 has active statements reading the
database (Connection 1 has a shared read lock during this time which
prohibits any other connections from getting an exclusive lock)."

Could a custom connection be used for multiple SQL templates?

[Igor Tandetnik]

wonea <wone...@gmail.com> wrote:
> Igor, thanks for the advice. After reading Mozilla documentation is
> there another way around this?
>
> I mean can I clear or pause all SELECT statements currently locking
> the database

Yes - you can call reset() or finalize() on them. I thought I was clear
on that.

Igor Tandetnik

[Arivald]

wonea pisze:

I think You should cache all SELECT data.
Open SELECT, read all data to array (indexed by field name), then close
SELECT (reste() or finalize()). Then run Your processing loop, and from
this loop post updates (UPDATE, INSERT DELETE).

To speed up process, You may try to post updates asynchronously, so
executing them will not slow down Your main processing loop.

Maybe even reading SELECT to array can be done in async way?

--
Arivald

[wonea]

Thanks for all your help. Got it sorted. Investigating asynchronous
commits as well. Cheers!