I have a database table that logs the changes in other table. table structure is as following.
Log_Table(id, table_name, operation, flag)
values(1,Customer,1,1);
values(2,Customer,2,1);
values(3,Customer,1,1);
values(4,Customer,2,1);
values(5,Customer,1,1);
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
values(6,Customer,2,1);
values(7,Customer,1,1);
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.