lundi 27 juin 2016

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"

    Try
        MysqlConn.Open()
        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
        DataAdptr.Fill(DataTable)
        If DataTable.Rows.Count = 0 Then
            ' Error!
        Else
            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 & "'"
            MysqlComd.ExecuteNonQuery()
        End If
        MysqlConn.Close()
    Catch ex As MySqlException
        ' Connection Error
    Finally
        MysqlConn.Dispose()
    End Try
End Sub

Aucun commentaire:

Enregistrer un commentaire