Database is locked on SELECT only calls?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Database is locked on SELECT only calls?

John Duprey
Hi All,

I'm using sqlite 3.2.0 - the native C/C++ interface.  I'm seeing
something that I haven't seen before.  I have 4 programs accessing the
same db file.  The db file is on a slow Windows network share (NAS
device).  The 4 programs are the only processes accessing the DB file
and all of them are executing a similar SELECT (read only) SQL
statement. However, intermittently, my program logs this error:

Failed to get category meta info for CLMLR - 293831 - title using:
SELECT value FROM category_meta_information
  WHERE
    taxonomy_id=13 AND
    category_id=293831 AND
    name='title' AND
    data_type_id=(SELECT rowid FROM data_type WHERE type='string');
because database is locked

Is the database really locked or is the NAS latency confusing sqlite?
Does the sub-select for rowid on data_type create some sort of temp
table that locks the file for a second?  I understand that 3.2.2 is
out.  Would this address any locking issues?

I would appreciate any help or insight anyone might have on this matter.

Thanks,
-John
Reply | Threaded
Open this post in threaded view
|

Re: Database is locked on SELECT only calls?

Massimo Gaspari

Dear John,

I am using an application linked with sqlite 3.2, using a database file
which is stored in a file server and marked "read only". Sometimes
when two instances of the application (running in different PC) perform  a
SELECT statement (read only) I got the same problem
The first "instance" can use the database but the second instance (started
after the first) get a "database is locked" selecting the same table and
rows.

It seems the same problem.

The application and the library have been compiled with gcc (3.4.2) in  a
Windows NT/2000/XP environment.

Regards

Massimo



John Duprey <[hidden email]> on 21/06/2005 22.59.12

Please respond to [hidden email]

To:    [hidden email]
cc:

Subject:    [sqlite] Database is locked on SELECT only calls?

Hi All,

I'm using sqlite 3.2.0 - the native C/C++ interface.  I'm seeing
something that I haven't seen before.  I have 4 programs accessing the
same db file.  The db file is on a slow Windows network share (NAS
device).  The 4 programs are the only processes accessing the DB file
and all of them are executing a similar SELECT (read only) SQL
statement. However, intermittently, my program logs this error:

Failed to get category meta info for CLMLR - 293831 - title using:
SELECT value FROM category_meta_information
  WHERE
    taxonomy_id=13 AND
    category_id=293831 AND
    name='title' AND
    data_type_id=(SELECT rowid FROM data_type WHERE type='string');
because database is locked

Is the database really locked or is the NAS latency confusing sqlite?
Does the sub-select for rowid on data_type create some sort of temp
table that locks the file for a second?  I understand that 3.2.2 is
out.  Would this address any locking issues?

I would appreciate any help or insight anyone might have on this matter.

Thanks,
-John








Reply | Threaded
Open this post in threaded view
|

Re: Database is locked on SELECT only calls?

D. Richard Hipp
Please run your program in a debugger and tell
me what the subroutine "isNT()" returns in os_win.c.
If it is returning false, that would explain your
occasional inability to get a read lock on a
read-only database.  

Perhaps a windows programmer can suggest a better
implementation of the isNT() function?
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Database is locked on SELECT only calls?

John Duprey
isNT() returns 1(true)

I've only seen this database lock problem while access the db file on
the (slow) NAS - local file systems seem to work fine.  Even accessing
it through one of our Windows shares seems to work fine.  I'll keep
you posted if I find any more information related to this problem.

FYI
I built SQLite using the native MS C++ compiler from Visual Studio 7.1.
Compiler version:
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 13.10.3077 for 80x86
Copyright (C) Microsoft Corporation 1984-2002. All rights reserved.


On 6/22/05, D. Richard Hipp <[hidden email]> wrote:

> Please run your program in a debugger and tell
> me what the subroutine "isNT()" returns in os_win.c.
> If it is returning false, that would explain your
> occasional inability to get a read lock on a
> read-only database.
>
> Perhaps a windows programmer can suggest a better
> implementation of the isNT() function?
> --
> D. Richard Hipp <[hidden email]>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Database is locked on SELECT only calls?

Lindsay
In reply to this post by D. Richard Hipp
D. Richard Hipp wrote:

>Please run your program in a debugger and tell
>me what the subroutine "isNT()" returns in os_win.c.
>If it is returning false, that would explain your
>occasional inability to get a read lock on a
>read-only database.  
>
>Perhaps a windows programmer can suggest a better
>implementation of the isNT() function?
>  
>

Haven't looked at your source, but the following has never failed:

bool IsNT()
{
    DWORD ver = GetVersion();
    if (ver & 0x80000000)
        return false;
    else
        return true;
};

bool Is95()
{
    return ! IsNT();
};

This is used in our app on 1000's of PC's, if it failed the PC's would
crash - badly. Believe me I would know ...

--
Lindsay