0
Using SQL Server and Delphi, I need to control so that in a certain database table two changes cannot be made at the same time in the same record, I cannot start the update if there is already another change in progress. I need to know if that’s possible. The scenario is as follows: one has the table Tabelateste with n records, and two access terminals, A and B. If terminal A wants to change some data from record 1, it must acquire a lock on that line, so that if terminal B tries to change that same line it gets an error message. But changing different lines is allowed. For this I managed to obtain a certain success. The flow is the following currently:
1 - Transaction initiated;
2 - Execution of a query to lock the requested record: -> 'select * from Tabelateste with (updlock, nowait) Where code = :code';
3 - If no error occurs when executing the above query, the record is free and the flow continues, otherwise an error is generated and the operation is aborted;
4 - Carried out the necessary update operation;
5 - Transaction is committed;
I’m having trouble with the following situation:
1 - Terminal A starts changing row 1 of this table;
2 - Terminal B starts changing row 2 of this table. Here all right, are different lines;
3 - Terminal A tries to perform its update (step 4 of the above scheme). Here is the problem, while Terminal B does not complete or cancel its processing, this update here will not be performed, leaving the user waiting;
If anyone has any knowledge to assist, say if it is the combination of the hints being used in select, or if really this situation is not possible.
Good afternoon @Tulio. I happened to have a very similar question today. Take a look to see if this is your case..: https://answall.com/questions/73667/lock-de-registro. This link points to another link that I thought might fit your problem..: https://www.xpertdeveloper.com/2011/11/row-locking-with-mysql/ . If these links are helpful in a reply stating this :) Hugs.
– Ricardo M.Souza
Good morning Ricardo, but it is not the same case, although similar. The syntax between Mysql and SQL Server are different. My case is more directly linked to the fact that I need the update to lock the table at the row level, and not at the page or table level, as seems to be happening.
– Túlio