How can I synchronize database access between a write-thread and a read-thread?

Question

My program has two threads:

1. Main execution thread that handles user input and queues up database writes
2. A utility thread that wakes up every second and flushes the writes to the database

Inside the main thread, I occasionally need to make reads on the database. When this happens, performance is not important, but correctness is. (In a perfect world, I would be reading from a cache, not making a round-trip to the database - but let's put that aside for the sake of discussion.)

How do I make sure that the main thread sees a correct / quiescent database?

A standard mutex won't work, since I run the risk of having the main thread grab the mutex before the data gets flushed to the database. This would be a big race condition.

What I really want is some sort of mutex that lets the main thread of execution proceed only AFTER the mutex has been grabbed and released once. Does such a thing exist? What's the best way to solve this problem?

UPDATE: After doing some additional research, I might use Boost's Conditional Variable to address this problem. Either that, or just bite the bullet and cache my writes. Thanks for the feedback!

Answer 1

Several solutions are possible:

• Have each thread open its own d/b connection and use it exclusively
• Use a single connection, but have each thread own the mutex to access it.

I don't see a problem with the second choice. What's the harm of flushing when there's nothing to flush?

Answer 2

Your proposed solution surely still results in a race condition, since a "write" may come in at any time, even halfway through a new event being queued.

A solution you might try is atomic database state value: you do your processing, then compare to an atomic state value to ensure that you are reading from a database that is in the state that is the same as when began the read. If it's different, you start over. This may be subject to starvation, but that's a separate issue.

Every time you change the database, you must compare and swap the atomic state value you used, indicating a change.

Answer 3

If you don't have more than one main execution thread (ie, the only thread that will push writes onto the worker thread is the same thread that will be reading from the database), then you can probably just have a simple "pending writes" variable/function that you can check before sending a read, and spinlock or wait for a signal until the writes have been flushed. It sounds like you won't need to perform any locking or synchonization on the writes, if they can simply be queued up to be processed by the worker thread.

Basically, as long as you are guaranteed that in between the check for that 'pending writes' state and when you actually perform the read, there are no writes, then you don't need to do anything too fancy.