0

I'm trying to code a multi-threaded program using VB.NET. It's a simple program: go to a database, get a row, process some back-end, then update the row and set it as "processed = true". Because there is so much data, I'm planning to do a multi-threaded program for it.

SELECT... FOR UPDATE doesn't seem to work in a transaction for some odd reason, and so I've decided to pre-emptively mark the row as "being read = TRUE", then process it from there.

Is it possible to update the row, then retrieve the row ID from the same SQL statement?

I've tried using these SQL statements together:

Dim sqlUpdateStatement As String = "SET @uid := 0;UPDATE process_data SET reading = TRUE, idprocess_data = (SELECT @uid := idcrawl_data) WHERE reading IS NOT TRUE AND processed IS NOT TRUE LIMIT 1;SELECT @uid;"

but it tells me that there was a fatal error encountered during command execution.

Any ideas?

EDIT

After some testing, I've come to the conclusion that you can't use MySQL variables when performing updates in VB.Net. Is this true? And if so, is there a workaround?

Wakka02
  • 1,491
  • 4
  • 28
  • 44
  • This should be done in a store procedure. Maybe think of it a different way, instead of selecting 1 at a time. Select all that need modification and send that list to your thread and let it process. When it's done, get a new list of id to modify. – the_lotus Jun 27 '18 at 14:42
  • I think you may have to resort to using a `for update trigger` and getting the Id from the `Inserted` table inside the trigger. [This question/answer](https://stackoverflow.com/a/1610530/9365244) seems similar to yours, with the answer being related to a Trigger – JayV Jun 27 '18 at 14:42
  • @the_lotus the backend demands that the processing is done row by row though... – Wakka02 Jun 27 '18 at 14:45
  • You should use a stored procedure ..... – Prescott Chartier Jun 27 '18 at 17:03
  • "Because there is so much data, I'm planning to do a multi-threaded program for it." - That's not really good reason. If you said "Because there is so much **CPU-based computation**, I'm planning to do a multi-threaded program for it." then you'd be on a winner. With lots of data you need lots of bandwidth - multithreading doesn't help with that. – Enigmativity Jun 28 '18 at 05:40

1 Answers1

0

I eventually took the time to debug the SELECT FOR UPDATE portion of my code to get it to work on a Transaction basis. Thanks everyone for their time!

Wakka02
  • 1,491
  • 4
  • 28
  • 44