• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

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
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

Open in new window

0
mSchmidt
Asked:
mSchmidt
  • 3
  • 2
1 Solution
 
bmatumburaCommented:
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.
0
 
mSchmidtAuthor Commented:
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.
0
 
deepakra79Commented:
Instead of using connection.execute method, you can use locking mechanism available with recordsets.
Lock Type Description
adLockReadonly this lock mode is used when no additions updates or deletions are allowed from recordset
adLockPesimistic In pessimistic locking the record is locked as soon as editing begins and remains locked until editing is completed.
adLockOptimistic this occurs when the update method is called on the record. the record is unlocked even while edit but is temporarily locked when the changes are saved to the database
adLockBatchOptimistic This option allows us to perform optimistic locking when we are updating a batch of records

 
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
mSchmidtAuthor Commented:
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 ?

0
 
bmatumburaCommented:
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

Open in new window

0
 
mSchmidtAuthor Commented:
wouldnt that just keep the table locked as long as the update was running ?
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now