Devart Forums

Hello,

I have a sofware that provides serial numbers. The DB name is "DB_SERIALS" and the table is "SERIALS" (InnoDB).

I have many agents/clients that are connected to DB to find an empty serial number and reserve it. So, the client is executing a TMyQuery

Query1:
SELECT * FROM serials
WHERE inuse = "N"
LIMIT 0,1

I get the serial in a local delphi variable

and then another query
Query2:
UPDATE serials
SET inuse = "Y" WHERE serial = :serial

The problem is when 2 agents tries to get a serial at the same time. I need to LOCK table until the UPDATE procedure is finishes and then other agents can work in the table.

I need an idea and/or example code please.

Thanks

Please see Mydac\Demos\Win32\Lock\ folder for example.

I already. the demo has nothing to do with my example. The example uses a table and I want to use a SQL code (UPDATE) via Query

In this case please see MySQL Reference about SELECT ... FOR UPDATE and SELECT... LOCK IN SHARE MODE statements.

it was much easier than this.
it was 1-2 lines of code only. I figured out it by myself.

Thanks anyway...

Hi Ben, it's always nice to share solutions to questions posted

I have two queries.

Query1 that has "SELECT ... blah blah WHERE "
and on AfterOpen I have

Query1.LockTable(ltWrite);

then another query

Query2 that has "UPDATE blah blah WHERE"
and on AfterExecute I have:

Query2.UnlockTable;

And it works fine.

LockTable method will work, but this may cause some performance problems if you have many agents/clients. I advise to read MySQL Reference about it.

Hello,

Query1:
Lock Tables serials write; <<< Lock the table.
SELECT * FROM serials
WHERE inuse = "N"
LIMIT 0,1;

I get the serial in a local delphi variable

and then another query
Query2:
UPDATE serials
SET inuse = "Y" WHERE serial = :serial;
Unlock Tables;


in my case this code resolve the problem
read in mySQL help about Lock Tables

good look