to lock a table when opened with FDQuery

[Midsoft UK]

Hi

Using Delphi 10.3

In Advantage database we used lock a record using - 

if tbl.AdsLockRecord(tbl.RecNo) then

Is there an equivalent in FireDac ?

I know I could just test with .Edit but just in case there is a better way.

Thanks

Andy

[Karol Bieniaszewski]

You can look record by eg.

 

Update MY_TABLE SET ID=ID WHERE ID=12345;

Or explicitly during select

 

SELECT ... FROM single_table

   [WHERE ...]

   [FOR UPDATE [OF ...]]

   [WITH LOCK]

Look at docs:

https://firebirdsql.org/refdocs/langrefupd21-notes-withlock.html

 

Regards,

Karol Bieniaszewski

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/cfe026ed-d923-40cd-8b4f-f584eb949335n%40googlegroups.com.

 

[Midsoft UK]

Thanks

[Midsoft UK]

only thing is - I need to hold a lock on the record while I perform a task, the either cancel or post depending on the result so just issuing an update statement won't hold onto it. Is there nothing like .RecordLock ?

Andy

[liviuslivius]

Probably do not understand you. But if you cancel simply rollback transaction and the lock will gone.

1. Start transaction

2. Lock record by Update mytable set id=id where id=123

3. Do some tasks

4. Release the lock by commit or rollback or rollback to savepoint before a lock.

Regards,

Karol Bieniaszewski

[Tim Crawford]

An update statement will lock the row until you commit.

Also the option Midsoft UK provided would work

I am curious why you need to hold a lock, in my experience it is not a good idea.

I have had batch processes come to a halt waiting on a row lock because a user
opened an edit dialog, then went home for the weekend without saving (committing)

Also check transaction parameter isc_tpb_nowait, I set this on all of my transactions now and
handle the exception in the program so batch processes won't hang up (they skip locked rows)
and interactive users can be notified the row is locked, otherwise they just get hung up.

[Midsoft UK]

ti...@ckmhealthcare.com

We provide a lock because we prefer to completely control the assigning of the next ID for a table.  We use a sequence table with a record for each table that requires an ID. Has worked flawlessly for 30 years. We need to hold the lock for a few milliseconds while we check the ID doesn't already exist before updating the sequence table. The whole process is in a repeat statement so if it can't get the next ID it just tries again.

[Mark Rotteveel]

On 29-03-2021 18:56, Midsoft UK wrote:
> We provide a lock because we prefer to completely control the assigning
> of the next ID for a table.  We use a sequence table with a record for
> each table that requires an ID. Has worked flawlessly for 30 years. We
> need to hold the lock for a few milliseconds while we check the ID
> doesn't already exist before updating the sequence table. The whole
> process is in a repeat statement so if it can't get the next ID it just
> tries again.

Consider using a sequence instead (or an identity column if you're using
Firebird 3).

Mark
--
Mark Rotteveel

[Midsoft UK]

We tried that route initially but we found we are so dependent on the database itself, our way made it simpler. I suppose multiple ways to do the same thing. :)