I have a database table that logs the changes in other table. table structure is as following.
Log_Table(id, table_name, operation, flag)
I update this table against a button on a web page. by doing following operations.
public List<Long> select_Changes()
1) select id from Log_Table where flag =1;
public void update_table(List<Long> ids)
2)update Log_Table set flag =0 where id in( ids)
Problem is that between first and second operation its up to the user to perform the operation. Meanwhile another user at same time does the same operations. now i want that for this user already selected rows for first user should not be selected by the second user. e.g second user when do the first operation should get
Please suggest what should i do? I need to lock the rows for any kind of operation after rows are get selected. I tried select for update clause but it did not solve the problem. Its in a web application.
You are correct that you need to setup a transaction. See the "Using Transactions to Preserve Data Integrity" section of Oracle's transaction page for how to use transactions with raw JDBC. From a code point of you, you'll need to set the transaction isolation level.