[sqlite] database and table lock

[Alex Katebi]

Hi,

I am getting a table lock for no apparent reason in my application.

To reproduce this I have written a test file that behaves the same way with
the difference that it reports database lock instead of table lock. My
application database is in memory the test uses a file database. I think it
is the same locking issue.

Attached is "test.c"
gcc -Wall test.c -lsqlite3 -o test
run two test at the same time you will see the problem.

Thanks,
-Alex

// gcc -Wall test.c -lsqlite3

#include <pthread.h>
#include <sqlite3.h>
#include <assert.h>
#include <stdio.h>
#include <sys/types.h>
#include <unistd.h>

sqlite3* gdb;

int ass(int a, int b)
{
  if(a != b)
    {
      printf("Expected %d Got %d\n", a, b);
      printf("%s\n", sqlite3_errmsg(gdb));
      return 0;
    }
  return 1;
}


#define MAX_LOOP 10000
#define MAX_THREAD 1

void xTrace(void* self, const char* zSql)
{
  printf(" %d %s\n", (int) self, zSql);
}

void init_trace(void* self)
{
  sqlite3_trace(gdb, xTrace, self);
}

void* test(void* val)
{
  int i, id = (int)val;
  for(i = 0 ; i < MAX_LOOP ; i++)
    {
      char* zSql;
      sqlite3_stmt* stmt;

      char* tbl = sqlite3_mprintf("tbl_%d_%d", getpid(), id);

      zSql = sqlite3_mprintf("create table %s(a)", tbl);
      assert(ass(SQLITE_OK, sqlite3_prepare_v2(gdb, zSql, -1, &stmt, 0)));
      assert(ass(SQLITE_DONE, sqlite3_step(stmt)));
      assert(ass(SQLITE_OK, sqlite3_finalize(stmt)));
      sqlite3_free(zSql);
      //printf("create stmt %p\n", stmt);

      zSql = sqlite3_mprintf("insert into %s values(77777)", tbl);
      assert(ass(SQLITE_OK, sqlite3_prepare_v2(gdb, zSql, -1, &stmt, 0)));
      assert(ass(SQLITE_DONE, sqlite3_step(stmt)));
      assert(ass(SQLITE_OK, sqlite3_finalize(stmt))); 
      sqlite3_free(zSql);
      //printf("insert stmt %p\n", stmt);

      zSql = sqlite3_mprintf("drop table %s", tbl);
      assert(ass(SQLITE_OK, sqlite3_prepare_v2(gdb, zSql, -1, &stmt, 0)));
      assert(ass(SQLITE_DONE, sqlite3_step(stmt)));
      assert(ass(SQLITE_OK, sqlite3_finalize(stmt))); 
      sqlite3_free(zSql);
      //printf("drop stmt %p\n", stmt);

      //zSql = sqlite3_mprintf("select tbl_name from sqlite_master where 
tbl_name=%Q", tbl);
      //assert(ass(SQLITE_OK, sqlite3_prepare_v2(gdb, zSql, -1, &stmt, 0)));
      //assert(ass(SQLITE_DONE, sqlite3_step(stmt)));
      //assert(ass(SQLITE_OK, sqlite3_finalize(stmt)));
      //sqlite3_free(zSql);
      //printf("select stmt %p\n", stmt);

      sqlite3_free(tbl);
    }
  return 0;
}

int main()
{
  char* file = "test.db";
  //char* file = "/dev/shm/test.db";
  assert(ass(SQLITE_OK, sqlite3_open(file, &gdb)));
  init_trace(0);

  int i;
  pthread_t threads[MAX_THREAD];
  for(i = 0 ; i < MAX_THREAD ; i++)
    {
      assert(pthread_create(&threads[i], 0, test, (void*) i) == 0);  
    }
  for(i = 0 ; i < MAX_THREAD ; i++)
    {
      assert(pthread_join(threads[i], 0) == 0);
    }

  sqlite3_stmt *pStmt;
  while( (pStmt = sqlite3_next_stmt(gdb, 0))!=0 )
    {
      static int count = 0;
      printf("%d: next %p\n", count++, pStmt);
      sqlite3_finalize(pStmt);
    }

  assert(ass(SQLITE_OK, sqlite3_close(gdb)));
  return 0;
}

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