[sqlite] Locked database

[Cecil Westerhof]

2015-12-27 21:51 GMT+01:00 Cecil Westerhof :

> invocation), you should use SQLite binding to some real program language
>
>> (perl, tcl, python,...) instead of sh and sqlite3.
>
>
> ?Well, for just checking if a database is locked I think it is good
> enough. But I also wrote a Java program for it:
>
???


> I should generalise it, so it can be used for any SQLite database.
>

?Generalised version:
?
https://github.com/CecilWesterhof/JavaExamples/blob/master/CheckSQLiteLocked.java




> --
> Cecil Westerhof
>



-- 
Cecil Westerhof

[sqlite] Locked database

[Cecil Westerhof]

2015-12-27 18:11 GMT+01:00 Yuriy M. Kaminskiy :

> (I know, I'm a bit late for discussion, but...)
>
> Cecil Westerhof 
> writes:
>
> > 2015-12-14 12:40 GMT+01:00 Clemens Ladisch :
> >
> >> Cecil Westerhof wrote:
> >> > I have a crontab job which uses a SQLite database. Sometimes this
> database
> >> > is locked because I used SQLite DB Browser, but did not Write or
> Revert
> >> > Changes. It looks like that when a database is locked there is a file
> with
> >> > the database name with -journal appended to it. Can I count on this?
> >>
> >> The -journal file exists when there is some changed data that might
> >> need to be rolled back.  It is possible for the DB to be locked before
> >> some changed data is actually written.
> >>
> >> In WAL mode, there is no journal.  (And if your job is read only, it
> >> then would not be blocked by concurrent writes.)
> >>
> >
> > ?No, I need to write also.?
>
> Still, if read-only concurrent transactions are more likely, it will get
> rid of (some) waiting on locks.
>

?I know and in a program where I am only reading there is no problem.

?


> >> > Then I could write a script to warn me about the lock.
> >>
> >> You could use SQLite to check whether the DB is locked:
> >>
> >>   if ! sqlite3 my.db "begin immediate"; then
> >> echo "is locked"
> >>   fi
> >>
> >
> > ?Thanks, I improved a little on it:
> > sqlite3 "${DATABASE}" "begin immediate" 2>/dev/null
>
> ***[1]
>
> > errorCode="${?}"
> > if [[ "${errorCode}" -eq 5 ]] ; then
> > printf "${DATABASE} is locked\n"
> > elif [[ "${errorCode}" -ne 0 ]] ; then
> > printf "Error ${errorCode} while accessing ${DATABASE}\n"
> > else
> > printf "${DATABASE} is free\n"
> > fi
>
> ***[2]
>
> This check is racy and must not be used (What will happen if you will
> start concurrent queries in DB Browser somewhere between [1] and [2]?
> Right,
> this check won't detect database is locked, but your later (actual) code
> *will* fail on attempt to lock).
>

?You did not read the problem correctly. The program is only needed to
check if a database is inadvertently locked. In this way I can be notified,
so that the database is not locked when the cron job is going to send
proverbs and need to change the database.

 ?


> 1) You should set reasonable sqlite3_busy_timeout (e.g. with `sqlite3
> -cmd '.timeout 12' ...` [for 2 minutes timeout]). By default, it is 0,
> so even if database is only shortly locked, it will still fail, even though
> waiting a bit would help.
>

?My experience is that there is a wait of around two seconds. At least in
Bash, Python and Java.

?


> 2b) If you cannot express your DB change as static SQL statement(s)
> (e.g., if some statements use results obtained in previous SELECT
> queries, so you cannot feed your whole SQL code into single sqlite3
> invocation), you should use SQLite binding to some real program language
> (perl, tcl, python,...) instead of sh and sqlite3.


?Well, for just checking if a database is locked I think it is good enough.
But I also wrote a Java program for it:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;


public class CheckProverbsLocked {
// We use this class as a program,
// so we make sure the class cannot be initiated
private CheckProverbsLocked() {
}

// Check if the database is locked.
// If it is give message and return 1
// On other error give message about the error and return 2
// Otherwise no message and return 0
public static void main(String [] args) throws Exception {
Connection   conn;
Statementstmt;

Class.forName("org.sqlite.JDBC");
conn =
DriverManager.getConnection("jdbc:sqlite:proverbs.sqlite");
stmt  = conn.createStatement();
try {
stmt.executeUpdate("BEGIN IMMEDIATE");
} catch (SQLException e) {
// In a stand-alone program the next two statements are not
necessary,
// but I prefer well written code, so I use them
stmt.close();
conn.close();
if (e.getMessage().equals("database is locked")) {
System.out.println("The database is locked.");
System.exit(1);
} else {
System.err.printf("Unexpected error: %s\n",
e.getMessage());
System.exit(2);
}
}
// In a stand-alone program these statements are not necessary,
// but I prefer well written code, so I use them
stmt.executeUpdate("ROLLBACK");
stmt.close();
conn.close();
}

}
?
?The reason I use getMessage instead of getErrorCode is that getErrorCode
return 0 instead of 5.?

I should generalise it, so it can be used for any SQLite database.

-- 
Cecil Westerhof

[sqlite] Locked database

[Yuriy M. Kaminskiy]

(I know, I'm a bit late for discussion, but...)

Cecil Westerhof 
writes:

> 2015-12-14 12:40 GMT+01:00 Clemens Ladisch :
>
>> Cecil Westerhof wrote:
>> > I have a crontab job which uses a SQLite database. Sometimes this database
>> > is locked because I used SQLite DB Browser, but did not Write or Revert
>> > Changes. It looks like that when a database is locked there is a file with
>> > the database name with -journal appended to it. Can I count on this?
>>
>> The -journal file exists when there is some changed data that might
>> need to be rolled back.  It is possible for the DB to be locked before
>> some changed data is actually written.
>>
>> In WAL mode, there is no journal.  (And if your job is read only, it
>> then would not be blocked by concurrent writes.)
>>
>
> ?No, I need to write also.?

Still, if read-only concurrent transactions are more likely, it will get
rid of (some) waiting on locks.

>> > Then I could write a script to warn me about the lock.
>>
>> You could use SQLite to check whether the DB is locked:
>>
>>   if ! sqlite3 my.db "begin immediate"; then
>> echo "is locked"
>>   fi
>>
>
> ?Thanks, I improved a little on it:
> sqlite3 "${DATABASE}" "begin immediate" 2>/dev/null

***[1]

> errorCode="${?}"
> if [[ "${errorCode}" -eq 5 ]] ; then
> printf "${DATABASE} is locked\n"
> elif [[ "${errorCode}" -ne 0 ]] ; then
> printf "Error ${errorCode} while accessing ${DATABASE}\n"
> else
> printf "${DATABASE} is free\n"
> fi

***[2]

This check is racy and must not be used (What will happen if you will
start concurrent queries in DB Browser somewhere between [1] and [2]? Right,
this check won't detect database is locked, but your later (actual) code
*will* fail on attempt to lock).

1) You should set reasonable sqlite3_busy_timeout (e.g. with `sqlite3
-cmd '.timeout 12' ...` [for 2 minutes timeout]). By default, it is 0,
so even if database is only shortly locked, it will still fail, even though
waiting a bit would help.

2a) If you can express complete your transaction in pure SQL, then you
should wrap whole transaction in 'begin immediate; ... ; commit;' in *single*
sqlite3 invocation:

   sqlite3 -cmd '.timeout 12' -bail "$DATABASE" "begin immediate;
   insert [];
   update [...];
   delete [...];
   commit;"
   errorCode="${?}"
   ...

2b) If you cannot express your DB change as static SQL statement(s)
(e.g., if some statements use results obtained in previous SELECT
queries, so you cannot feed your whole SQL code into single sqlite3
invocation), you should use SQLite binding to some real program language
(perl, tcl, python,...) instead of sh and sqlite3.

> I saw that when it is locked I get back a 5. Is this always the case? In
> this way I can see the difference between a lock and another error.
>
> ?If this code is correct I rewrite it for crontab. ;-)?

[sqlite] Locked database

[Simon Slavin]

On 27 Dec 2015, at 5:11pm, Yuriy M. Kaminskiy  wrote:

> (I know, I'm a bit late for discussion, but...)

... but you know the language (script system) the OP was writing in and you 
understand the problems involved in scripting SQLite.  Great post.

Simon.

[sqlite] Locked database

[Cecil Westerhof]

2015-12-14 15:14 GMT+01:00 Clemens Ladisch :

> Cecil Westerhof wrote:
> > sqlite3 "${DATABASE}" "begin immediate" 2>/dev/null
> > errorCode="${?}"
> > if [[ "${errorCode}" -eq 5 ]] ; then
> > printf "${DATABASE} is locked\n"
> >
> > I saw that when it is locked I get back a 5. Is this always the case?
>
> sqlite3 returns the SQLite error code, and 5 indeed is SQLITE_BUSY.
> (Non-SQL errors result in 1, which would be the same as SQLITE_ERROR.)
>

?And here are the other codes; ;-)
https://sqlite.org/c3ref/c_abort.html?


-- 
Cecil Westerhof

[sqlite] Locked database

[Clemens Ladisch]

Cecil Westerhof wrote:
> sqlite3 "${DATABASE}" "begin immediate" 2>/dev/null
> errorCode="${?}"
> if [[ "${errorCode}" -eq 5 ]] ; then
> printf "${DATABASE} is locked\n"
>
> I saw that when it is locked I get back a 5. Is this always the case?

sqlite3 returns the SQLite error code, and 5 indeed is SQLITE_BUSY.
(Non-SQL errors result in 1, which would be the same as SQLITE_ERROR.)


Regards,
Clemens

[sqlite] Locked database

[Cecil Westerhof]

2015-12-14 12:40 GMT+01:00 Clemens Ladisch :

> Cecil Westerhof wrote:
> > I have a crontab job which uses a SQLite database. Sometimes this
> database
> > is locked because I used SQLite DB Browser, but did not Write or Revert
> > Changes. It looks like that when a database is locked there is a file
> with
> > the database name with -journal appended to it. Can I count on this?
>
> The -journal file exists when there is some changed data that might
> need to be rolled back.  It is possible for the DB to be locked before
> some changed data is actually written.
>
> In WAL mode, there is no journal.  (And if your job is read only, it
> then would not be blocked by concurrent writes.)
>

?No, I need to write also.?




> > Then I could write a script to warn me about the lock.
>
> You could use SQLite to check whether the DB is locked:
>
>   if ! sqlite3 my.db "begin immediate"; then
> echo "is locked"
>   fi
>

?Thanks, I improved a little on it:
sqlite3 "${DATABASE}" "begin immediate" 2>/dev/null
errorCode="${?}"
if [[ "${errorCode}" -eq 5 ]] ; then
printf "${DATABASE} is locked\n"
elif [[ "${errorCode}" -ne 0 ]] ; then
printf "Error ${errorCode} while accessing ${DATABASE}\n"
else
printf "${DATABASE} is free\n"
fi

I saw that when it is locked I get back a 5. Is this always the case? In
this way I can see the difference between a lock and another error.

?If this code is correct I rewrite it for crontab. ;-)?

-- 
Cecil Westerhof

[sqlite] Locked database

[Clemens Ladisch]

Cecil Westerhof wrote:
> I have a crontab job which uses a SQLite database. Sometimes this database
> is locked because I used SQLite DB Browser, but did not Write or Revert
> Changes. It looks like that when a database is locked there is a file with
> the database name with -journal appended to it. Can I count on this?

The -journal file exists when there is some changed data that might
need to be rolled back.  It is possible for the DB to be locked before
some changed data is actually written.

In WAL mode, there is no journal.  (And if your job is read only, it
then would not be blocked by concurrent writes.)

> Then I could write a script to warn me about the lock.

You could use SQLite to check whether the DB is locked:

  if ! sqlite3 my.db "begin immediate"; then
echo "is locked"
  fi


Regards,
Clemens

[sqlite] Locked database

[Cecil Westerhof]

I have a crontab job which uses a SQLite database. Sometimes this database
is locked because I used SQLite DB Browser, but did not Write or Revert
Changes. It looks like that when a database is locked there is a file with
the database name with -journal appended to it. Can I count on this? Then I
could write a script to warn me about the lock.

-- 
Cecil Westerhof

Re: [sqlite] locked database?

[Nelson, Erik - 2]

Richard Hipp wrote:
> The first thing I would do is use the sqlite3_next_stmt() interface (
> http://www.sqlite.org/c3ref/next_stmt.html) to double-check that there
> were no unreset and unfinalized prepared statements.
> 

That's perfect, exactly what I needed to find the offending statement!  Thanks 
much.

Erik

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] locked database?

[Richard Hipp]

On Fri, Dec 13, 2013 at 1:18 PM, Nelson, Erik - 2 <
erik.l.nel...@bankofamerica.com> wrote:

> In my app that embeds sqlite version 3.7.14, I'm running into a database
> locking problem that happens when detaching a database.  If I execute
>
> attach database 'file:dbtest.db?mode=ro' as prism
>
> the attachment happens okay, but when I execute
>
> detach database prism
>
> I get the error 'database prism is locked'
>
> I assumed that this is a duplicate of the question at
>
> http://goo.gl/k3raLi
>
> http://stackoverflow.com/questions/15531498/sqlite-why-cant-i-detach-a-database-when-the-main-db-has-an-open-statement
>
> but I'm having trouble finding the statement in question... I'm tracking
> all my calls to sqlite3_prepare_v2() and sqlite3_finalize() and it seems
> like the all prepared statements are being finalized.  I'm not using any
> explicit transactions or sqlite3_blobs, and the app is single threaded.
>
> I looked through the transaction page at
> http://www.sqlite.org/lang_transaction.html and didn't see anything
> obvious that I'm tripping on.
>
> Is there some way to find out what is causing the lock?  I've looked
> through the C interface documentation and didn't notice anything promising.
>  If I could find the statement that's causing the lock that would be most
> helpful, if that's the problem.  Or maybe there's something else it could
> be?  Any suggestions would be welcome.
>


The first thing I would do is use the sqlite3_next_stmt() interface (
http://www.sqlite.org/c3ref/next_stmt.html) to double-check that there were
no unreset and unfinalized prepared statements.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] locked database?

[Nelson, Erik - 2]

In my app that embeds sqlite version 3.7.14, I'm running into a database 
locking problem that happens when detaching a database.  If I execute

attach database 'file:dbtest.db?mode=ro' as prism

the attachment happens okay, but when I execute

detach database prism

I get the error 'database prism is locked'

I assumed that this is a duplicate of the question at

http://goo.gl/k3raLi
http://stackoverflow.com/questions/15531498/sqlite-why-cant-i-detach-a-database-when-the-main-db-has-an-open-statement

but I'm having trouble finding the statement in question... I'm tracking all my 
calls to sqlite3_prepare_v2() and sqlite3_finalize() and it seems like the all 
prepared statements are being finalized.  I'm not using any explicit 
transactions or sqlite3_blobs, and the app is single threaded.

I looked through the transaction page at 
http://www.sqlite.org/lang_transaction.html and didn't see anything obvious 
that I'm tripping on.

Is there some way to find out what is causing the lock?  I've looked through 
the C interface documentation and didn't notice anything promising.  If I could 
find the statement that's causing the lock that would be most helpful, if 
that's the problem.  Or maybe there's something else it could be?  Any 
suggestions would be welcome.

Thanks

Erik

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] locked database returning SQLITE_IOERR, not SQLITE_BUSY

[Greg Janee]

For the record, this problem went away when I upgraded to the latest & greatest 
(3.7.15.2).

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Dan Kennedy [danielk1...@gmail.com]
Sent: Tuesday, February 26, 2013 10:32 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] locked database returning SQLITE_IOERR, not SQLITE_BUSY

On 02/27/2013 12:49 AM, Greg Janée wrote:
> I've instrumented the SQLite source and have found that the error is
> occurring at the fcntl call near the end of function unixLock (in SQLite
> version 3.7.0.1, this is line 23592 of sqlite3.c).  The relevant code
> snippet is below.  fnctl is returning -1, and errno=2 (ENOENT).  From my
> reading of the fcntl man page, it wouldn't seem to be possible for fcntl
> to even return ENOENT.
>
> SQLite is being used from a multi-threaded application in my case, and I
> don't know if it's a possibility that some other thread is overwriting
> errno.  But then, if that's even a possibility, wouldn't that seem to
> preclude using SQLite in a multithreaded application at all?

I think errno is thread specific on any unix that isn't ancient.
On Solaris you have to get the compiler flags right - "-D_REENTRANT"
or something. Maybe "-mt" too.

I thought you might have been reading errno fter the sqlite3_step()
call returned.

It is weird that it is setting errno to ENOENT...

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] locked database returning SQLITE_IOERR, not SQLITE_BUSY

[Greg Janée]

For the record, this problem went away when I upgraded to the latest &  
greatest (3.7.15.2).


On Feb 27, 2013, at 8:57 AM, Greg Janee wrote:




From: sqlite-users-boun...@sqlite.org [sqlite-users- 
boun...@sqlite.org] on behalf of Dan Kennedy [danielk1...@gmail.com]

Sent: Tuesday, February 26, 2013 10:32 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] locked database returning SQLITE_IOERR, not  
SQLITE_BUSY


On 02/27/2013 12:49 AM, Greg Janée wrote:

I've instrumented the SQLite source and have found that the error is
occurring at the fcntl call near the end of function unixLock (in  
SQLite

version 3.7.0.1, this is line 23592 of sqlite3.c).  The relevant code
snippet is below.  fnctl is returning -1, and errno=2 (ENOENT).   
From my
reading of the fcntl man page, it wouldn't seem to be possible for  
fcntl

to even return ENOENT.

SQLite is being used from a multi-threaded application in my case,  
and I
don't know if it's a possibility that some other thread is  
overwriting

errno.  But then, if that's even a possibility, wouldn't that seem to
preclude using SQLite in a multithreaded application at all?


I think errno is thread specific on any unix that isn't ancient.
On Solaris you have to get the compiler flags right - "-D_REENTRANT"
or something. Maybe "-mt" too.

I thought you might have been reading errno fter the sqlite3_step()
call returned.

It is weird that it is setting errno to ENOENT...

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] locked database returning SQLITE_IOERR, not SQLITE_BUSY

[Dan Kennedy]

On 02/27/2013 12:49 AM, Greg Janée wrote:

I've instrumented the SQLite source and have found that the error is
occurring at the fcntl call near the end of function unixLock (in SQLite
version 3.7.0.1, this is line 23592 of sqlite3.c).  The relevant code
snippet is below.  fnctl is returning -1, and errno=2 (ENOENT).  From my
reading of the fcntl man page, it wouldn't seem to be possible for fcntl
to even return ENOENT.

SQLite is being used from a multi-threaded application in my case, and I
don't know if it's a possibility that some other thread is overwriting
errno.  But then, if that's even a possibility, wouldn't that seem to
preclude using SQLite in a multithreaded application at all?


I think errno is thread specific on any unix that isn't ancient.
On Solaris you have to get the compiler flags right - "-D_REENTRANT"
or something. Maybe "-mt" too.

I thought you might have been reading errno fter the sqlite3_step()
call returned.

It is weird that it is setting errno to ENOENT...

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] locked database returning SQLITE_IOERR, not SQLITE_BUSY

[Michael Black]

This was covered a few days ago...the error handling is not thread safe
apparently(why not???) so you need to wrap the call and error check with a
mutex.

  int rc=SQLITE_OK;
  char *errmsg=NULL;
  sqlite3_mutex_enter(sqlite_db_mutex(db));
  rc=sqlite3_blobopen(.);
  if(rc!=SQLITE_OK) {
 rc=sqlite3_extended_errcode(db);
 errmsg=strdup(sqlite3_errmsg(db);
  }
  sqlite3_mutex_leave(sqlite_db_mutex(db));
  // now you can safely use rc and errmsg

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Greg Janée
Sent: Tuesday, February 26, 2013 11:50 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] locked database returning SQLITE_IOERR, not
SQLITE_BUSY

I've instrumented the SQLite source and have found that the error is  
occurring at the fcntl call near the end of function unixLock (in  
SQLite version 3.7.0.1, this is line 23592 of sqlite3.c).  The  
relevant code snippet is below.  fnctl is returning -1, and errno=2  
(ENOENT).  From my reading of the fcntl man page, it wouldn't seem to  
be possible for fcntl to even return ENOENT.

SQLite is being used from a multi-threaded application in my case, and  
I don't know if it's a possibility that some other thread is  
overwriting errno.  But then, if that's even a possibility, wouldn't  
that seem to preclude using SQLite in a multithreaded application at  
all?

   }else{
 /* The request was for a RESERVED or EXCLUSIVE lock.  It is
 ** assumed that there is a SHARED or greater lock on the file
 ** already.
 */
 assert( 0!=pFile->eFileLock );
 lock.l_type = F_WRLCK;
 switch( eFileLock ){
   case RESERVED_LOCK:
 lock.l_start = RESERVED_BYTE;
 break;
   case EXCLUSIVE_LOCK:
 lock.l_start = SHARED_FIRST;
 lock.l_len = SHARED_SIZE;
 break;
   default:
 assert(0);
 }
 s = fcntl(pFile->h, F_SETLK, );
 if( s==(-1) ){
   tErrno = errno;
   rc = sqliteErrorFromPosixError(tErrno, SQLITE_IOERR_LOCK);
   if( IS_LOCK_ERROR(rc) ){
 pFile->lastErrno = tErrno;
   }
 }
   }

On Feb 26, 2013, at 9:13 AM, Dan Kennedy wrote:

> On 02/27/2013 12:00 AM, Greg Janée wrote:
>> errno=2 (ENOENT)
>> What could not be existing?
>
> Strange. Could the value of errno have been clobbered before you
> read it?
>
> What can you see if you run the app under "truss -tfcntl"?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] locked database returning SQLITE_IOERR, not SQLITE_BUSY

[Simon Slavin]

On 26 Feb 2013, at 5:49pm, Greg Janée  wrote:

> SQLite is being used from a multi-threaded application in my case, and I 
> don't know if it's a possibility that some other thread is overwriting errno.

Yes it is.  Change your threading mode and see whether you get a different 
error, or if the error happens more or less often.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] locked database returning SQLITE_IOERR, not SQLITE_BUSY

[Dan Kennedy]

On 02/27/2013 12:00 AM, Greg Janée wrote:

errno=2 (ENOENT)
What could not be existing?


Strange. Could the value of errno have been clobbered before you
read it?

What can you see if you run the app under "truss -tfcntl"?

Dan.




On Feb 26, 2013, at 4:01 AM, Dan Kennedy wrote:


On 02/26/2013 05:22 AM, Greg Janée wrote:

I'm accessing an SQLite database from two processes simultaneously.  If
there's contention, one process will receive an SQLITE_BUSY; that's
fine.  However, occasionally a process will receive an SQLITE_IOERR with
the extended result code SQLITE_IOERR_LOCK.  This seems to occur if the
other process is in the middle of committing (at least, that's the only
time I've been able to observe it).  Is this reasonable behavior?  I was
expecting to get SQLITE_BUSY and nothing else.  (SQLite 3.7.0.1 on
Solaris 10; database on local filesystem.)

Here's a log showing the serial actions of two processes, 9157 and 9096:

9157: opening cursor
9157: got cursor
9157: issuing begin immediate
9157: begun
9157: issuing delete
9157: deleted
9157: issuing commit
9096: opening cursor
9096: got cursor
9096: issuing begin immediate
9096: exception: IOError: disk I/O error, errcode=10, extended=3850
9096: closing cursor
9096: closed
9157: committed
9157: closing cursor
9157: closed


What is errno set to after the error occurs?


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] locked database returning SQLITE_IOERR, not SQLITE_BUSY

[Greg Janée]

errno=2 (ENOENT)
What could not be existing?

On Feb 26, 2013, at 4:01 AM, Dan Kennedy wrote:


On 02/26/2013 05:22 AM, Greg Janée wrote:
I'm accessing an SQLite database from two processes  
simultaneously.  If

there's contention, one process will receive an SQLITE_BUSY; that's
fine.  However, occasionally a process will receive an SQLITE_IOERR  
with
the extended result code SQLITE_IOERR_LOCK.  This seems to occur if  
the
other process is in the middle of committing (at least, that's the  
only
time I've been able to observe it).  Is this reasonable behavior?   
I was

expecting to get SQLITE_BUSY and nothing else.  (SQLite 3.7.0.1 on
Solaris 10; database on local filesystem.)

Here's a log showing the serial actions of two processes, 9157 and  
9096:


9157: opening cursor
9157: got cursor
9157: issuing begin immediate
9157: begun
9157: issuing delete
9157: deleted
9157: issuing commit
9096: opening cursor
9096: got cursor
9096: issuing begin immediate
9096: exception: IOError: disk I/O error, errcode=10, extended=3850
9096: closing cursor
9096: closed
9157: committed
9157: closing cursor
9157: closed


What is errno set to after the error occurs?


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] locked database returning SQLITE_IOERR, not SQLITE_BUSY

[Dan Kennedy]

On 02/26/2013 05:22 AM, Greg Janée wrote:

I'm accessing an SQLite database from two processes simultaneously.  If
there's contention, one process will receive an SQLITE_BUSY; that's
fine.  However, occasionally a process will receive an SQLITE_IOERR with
the extended result code SQLITE_IOERR_LOCK.  This seems to occur if the
other process is in the middle of committing (at least, that's the only
time I've been able to observe it).  Is this reasonable behavior?  I was
expecting to get SQLITE_BUSY and nothing else.  (SQLite 3.7.0.1 on
Solaris 10; database on local filesystem.)

Here's a log showing the serial actions of two processes, 9157 and 9096:

9157: opening cursor
9157: got cursor
9157: issuing begin immediate
9157: begun
9157: issuing delete
9157: deleted
9157: issuing commit
9096: opening cursor
9096: got cursor
9096: issuing begin immediate
9096: exception: IOError: disk I/O error, errcode=10, extended=3850
9096: closing cursor
9096: closed
9157: committed
9157: closing cursor
9157: closed


What is errno set to after the error occurs?

Dan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] locked database returning SQLITE_IOERR, not SQLITE_BUSY

[Greg Janée]

Have you set a SQLite timeout of a few seconds ?  See here:




I was expecting to get SQLITE_BUSY and nothing else.


If the _BUSY state lasts for longer than the timeout you've set,  
then you'll get _IOERR.  Setting a long timeout gives the software  
longer to deal with the busy condition quietly before concluding  
that something has locked up the databases longer than reasonable.


This explanation doesn't fit for a number of reasons.

- I've observed this problem using both the native Python sqlite3  
library and apsw, and the native library sets a default timeout of 5s,  
and there's nothing that takes remotely that long in my example.


- The documentation referenced above says that SQLITE_BUSY will be  
returned if the timeout is exceeded, and I do in fact occasionally  
receive such return codes (when using apsw, which has no default  
timeout).  The condition under which SQLITE_IOERR_BLOCKED might be  
returned (I'm referring to  here) would seem applicable only to the process trying to commit,  
but as my log showed, it's the process trying to begin a transaction  
that is receiving the error code.


- In any case, I'm receiving SQLITE_IOERR_LOCK, not  
SQLITE_IOERR_BLOCKED.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] locked database returning SQLITE_IOERR, not SQLITE_BUSY

[Simon Slavin]

On 25 Feb 2013, at 10:22pm, Greg Janée  wrote:

> I'm accessing an SQLite database from two processes simultaneously.  If 
> there's contention, one process will receive an SQLITE_BUSY; that's fine.  
> However, occasionally a process will receive an SQLITE_IOERR with the 
> extended result code SQLITE_IOERR_LOCK.

Have you set a SQLite timeout of a few seconds ?  See here:



> I was expecting to get SQLITE_BUSY and nothing else.

If the _BUSY state lasts for longer than the timeout you've set, then you'll 
get _IOERR.  Setting a long timeout gives the software longer to deal with the 
busy condition quietly before concluding that something has locked up the 
databases longer than reasonable.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] locked database returning SQLITE_IOERR, not SQLITE_BUSY

[Greg Janée]

I'm accessing an SQLite database from two processes simultaneously.   
If there's contention, one process will receive an SQLITE_BUSY; that's  
fine.  However, occasionally a process will receive an SQLITE_IOERR  
with the extended result code SQLITE_IOERR_LOCK.  This seems to occur  
if the other process is in the middle of committing (at least, that's  
the only time I've been able to observe it).  Is this reasonable  
behavior?  I was expecting to get SQLITE_BUSY and nothing else.   
(SQLite 3.7.0.1 on Solaris 10; database on local filesystem.)


Here's a log showing the serial actions of two processes, 9157 and 9096:

9157: opening cursor
9157: got cursor
9157: issuing begin immediate
9157: begun
9157: issuing delete
9157: deleted
9157: issuing commit
9096: opening cursor
9096: got cursor
9096: issuing begin immediate
9096: exception: IOError: disk I/O error, errcode=10, extended=3850
9096: closing cursor
9096: closed
9157: committed
9157: closing cursor
9157: closed

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Locked Database Problem

[Martin Sigwald]

Hi,
I'm running two different processes, both access the same database at given
times, without synchronization. In order to handle a simultaneous access, I
was testing for the BUSY condition. However, one of the processes is
receiving a LOCKED status form SQLITE, resulting in a eternal deadlock.
According with this http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked ,
that kind of error has nothing to do with concurrent process accessing the
same DB, but with a single process trying to do tow simultaneous and
contradictory actions, without finalizing the previous statement. The thing
is I am calling sqlite3_finalize between calls, so I'm really at a a loss
here. Any ideas?

Pseudocode:

Process 1:  read_db() Performs a select operation on table_1.
 do_stuff() Doesnt touch the db.
 write_db() Update operation on table_1
Process 2:
 do_stuff();
 if(certain condition has changed)
   write_db(); Update operation on table_1 ---> This one
gets LOCKED

Best regards,
Martin Sigwald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locked database problem

[Igor Tandetnik]

"Lothar Behrens"
<[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> I have s question about avoiding locked database errors by closing a
> connection in these circumstances.

Opening a connection doesn't acquire any locks, and closing doesn't 
release any. Starting and finishing transactions is what acquires and 
releases locks. Your problem must lie elsewhere.

> Is it possible, that there are some open connections around, opened
> with
>
>   char* databaseNameBuffer = "mydb";
>   int nReturn = sqlite3_open(databaseNameBuffer, _pDatabase1);
>   nReturn = sqlite3_open(databaseNameBuffer, _pDatabase2);
>
> Doing some reading on m_pDatabase1
> Doing some changing on m_pDatabase2

How exactly are you doing this "reading and changing"? Do you reset or 
finalize all statement handles you've prepared? Do you commit or roll 
back all transactions you've started?

> to change other data while m_pDatabase2 has locked the database

I don't see how you are going to "change other data" while the database 
is locked. The point of locking the database is precisely so that you 
_can't_ change it on another connection.

> Or will m_pDatabase2 be invalid after  m_pDatabase1 has been closed ?

Definitely not.

> I have trouble with closing the database and still locking problems.
> How could I see, if I have such dangling open
> database handle m_pDatabase2 ?

You are barking up the wrong tree. Watch for unfinalized statement 
handles instead.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Locked database problem

[Lothar Behrens]

Hi,

I have s question about avoiding locked database errors by closing a 
connection in these circumstances.
In my test application this works and I also can see that the related 
data has been inserted.

But in my main application I also close the connection without success.

Is it possible, that there are some open connections around, opened with


   char* databaseNameBuffer = "mydb";
   int nReturn = sqlite3_open(databaseNameBuffer, _pDatabase1);
   nReturn = sqlite3_open(databaseNameBuffer, _pDatabase2);

Doing some reading on m_pDatabase1
Doing some changing on m_pDatabase2

   nReturn = sqlite3_close(m_pDatabase1);

and then reopen

   nReturn = sqlite3_open(databaseNameBuffer, _pDatabase1);

to change other data while m_pDatabase2 has locked the database to be 
the cause of locked database ?

Or will m_pDatabase2 be invalid after  m_pDatabase1 has been closed ?

I have trouble with closing the database and still locking problems. 
How could I see, if I have such dangling open
database handle m_pDatabase2 ?

Any help ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locked database

[Guy Hachlili]

Hello.

At 13:37 2/25/2007 +, you wrote:

Came across a situation where it was impossible to delete or rename a SQLite
db file even after the application (Excel) that had locked the database was
closed. It wasn't me, so I don't have very exact information, but there was
a statement to create a table and to insert data. For some reason this
couldn't be completed and there was a db file and the corresponding journal
file.
I thought that it should be that closing the app that initiated the db
connection should always release the handle to the db and allow deletes of
renames of the file.

If this is not so then should I maybe write the db handle to a safe place,
like an .ini file, so I could use it later to close the db.
Also would there be a way to release this db lock without a reboot of the
PC?


I have had a lot of experience with Excel crashing the UI and leaving some 
Excel application running in the background.
Open the task manager (Ctrl+Shift+Esc) and check to see if you have any 
Excel leftovers in the Processes tab (NOT in the Applications tab!). If you 
do, just select them and use the End Process button to close them. The DB 
file should be freed.



Guy



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Locked database

[RB Smissaert]

Came across a situation where it was impossible to delete or rename a SQLite
db file even after the application (Excel) that had locked the database was
closed. It wasn't me, so I don't have very exact information, but there was
a statement to create a table and to insert data. For some reason this
couldn't be completed and there was a db file and the corresponding journal
file.
I thought that it should be that closing the app that initiated the db
connection should always release the handle to the db and allow deletes of
renames of the file.

If this is not so then should I maybe write the db handle to a safe place,
like an .ini file, so I could use it later to close the db.
Also would there be a way to release this db lock without a reboot of the
PC?

Thanks for any advice.

RBS 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-