database transactions and threads

Question

I have functions lock(), unlock(), query1(), query2() and query3(). The query functions just run some query on a database and can be considered r/w access to it. They do not lock. My system is multithreaded.

The functionality I want is: If lock() is called from thread p1, only queries from thread p1 will run and queries from all other threads wait for unlock. How do I do this?

I'm using pthreads from C. To do this, the thread must know that it holds the lock. But pthreads doesn't have such a function.

Is the design wrong??

EDIT:

Function1(){
lock();
query1();
query2();
doQuery3();
unlock();
}

doQuery3(){
lock();
query3();
unlock();
}

The behaviour I want with lock() is that if the thread already is holding that lock, it should not wait on the lock. It should just run. The thing is my lock() function actually starts a transaction. I want to run a bunch of stuff in the transaction. And unlock() ends the transaction. I want to be able to chain queries. One workaround is to call query3() in Function1() instead of doQuery3(); That would mean for every function there are two versions, one with the locking and one vanilla query()

Again, these lock and unlock functions may or maynot be mutex locks. I tried implementing it with pthread mutex, but couldn't. Because a pthread_mutex_lock on the same thread blocks! Any cool tricks??

Answer 1

Yes, your design is wrong.

The locking should be taking place in the Database.

Answer 2

I'm not sure I've understood your question/design, however ...

The functionality I want is: If lock() is called from thread p1, only queries from thread p1 will run and queries from all other threads wait for unlock. How do I do this?

If the unit of work called by the p1 thread is like this ...

void doQuery1()
{
  //get the lock
  //if someone else has the lock, block until it's released
  lock();

  //now we have the lock
  //do query
  query1();

  //our work is done
  //release the lock, let another thread run
  unlock();
}

... and the work called by the others threads are similar ...

void doQuery2()
{
  lock();
  query2();
  unlock();
}

void doQuery3()
{
  lock();
  query3();
  unlock();
}

... then if p1 has the lock, then because other threads also always call the lock function before they try to query, and because the lock function waits for unlock, then I think that's the answer to your question: the result is that queries from other threads will wait for unlock (i.e.because all threads always call lock, and because lock waits for unlock).

Answer 3

Well, it seems you are lookng for implementations of lock and unlock? Here is an overview of how to achieve a critical section. but you can use this to implement your functions:

pthread_mutex_t mutex;
pthread_mutex_init(&mutex, NULL);
pthread_mutex_lock(&mutex);
queryN(); // critical section
pthread_mutex_unlock(&mutex);

If the database is only accessed from your single program, it will work for you. I think if your queries are doing r/w, your database will be doing locking of tables/rows as appropriate, in any case.

If all your queries are surrounded by lock and unlock, you will achieve what you desire.

Answer 4

• If your database is handling locks ex. SQL database, then the transaction is starting explicitly or at the first SQL-statement and ended at commit work. You have only to synchronize (ex. using a queue) your program if you are using shared data.

• If your database is not handling locks, then locks must be used like:

  START_TRANSACTION = pthread_mutex_lock

  END_TRANSACTION = pthread_mutex_unlock

but there is no rollback handling and no protecting against inconsistency.