Multi-threading : Multiple Threads Interacting With Same Table

Interview question Say , we have a table with 2 million records in Employee table and we need to cut 10% salary(need to do some processing) of each employee and then save it back t

Solution 1:

One thing that you could do is to use a producer/consumer type model, where you have one thread working to feed the others the records to update. This way you would not have to worry as much about duplicate processing.

Solution 2:

The best approach given the question as stated is to use pure SQL, something like:

update employees set
salary = salary *.9

It is very hard to imagine needing to do something to employee data that SQL could not handle.

If by some quirk of bad design you really needed to do something to employee type data that SQL absolutely could not do, then you would open a cursor to the rowset and iterate through it, making the update synchronously so you only do one pass over the data.

In pseudo code:

cursor = forUpdate ("select for update * from employees")
while (cursor.next()) {
    cursor.salary = cursor.salary * .9
}

This is the simplest and likely fastest executing approach.

—-

Regarding logging

It’s only 2M rows, which is a “small” quantity, so most DB could handle it in a single transaction. However if not, add a where clause, eg where id between <start> and <end> to the query to chunk up the process into loggable amounts if using the shell script approach.

If using the code approach, most databases allow you to commit while holding the cursor open, so just commit every 10K rows or so.

Regarding locking

Similar aspects to logging. All rows in such a query are locked for the duration of the transaction. Given it would take that long to run, pick a quiet time to run. If it’s really a big deal, chunk up but realise that locking is unavoidable.

Solution 3:

I would load in this table, then add a column for the state. By default, you could set this column to "Not Processed". Once a thread starts processing this employee it would change the state to "Processing", then when finished it would finally switch it to "Processed".

Having 3 states like this would also allow you to use this as a Lock preventing the processing from happening twice.