Transactions deadlocks

Classic Servoy
1

I am using transactions in a solution where the clients can edit records and the data will be saved only at request (all the forms where data can be edited have this functionality)

What happens is that when 2 or more clients are on the same record at the same time, with the transaction being active, obviously, a deadlock occurs.

I tried to fix this using locks, but they want to commit the transaction only at request, so even if I unlock the record onRecordSave, I cannot commit the transaction and the deadlock occurs anyway.

For example, the user edits a record and then clicks outside the field; the record is saved and the lock is released, then he goes to the previous record, which has a lock on it (it is locked by the other client). The other user will release the lock on this record and then they will both be in the same record, one client being frozen(the one who had the lock) and the other getting a message that ‘You do not have access to modify this data…’, just like if this record would be locked by the other client (which is not the case). Transactions are active on both sides.

Could anyone point me in the right direction on how to achieve the functionality that records are saved only at request (throughout the whole application), and that deadlocks do not appear?

2

i still don’t get how you get deadlocks. Are you saying that you have 2 tables that are used by both and both start a transaction but client A first goes to table A and client B to table B. Then B wants to go to A and A to B?

yes that you have a deadlock…

If such a thing is the case then you could try to first get all the locks you want over all tables/records. And if all are successfull then start a transaction to modifiy them all.
Lock and transaction support is much improved in the last build of servoy so you should use that one when testing.

3

I get a deadlock in the same table (I was talking about 2 records in the same table).

4

but are they then both want to access those 2 records?
So same story: a access record a and b record b then a also record b and a also record b?

If this is the case then yes you need to make sure that this can’t happen by first locking those 2 records.