Problem with locked database

[shinsengumi]

Hi everyone. I'm using sqlite3 in Linux and I currently have a problem. I have a database and 2 tables in it: db1 and db2. db1 has 3 columns: empID, empName, empAddr. db2 has just 1 column: empID and its value is set to 0 by default.

I created a C program that continuously polls the 2nd database db2 for any changes in the value of empID. Depending on the new value of empID in db2, the program fetches the corresponding info from db1. For example, if the value of empID in db2 is changed to 1, the program looks for the information (empName and empAddr) from db1 that corresponds to an empID equal to 1. However when I run 2 terminals: 1 for manually changing the value of empID in db2 and another 1 for running the C program, I encountered the problem of a locked database. While the program is running and accessing the database, I want to change the value of empID in db2 but I understand that since my C program is accessing it, I cannot update the value of empID (unless I close the terminal running the program). So my question is, is there a way to update/change the value of empID in db2 while I'm accessing db2 through my program? Answers will be greatly appreciated. Thank you.

[smantscheff]

This problem does not occur when using other interfaces, e.g. the MySQL command line or Navicat or PHP connections. Therefore I assume that you have some locking logic in your C program which is responsible, not MySQL. Do you use 3rd party libraries which might contain the locking? What is the MySQL error message? Can you reproduce it from the MySQL command line?

[shinsengumi]

This problem does not occur when using other interfaces, e.g. the MySQL command line or Navicat or PHP connections. Therefore I assume that you have some locking logic in your C program which is responsible, not MySQL. Do you use 3rd party libraries which might contain the locking? What is the MySQL error message? Can you reproduce it from the MySQL command line?

The code below is my actual program:

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

//gcc -o sql1 sql1.c -lsqlite3

struct data_t {
	int prev_loc, prev_pan, prev_tilt, cur_loc, cur_pan, cur_tilt;
};

struct data_t data;

static int callback1(void *not_used, int argc, char **argv, char **colname)
{
	int cur_pos;
	cur_pos = atoi((const char*)argv[0]); //argv[0] is the ID to be polled if it changed
	data.cur_loc = cur_pos;
	return 0;
}

static int callback2(void *not_used, int argc, char **argv, char **colname)
{
	int x, y;
	x = atoi((const char*)argv[0]); //gets info from the 2nd column (1st column is the ID)
	y = atoi((const char*)argv[1]); //gets info from the 3rd column
	data.cur_pan = x;
	data.cur_tilt = y;
	data.prev_loc = data.cur_loc;
	data.prev_pan = data.cur_pan;
	data.prev_tilt = data.cur_tilt;
	return 0;
}

int main (int argc, char **argv)
{
	data.prev_loc = 0; //initialization
	data.prev_pan = 0;
	data.prev_tilt = 0;
	data.cur_loc = 0;
	data.cur_pan = 0;
	data.cur_tilt = 0;

	sqlite3 *db = 0;
	char *err = 0;
	int input;

	int r = sqlite3_open("ex1.db",&db); //ex1.db is the database that contains the 2 tables
	if(r){
		fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
		sqlite3_close(db);
		exit(1);
	}

	while (1) {
		char cmd1[1024] = "select * from try2"; // try2 is the 2nd table that contains ID
		r = sqlite3_exec(db, cmd1, callback1, 0, &err); //poll table try2 if its content has changed
		if( r!=SQLITE_OK ){
			fprintf(stderr, "SQL error: %s\n", err);
		}
	
		if (data.cur_loc != data.prev_loc) { //if content of try2 has changed, get contents of try1 based from
			char cmd2[1024] = "";        //try2's new value
			snprintf(cmd2,sizeof(cmd2),"select val_1, val_2 from try1 where ID = %d",data.cur_loc);
			r = sqlite3_exec(db, cmd2, callback2, 0, &err); //try1 is the table that has 3 columns
			if( r!=SQLITE_OK ){
				fprintf(stderr, "SQL error: %s\n", err);
			}
			
			printf("cur_pan = %d for ID = %d\n", data.cur_pan, data.cur_loc);
			printf("cur_tilt = %d for ID = %d\n", data.cur_tilt, data.cur_loc);
		}
	}
	sqlite3_close(db);
}

I run the program in one terminal, and open another terminal to update the 2nd table try2 by typing this command:
sqlite> update try2 set ID = 1 where ID = 0;
Error: database is locked

As you can see, the error says that the database is locked probably because my program is "polling" it. Is there a way to continuously poll the database and update it at the same time?

[smantscheff]

I don't know the sqlite3 library. Is there some implicit locking in their functions? Also I would try to include a sleep() into the polling loop - maybe the database is just too busy with the polling to respond. What is the output of "show status" in your terminal window?

[shinsengumi]

I don't know the sqlite3 library. Is there some implicit locking in their functions? Also I would try to include a sleep() into the polling loop - maybe the database is just too busy with the polling to respond. What is the output of "show status" in your terminal window?

I can't run "show status" right now because it's not currently installed in my PC. I can't download it either because my internet connection's proxy requires some authentications (I tried downloading it but errors occurred and said "Proxy Authentication Required"). I'll try to burrow my friend's internet connection later. I didn't put sleep() because I need the program's execution in "real-time" such that when the table's content is changed, the program reacts right away.

Anyway, do you think PRAGMA will work? I read somewhere that setting my program to "PRAGMA read_uncommitted;" might solve the problem. But I don't know how to include this command in the program.

[shinsengumi]

I don't know the sqlite3 library. Is there some implicit locking in their functions? Also I would try to include a sleep() into the polling loop - maybe the database is just too busy with the polling to respond. What is the output of "show status" in your terminal window?

By the way, the sqlite3 library has some implicit locking mechanism. When a database is opened/when a database connection is created, the database will be locked and will only be unlocked after a read/write execution. In my case, I think the "polling" is similar to "reading" the database.

[smantscheff]

I'm sorry, I know MySQL but not the sqlite3 library. Maybe you better find a forum which focuses on sqlite, not MySQL, since your problem is obviously not pertinent to SQL per se but to the implementation by sqlite.
Does sqlite have triggers? Maybe you could install a trigger which notifies some agent of the change instead of polling the database incessantly.

[shinsengumi]

I'm sorry, I know MySQL but not the sqlite3 library. Maybe you better find a forum which focuses on sqlite, not MySQL, since your problem is obviously not pertinent to SQL per se but to the implementation by sqlite.
Does sqlite have triggers? Maybe you could install a trigger which notifies some agent of the change instead of polling the database incessantly.

Alright