Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Table Locks in FireDAC


This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 1 - Last Post: Aug 10, 2017 10:25 AM Last Post By: Lajos Juhasz
Michael Sawyer ...

Posts: 9
Registered: 9/10/17
Table Locks in FireDAC  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 10, 2017 4:53 AM
Hello my friends,

Is there any way to lock a table using FireDAC and FDQuerys? My goal is, that only one user is capable of fetching and writing data to a locked table until it is unlocked.
In BDE, there was a function called "TTable.Locktable(ltWriteLock)". Does FireDAC have a similar functionality?
If not, how am I able to ensure that someone has exclusive access to a table?

EDIT: Using MySQL/MariaDB. I know there are serveral lock methods in mysql (e.g. Lock tables, get_lock, release_lock),
but this isn't right, because these commands are sent by FDCommand, and may not be cooperative with FireDAC.
For example im locking a table by "Lock tables customer", but FireDAC is accesing this table using an alias "A", which i ALSO have to Lock in the "Lock tables" statement.
This is too unsafe.

Kind regards.

Edited by: Michael Sawyer Yin on Aug 10, 2017 8:15 AM
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: Table Locks in FireDAC [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 10, 2017 10:25 AM   in response to: Michael Sawyer ... in response to: Michael Sawyer ...
Michael Sawyer Yin wrote:

Hello my friends,

Is there any way to lock a table using FireDAC and FDQuerys? My goal
is, that only one user is capable of fetching and writing data to a
locked table until it is unlocked. In BDE, there was a function
called "TTable.Locktable(ltWriteLock)". Does FireDAC have a similar
functionality? If not, how am I able to ensure that someone has
exclusive access to a table?

EDIT: Using MySQL/MariaDB. I know there are serveral lock methods in
mysql (e.g. Lock tables, get_lock, release_lock), but this isn't
right, because these commands are sent by FDCommand, and may not be
cooperative with FireDAC. For example im locking a table by "Lock
tables customer", but FireDAC is accesing this table using an alias
"A", which i ALSO have to Lock in the "Lock tables" statement. This
is too unsafe.

Kind regards.

Edited by: Michael Sawyer Yin on Aug 10, 2017 8:15 AM

I have never used MySQL but in a multi user environment and servers
that's how it's working. You've to use transactions in a combination
with lock table and select for update. You lock data on server not in
the client framework.

For mysql you can read this page:

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02