Lock log in SQL Server

0

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.

    
asked by anonymous 10.07.2018 / 15:01

1 answer

0

    

13.07.2018 / 02:07