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

marcick · May 26, 2018

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:

Client A consumes 5 credits and posts this value to the B4J server that receive it through a server handler.
Server handler receive the value 5
Server make a DbQuery and read the current value of the credit, suppose it is 100
Server subtract 5 from the credits and store in the DB the new value 95
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 · May 29, 2018

Erel said:
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:

B4X:

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

B4X:

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 · May 29, 2018

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 · May 29, 2018

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

udg · May 29, 2018

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 · May 29, 2018

B4X:

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 · May 29, 2018

udg said:
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)

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

marcick

Well-Known Member

marcick

Well-Known Member

Erel

B4X founder

marcick

Well-Known Member

udg

Expert

OliverA

Expert

marcick

Well-Known Member

Similar Threads

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

marcick

Well-Known Member

marcick

Well-Known Member

Erel

B4X founder

marcick

Well-Known Member

udg

Expert

OliverA

Expert

marcick

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency