Transaction problem

Question

I use Java and MySql as a database.

I am running multiple instances of the application. I select one record from the database and at the same time after receiving it, I update its status "in process" so that no other instances can access this record.

But what happens is that the instances work so fast that when one instance accesses one record, another instance also accesses the same record before the update is performed in "In Process" by the first instance. What can I do to ensure that the update also takes place before another instance can access it? I used conn.setTransactionIsolation(conn.TRANSACTION_READ_COMMITTED)in my code, but it also does not help.

Thanks in advance.

Answer 1

You need a type operator select for update.

http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

Answer 2

Well, I think the solution to your problem is a synchronization block. There are two ways to do this. Either write a synchronization block, or put this update database code in one method that is synchronized. Thus, if one thread makes changes, no other thread will gain access to it. For more information click here.