CODEWITHSUNDEEP
c#mysqlsqldatabase
GameHackerPM
GameHackerPM

Reputation: 107

Select Command sometimes gets the same row

I have a project in C# which gets data from MySQL Database table and updating column with (StatusID = 5). It searches for a row with StatusID = 4.

Let me show you the flow:

SELECT * FROM ACCOUNTS WHERE STATUSID = 4
// After that I assign an object with the data i got.
UPDATE ACCOUNTS SET STATUSID = 5 WHERE ACCOUNTNAME = '#ACCOUNTNAME'

This flow takes few ms, but the problem is this project is running over 700 PC at the same time, So it sometimes reach the same part of the codes at the same time, so what happen is 1-5 PCs gets the same account. So i need something like a trigger or to make when i Select an account, it takes few seconds if not changed to be selected again something like that.

Thanks for your attention.

Upvotes: 0

Views: 85

Answers (2)

Cleptus
Cleptus

Reputation: 3541

You need transactions in your code.

You can start a transaction in your SQL code or in your c# code, both do similar things. In your case looks like you need a c# transaction https://msdn.microsoft.com/en-us/library/86773566(v=vs.110).aspx

That being said, you should also check the isolation level, to make sure the behaviour is the desired one https://msdn.microsoft.com/en-us/library/system.data.isolationlevel(v=vs.110).aspx

Upvotes: 1

user244943
user244943

Reputation: 42

Is this ok? It is not the answer for your question but if you cant not update you know it was already changed

UPDATE ACCOUNTS SET STATUSID = 5 WHERE ACCOUNTNAME = '#ACCOUNTNAME' AND STATUSID = 4

Upvotes: 0

Related Questions