Hi all..I am working on a small program with threads. In the middle of the program i want to obtain an exclusive lock on cell in table , so that no other thread should even be able to read the contents of that cell untill the lock is released.
Not just other threads i dont want to allow read/write operations on that cell from any other applications as well, if the database is shared across applications.
What i need exactly is some type of EXCLUSIVE LOCK on the database level.
Various databases support different set of tools to accomplish this. I don't know whether there are databases which would support locking individual columns, but I know for sure there are ones that don't, so this might not be feasible for you depending on the database. Generally the most compatible solution would be to lock an entire row, ideally using a SELECT ... FOR UPDATE commad. Look up the FOR UPDATE clause in the SQL reference of your database (and come back here if you encounter problems with it).
In your program you'll begin a transaction and issue the SELECT ... FOR UPDATE when entering the exclusive block, and you'll rollback or commit when you're exiting. This also means that you need to keep the connection open for the entire execution of the exclusive block. If this is not possible in your code, a more complicated design will have to be implemented in all of the applications that need to be synchronized this way.
A further consideration is that some databases, such as Oracle, don't allow locks that would block read operations. If blocking reads is essential to you, it must be addressed differently.