Is there any sort of "interlocked exchange" in DBISAM 4?

Basically, I have a table of work items, and I have a number of threads
who are looking for work. They'll do a query, find the first item and then
want to mark themselves as the thread working on it. Of course, several
threads might try to do this at the same time. I need something to stop
them all thinking they own it.

Now, one way would be a transaction, but that's too large scale for this.

Can I put a lock on a particular record, such that only one gets to lock
it? Thus the code would be:
 Find work item using a SELECT
 Lock the work item row
 Read the work item's thread ID
 If still NULL, then set it to mine
 Unlock the work item row
 If it was set to mine, then process it, otherwise loop.
 
An ability to do this all in one SQL statement would be good, but am I on
the right lines?  Reading the help indicates I can't manually lock a row.
Therefore I wonder if that I need to do is actually edit the row to lock
it, and then check the value. That said, I could presumably do it in a SQL
pair:

UPDATE WorkTable WHERE (WorkID = :MyWorkID) AND (ThreadID = NULL)
 SET (ThreadID = :myThreadID)

Isn't this exactly what I want? That is, the query will update the work
item but only if it still matches because the thread ID hasn't been set by
another thread. I presume this is atomic enough in the engine?

Obviously I'll then re-check the work ID to see if it is mine, and if not
loop round. It looks like it would work to me... Please, if you read this
far, let me know if you agree or have found fault. Is there a better way?
Many thanks!

/Matthew Jones/

Matthew


Will this actually result in ONLY ONE row being updated or could it be more than one? My guess is the latter

UPDATE WorkTable WHERE (WorkID = :MyWorkID) AND (ThreadID = NULL)
 SET (ThreadID = :myThreadID)

I would 1) wrap the getting work bit in a transaction, 2) use a table rather than sql it will probably be easier


Roy Lambert

Hmm, good thought. I think it will only get one, since the initial "is
there work" query will get more than one, but each will have a unique work
ID. This work ID is the "(WorkID = :MyWorkID)" part of the update, to make
sure we only change the one we care about.

Using tables does sound easier, but I've been amazed at the power of SQL
in one project, and so long as it is atomic in itself, then it will do the
job I think. I've fallen back to tables in other areas though for
simplicity, so when I get to code this I'll consider carefully.

/Matthew Jones/

Matthew


Remember the similarity of filters and where clauses (thanks Tim). Its a doddle to use a filter (with appropriate indices) so what you would do is

1) set filter
2) try and edit the first record (assuming pessimistic locking)
3) if the lock fails try the next
4) get on with it

SQL (IMNSHO) is great when it can affect blocks of records in one go and the larger the block the better. When it comes down to individual bits navigational code is often better

Roy Lambert

"Matthew Jones" <mattjones@cix.co.uk> wrote in message
news:memo.20070419092624.5008G@nothanks.nothanks.co.uk...
> Is there any sort of "interlocked exchange" in DBISAM 4?
>
> Basically, I have a table of work items, and I have a number of threads
> who are looking for work. They'll do a query, find the first item and then
> want to mark themselves as the thread working on it. Of course, several
> threads might try to do this at the same time. I need something to stop
> them all thinking they own it.
>
my 2 cents :
- Let the threads not find the work but give the work from another thread
that distributes it OR
- use a buffer and let one thread fill the buffer. (one producer - many
consumers)

The performance could improve as there is no need to do multiple queries

Thanks for that. I did initially consider that, but the database is
complex and is essentially to be filled in by the threads. But it would
simplify the control over the whole thing, so I may just go back to this.
Thanks for the $0.02.

/Matthew Jones/

Matthew,

<< That said, I could presumably do it in a SQL pair:

UPDATE WorkTable WHERE (WorkID = :MyWorkID) AND (ThreadID = NULL)  SET
(ThreadID = :myThreadID)

Isn't this exactly what I want? That is, the query will update the work
item but only if it still matches because the thread ID hasn't been set by
another thread. I presume this is atomic enough in the engine? >>

Yep, UPDATE statements are always completely atomic for single row updates.

<< Obviously I'll then re-check the work ID to see if it is mine, and if not
loop round. >>

Just check the RowsAffected property - if it's 1 then the work ID is yours,
otherwise it isn't.

--
Tim Young
Elevate Software
www.elevatesoft.com

> Just check the RowsAffected property - if it's 1 then the work ID is
> yours, otherwise it isn't.

Brilliant solution. Many thanks.

/Matthew Jones/