Using SQL Server and Delphi, I need to control so that in a certain database table two changes can not be made at the same time in the same record, I can not start the update if there is already another change in progress. I need to know if this is possible. The scenario is as follows: we have the TableTest table with n records, and two access terminals, A and B. If terminal A wants to change some data from register 1, it must acquire a lock on that line, so that terminal B tries to change that same line it receives an error message. But changing different rows is allowed. For that I was able to obtain a certain success. The flow is as follows:
1 - Transaction started;
2 - Execution of a query to lock the requested record: - > 'select * from TableTest with (updlock, nowait) where code =: code';
3 - If there is no error executing the above query, the record is free and the flow continues, otherwise an error is generated and the operation is aborted;
4 - Performed the required update operation;
5 - Transaction is committed;
I'm having trouble with the following situation:
1 - Terminal A starts changing line 1 of this table;
2 - Terminal B starts changing line 2 of this table. Here everything is fine, they are different lines;
3 - Terminal A attempts to perform its update (step # 4 in the above schematic). Here is the problem, as long as Terminal B does not finish or cancel its processing, this update will not be performed here, leaving the user waiting;
If someone has some knowledge to help, say if it is the combination of the hints that are being used in the select, or if this situation is really not possible.