Re: [sqlite] Database locked after crash

[Jaap Krabbendam]

I do have some code here. It is clear that the problem is related to using
threads. I did not see the problem having just the main program. Note that it
is my objective to test a crash, hence the exit(0) from my_thread. The same
phenomenon is seen when replacing exit(0) by while(1) sleep(100) and then
hitting ^C

If you start the program with an argument, a new mydb file will be created
and the program will issue an exit(0). If you then start the program again
without any arguments,
my_thread will end up in while(1) sleep(100). You can also test with
sqlite3 client to see that the db is locked.


Jaap Krabbendam.


>Can you post code?
>
>--- Jaap Krabbendam <[EMAIL PROTECTED]> wrote:
>
>> 
>> Hi,
>> 
>> I have been simulating a crash during a transaction. After BEGIN, at some 
point
>> I do exit(-1) instead of COMMIT or ROLLBACK in order to simulate a crash.
>> 
>> After that, I can see that a -journal file is present. If I restart my 
>> executable, it seems that the changes of the transaction are made undone 
>> (which is as expected). The journal file however is not removed. 
>> Furthermore, if I try to do the same operation again (BEGIN + some changes),
>> I get an SQL_BUSY error code on the first record change (UPDATE/SET). 
>> 
>> I have the feeling that the OS still has a lock on the database. Any ideas 
>> on 
>> how to prevent this or on how to recover from this situation?
>> 
>> I am using the following setup:
>> -sqlite-3.2.1
>> -linux/i686/2.6.9-1.667smp
>> -application using posix threads. Only one thread is accessing the database.
>> 
>> Thanks,
>> J.J. Krabbendam
>> 
>> 
>
>
>
>               
>__________________________________ 
>Do you Yahoo!? 
>Yahoo! Small Business - Try our new resources site!
>http://smallbusiness.yahoo.com/resources/ 

#include <sqlite3.h>
#include <unistd.h>
#include <stdlib.h>
#include <pthread.h>
#include <stdio.h>

void *my_thread(void *);

char *file = "mydb";
bool create; 

int main(int argc, char *argv[])
{
   int ret;

   create = argc > 1;

   pthread_t thread;
   ret = pthread_create(&thread, 0, my_thread, 0);
   if (ret != 0)
      perror("pthread_create");
   
   while (1)
      sleep(100);

}

void *my_thread(void *)
{
   int ret = 0;
   sqlite3 *dbref;

   if (create)
      unlink(file); 

   ret = sqlite3_open(file, &dbref);
   if (ret != 0)
      goto exit;

   if (create) 
   {
      ret = sqlite3_exec(dbref, "CREATE TABLE tab (col1 INTEGER)", 0,0,0);
      if (ret != 0)
         goto exit;
      ret = sqlite3_exec(dbref, "INSERT INTO tab (col1) VALUES (3)", 0,0,0);
      if (ret != 0)
         goto exit;
   }

   ret = sqlite3_exec(dbref, "BEGIN", 0,0,0);
   if (ret != 0)
      goto exit;
   ret = sqlite3_exec(dbref, "UPDATE tab SET col1=6", 0,0,0);
   if (ret != 0)
      goto exit;

   printf("Return value1: %d\n",ret);
   while (1)
      sleep(100);
   exit(0);

exit:
   printf("Return value2: %d\n",ret);
   sqlite3_close(dbref);


   while (1)
      sleep(100);
}