Armin Armin - 1 year ago 58
SQL Question

Two users attempt do to the same thing at the same time

I'm writing a program using visual basic 2015 and MySql database and I asked this question somewhere else before but they couldn't help me.

For example we have a table named "users" :

username | coins
user1 | 3
user2 | 5

I want to change the coin value and this code is working fine but in this code : 1- I get the column's value. 2- I add a unit to the value. 3- I put the value in column. So the problem is what if the database is online and two different computers run the program and try to change the value at the same time? For example one of them is trying to give user1 2 coins and the other one is trying to give user1 4 coins. Then they click on add coin at the same time. First computer gets the value and it is 3. Second Computer gets the value and it is 3. First computer add 2 coins (3+2=5) and put 5 in the column. Now second computer add 4 coins (3+4=7) and put 7 in the column. So we have 7 in our column but we should have 9 because 3+2+4=9

So here is the question : Is there a way to add a value directly to a column or is there another way to solve this problem?

MysqlConn = New MySqlConnection
MysqlConn.ConnectionString = "Server='" & TextBox_MYSQL_Host.Text & "';UserID='" & TextBox_MYSQL_Username.Text & "';Password='" & TextBox_MYSQL_Password.Text & "';Database='" & TextBox_MYSQL_Database.Text & "';Character Set=utf8"

DataAdptr = New MySqlDataAdapter
DataTable = New DataTable
MysqlComd = New MySqlCommand
With MysqlComd
.Connection = MysqlConn
.CommandText = "Select * FROM users WHERE username ='" & user1.text & "';"
End With
DataAdptr.SelectCommand = MysqlComd
If DataTable.Rows.Count = 0 Then
' Error!
Dim gold As Integer = DataTable.Rows(0).Item("coins").ToString()
gold = gold + 1
MysqlComd = New MySqlCommand
MysqlComd.Connection = MysqlConn
MysqlComd.CommandText = "UPDATE users SET coins='" & gold & "' WHERE username='" & user1.Text & "'"
End If
Catch ex As MySqlException
' Connection Error
End Try
End Sub

Answer Source

Disregarding the issues with your code as mentioned in the comments, there are two ways to address the issue you're specifically asking about.

Firstly, you can continue to do it the way you are and employ optimistic concurrency. What that does is assume that each user is the only user editing the data and save as though that's the case and then throw an exception if it turns out to not be. Specifically, when a record is saved, the current values in the database are compared with the original data retrieved by that user and the new values will be saved if and only if they match. If they don't, it's up to you to catch the ConcurrencyException that's thrown and do whatever is appropriate. That will usually be retrieving the data again and either asking the user to start editing again or merge the current data with their existing modifications.

The other option is to simply increment what's in the database rather than saving a specific value, e.g.

UPDATE MyTable SET MyColumn = MyColumn + 1 WHERE ID = @ID