mSchmidt
asked on
Multiple workstation, both INSERTING, instead of One insert and one Update
Hi
I have an application where i submit a command to multiple workstations through a Socket connection, once they recieve this command this should update a value if it already exists and insert a new if it doesnt.
My problem however is that this socket is so fast that the command shown below sometimes will result in two inserts, meaning two computer simultaneusly do the UPDATE and gets a @@ROWCOUNT of 0 returned, resulting in both doing an insert.
How do i prevent this from happening ?
the registerCnxn is a open ADODB.connection
I have an application where i submit a command to multiple workstations through a Socket connection, once they recieve this command this should update a value if it already exists and insert a new if it doesnt.
My problem however is that this socket is so fast that the command shown below sometimes will result in two inserts, meaning two computer simultaneusly do the UPDATE and gets a @@ROWCOUNT of 0 returned, resulting in both doing an insert.
How do i prevent this from happening ?
the registerCnxn is a open ADODB.connection
sqlSent = "UPDATE kasseOpt SET amount = " & getSQLDecimalVal(amount) & " Where opgTal_id=" & opgtal_id & " and lukket_id=" & lukID & " and workstation is null and person_id is null and kasse_id is null"
sqlSent = sqlSent & " IF @@ROWCOUNT = 0 INSERT INTO kasseOpt (opgtal_id, lukket_id,amount) VALUES (" & opgtal_id & "," & lukID & "," & getSQLDecimalVal(amount) & ")"
frmFront.registerCnxn.execute sqlSent
Instead of updating the database immediately when a message is received from a workstation, I would put the message in a shared Queue. I would then create a worker thread that polls the Queue and if the Queue has message, remove each message and save to the database.
ASKER
the SQL is transmitted directly from each workstation and does not run through the server application.
The server submit a command to each workstation that then directly updates/inserts data into the SQL database.
The server submit a command to each workstation that then directly updates/inserts data into the SQL database.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So i should do a adLockPesimistic ?
Cant i do a Pesimistic lock on a Connection.execute ?
Doing it with a recordset is just slower, isnt ?
Cant i do a Pesimistic lock on a Connection.execute ?
Doing it with a recordset is just slower, isnt ?
Try to use the WITH(TABLOCKX) SQL hint in your query:
sqlSent = "UPDATE kasseOpt WITH(TABLOCKX) SET amount = " & getSQLDecimalVal(amount) & " Where opgTal_id=" & opgtal_id & " and lukket_id=" & lukID & " and workstation is null and person_id is null and kasse_id is null"
sqlSent = sqlSent & " IF @@ROWCOUNT = 0 INSERT INTO kasseOpt (opgtal_id, lukket_id,amount) VALUES (" & opgtal_id & "," & lukID & "," & getSQLDecimalVal(amount) & ")"
frmFront.registerCnxn.execute sqlSent
ASKER
wouldnt that just keep the table locked as long as the update was running ?