B4J Question B4J server & MySql: is this code weak ?

[marcick]

I have a server and hundreds of B4A/B4I clients.
Clients consume "virtual credits" during operations and communicate this value to the server, so that a MySql table is updated with the new credit.
The flow is something like this:

1. Client A consumes 5 credits and posts this value to the B4J server that receive it through a server handler.
2. Server handler receive the value 5
3. Server make a DbQuery and read the current value of the credit, suppose it is 100
4. Server subtract 5 from the credits and store in the DB the new value 95
5. Server send an OK to the client

Now, suppose we are at point 3 in the server and another client communicates its consumed credits to the server, suppose the value is 10.
I suppose a new session of the server handler is started, right ?
What happens if the first handler is not completed and the second handler still read 100 from the DB ?

When everything will be finished, will be the final value in the DB always 85 or may happens 90 ?

 

[marcick]

Try to replace the '5' with ? and add a variable to the array. If it doesn't work, the SQL engine will throw an error, then change it to:

conA.ExecNonQuery2($"UPDATE credits SET LastAccess=?, Credits=Credits - ${YourVariable} WHERE Username=? AND Password=?"$, Array AS Object(LastAccess, Username, Password))

Just replacing "5" with the variable it works.
This is the actual code

Dim cursor1 As ResultSet
cursor1=conA.ExecQuery2("SELECT * from credits WHERE Username=? And Password=?", Array As Object(Username, Password))
Do While cursor1.NextRow
    Dim Credits As Int=cursor1.GetString("Credits")
    Exist=True
Loop
cursor1.close
If Exist=False Then           
    resp.Write("UNKN")
    Log("Unknown user")
Else
    conA.ExecNonQuery2("UPDATE credits SET Credits=Credits-? WHERE Username=? And Password=?", Array As Object(BilledCredits, Username, Password))
    NewCredits=Credits-BilledCredits
    Dim llu As List
    llu.Initialize
    llu.Add(LastAccess)
    llu.Add(Username)
    llu.add(Password)
    conA.ExecNonQuery2("INSERT INTO billings (Data, Username, Password, BilledCredits, NewCredits) VALUES (?,?,?,?,?)", Array As Object(llu))
    resp.Write(NewCredits)
End If
If conA <> Null And conA.IsInitialized Then conA.Close

But because after the update I need to respond to the client with the new credits, I think something can still go wrong. If a parallel acces happens, maybe the NewCredits I send to the client is not real. I tried to query again the Credits after the update but I got a SQL engine error
I think something like what Daestrum says is needed, just need to find how to ...

 

[Erel]

You can make the handler a single threaded handler. This way the requests will not be handled concurrently. In most cases the performance will be good enough.

 

[marcick]

this looks to be the definitive solution ... Thanks everybody

 

[udg]

Did you check the locking mechanisms offered by the DB?
MySql has "SELECT--- FOR UPDATE" for example.
I'm not an expert on the subject but maybe you could pack a SELECT FOR UPDATE and a following UPDATE statement on same recordID in a common explicit transaction.

 

[OliverA]

conA.ExecNonQuery2("INSERT INTO billings (Data, Username, Password, BilledCredits, NewCredits) VALUES (?,?,?,?,?)", Array As Object(llu))

You have 5 question marks, yet your list only contains three entries (LastAccess, Username, Password). I think you either (1) you pasted the wrong code, or (2) you did not add BilledCredits and NewCredits to the list.

 

[marcick]

Did you check the locking mechanisms offered by the DB?
MySql has "SELECT--- FOR UPDATE" for example.
I'm not an expert on the subject but maybe you could pack a SELECT FOR UPDATE and a following UPDATE statement on same recordID in a common explicit transaction.

I'll search for it, looks interesting, thanks.

@OliverA: don't care about that, I justed copied a part of code and modified to show here, that's not the real running code ... (but thanks anyway)