Update a table after select in multi-threaded environment

Question

I am using a SQL table for state maintenance of my application.

Initially I was extracting new keys from my table using a select query and then updating there status as 'picked up'

I am now moving to a multi-threaded environment. the problem in multi threaded environment is that same key is extracted multiple times. System state become inconstant. I have tried using synchronized but its not work. I believe there might be a SQL only solution to my problem too, but attaching java code

Below is my code

sql ="select "+idField+",id from `tableName` where finish_time is NULL and status = 0 order by init_time limit 1"; Statement statement; statement = connection.createStatement(); synchronized (this) { ResultSet rs = statement.executeQuery(sql); if (rs.next()) { bId = rs.getString(1); rowId = rs.getString(2); } sql1 = "UPDATE `tableName` SET `pick_up_time`=Now(),`status`=1 WHERE `id`=" + rowId; executeQuery(sql1);

wero · Accepted Answer · 2016-02-16 11:26:26Z

1

In the UPDATE statement use the condition "WHERE id=" + rowId + " AND status = 0"

If the update count returned by Statement.executeUpdate is 0 then you know that another thread has picked that id at the same time and you can ignore it in the current thread.

edited Feb 16, 2016 at 11:26

answered Feb 16, 2016 at 10:04

wero

33.1k3 gold badges63 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Count Over a year ago

This solves the problem, but is there a sql specific solution too , I think that will have less overhead. for ex : returning id of the row that I updated.

wero Over a year ago

@Count what do mean by returning id of the row that I updated?

Count Over a year ago

I am not too good with SQL , but I meant " If I can select the id of the row that I updated using my update query "

wero Over a year ago

@Count unfortunately there is no way to execute an UPDATE and at the same time get the ids of the affected records

Collectives™ on Stack Overflow

Update a table after select in multi-threaded environment

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related