Help on locking

[ben]

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

[Ikar]

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

[ben]

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

[Ikar]

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

[ben]

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

Thanks anyway...

[GEswin]

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

[ben]

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.

[Ikar]

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.

[eduardosic]

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