update with locktable

Guest · Post by **Guest** » Wed 16 Mar 2005 17:56

Hello,

i have a database of serial numbers, like

ID
SERIAL_NUMBER
IN_USE

so, first im doing a

SELECT * FROM serials WHEN IN_USE = "N" LIMIT 1

and im getting the first available serial number. then:

UPDATE serials SET IN_USE = "Y" WHERE ID = :id

:id is updated via ParamByName.

Everything is fine but the problem is that when 2 users tries to run the program at the same time, they read the same serial and i have serious problem. So, i think the only solution is

LOCK TABLES SERIALS WRITE;

I tried to add the LOCK TABLE SERIALS WRITE in the Query but Query is unable to execute a second command.

So, I tried the LockTable() of the Query but it says that it can lock the table in a closed dataset.

Then I used the TMyScript, but this script doesnt allow to use ParamByName to apply variables.

Can you please give me a workaround solution for LockTable in an UPDATE query.

Any comments or redirections are welcomed.

Thank you in advanced.

ben · Post by **ben** » Thu 17 Mar 2005 08:23

Guest? Hey, its me I posted it (regged users).

ben (aka George)

Thanks

Ikar · Post by **Ikar** » Thu 17 Mar 2005 10:38

> UPDATE serials SET IN_USE = "Y" WHERE ID = :id

If you execute this query manually through TMyQuery or TMyCommand then you should lock rows also manually executing "SELECT ... FOR UPDATE" on starting record modification.

To edit with Edit..Post use handlers

Code: Select all

procedure TForm3.MyTable1BeforeEdit(DataSet: TDataSet);
begin
 MyTable1.Connection.StartTransaction;
 MyTable1.Lock(lrImmediately);
 MyTable1.RefreshRecord;
end;

procedure TForm3.MyTable1AfterPost(DataSet: TDataSet);
begin
 MyTable1.Connection.Commit;
end;

procedure TForm3.MyTable1AfterCancel(DataSet: TDataSet);
begin
 MyTable1.Connection.Rollback;
end;

To use locking SERIALS table must be InnoDB-table.

ben · Post by **ben** » Thu 17 Mar 2005 19:34

MyTable1? Sorry i dont use Serials, as you can see in using Query. Can you help me on locking using Queries, not tables?

Ikar · Post by **Ikar** » Fri 18 Mar 2005 10:47

It doesn't make great sense. Use Query instead of MyTable1 with the same handlers.

ben · Post by **ben** » Mon 21 Mar 2005 23:09

Well, in the example i made is easy but in the real application is not that easy. The query has many parameters, IF statements, WHERE clauses etc. Also, there is no GUI. This software runs on the background. So, table is useless and hard to use.

So, is there any way to use LOCK TABLES with Query? If not, it will look like a big big lack of MyDAC

Ikar · Post by **Ikar** » Tue 22 Mar 2005 11:51

Lock method can work with MyQuery. Certainly, if text of the query doesn't allow to choose requested data, a query for Lock will not be generated. The same it will not be generated for Insert, Post, Delete or RefreshRecord. However, at most cases queries are generated correctly.

> Also, there is no GUI. This software runs on the background. So, table is
> useless and hard to use.

Please specify relation between GUI and using tables. Have you found out any difficulties with dynamic creating MyDAC components or their using with DataModule?