Help on locking

Help on locking

Postby ben » Wed 09 Nov 2005 12:58

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
ben
 
Posts: 119
Joined: Wed 17 Nov 2004 19:48

Postby Ikar » Wed 09 Nov 2005 13:01

Please see Mydac\Demos\Win32\Lock\ folder for example.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby ben » Wed 09 Nov 2005 18:07

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
ben
 
Posts: 119
Joined: Wed 17 Nov 2004 19:48

Postby Ikar » Thu 10 Nov 2005 13:41

In this case please see MySQL Reference about SELECT ... FOR UPDATE and SELECT... LOCK IN SHARE MODE statements.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby ben » Fri 11 Nov 2005 01:07

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

Thanks anyway...
ben
 
Posts: 119
Joined: Wed 17 Nov 2004 19:48

Postby GEswin » Fri 11 Nov 2005 08:55

Hi Ben, it's always nice to share solutions to questions posted
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby ben » Fri 11 Nov 2005 11:25

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.
ben
 
Posts: 119
Joined: Wed 17 Nov 2004 19:48

Postby Ikar » Fri 11 Nov 2005 15:19

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.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

i have a solution..

Postby eduardosic » Fri 18 Nov 2005 00:29

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
eduardosic
 
Posts: 387
Joined: Fri 18 Nov 2005 00:26
Location: Brazil


Return to MySQL Data Access Components