Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 5 of 5 total
Thread Locking a Group of Records for Processing (Client/Server)
Sat, Jan 27 2007 10:20 AMPermanent Link

"Johnnie Norsworthy"
I have a new application where I need to lock a series of records for
processing by an individual station so they will not be processed by other
stations. So, considering I have 100 records, I would like station 1 to lock
10, then station 2 to lock the next 10, and so on ...

What would be the best way to handle such a locking scheme? Should I add a
field to mark which station has the record open, then mark it null after
processing and include a timestamp for the last processed time?

I was think of something along the following:

UPDATE Table WHERE Station=null ORDER BY TimeStamp TOP 10 SET
Station=:StationParam
(I don't know the syntax yet because I haven't used TOP)
I do have to INSURE that two machines could never be grabbing the same
records at once.

Then do my processing on each and set Station=null,
TimeStampt=CURRENT_TIMESTAMP after each is processed.

Does this sound like the best way to do this or does any fellow DBISAMer (or
ElevateDBer) have any better suggestions?

Thanks folks!

-Johnnie

Sat, Jan 27 2007 12:41 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Johnnie,

<< I have a new application where I need to lock a series of records for
processing by an individual station so they will not be processed by other
stations. So, considering I have 100 records, I would like station 1 to lock
10, then station 2 to lock the next 10, and so on ...

What would be the best way to handle such a locking scheme? Should I add a
field to mark which station has the record open, then mark it null after
processing and include a timestamp for the last processed time? >>

For DBISAM you'll have to use a column to mark the rows.  With EDB, you can
manually lock as many rows as you would like with these methods:

http://www.elevatesoft.com/edb1d5_tedbdataset_lockcurrentrecord.htm
http://www.elevatesoft.com/edb1d5_tedbdataset_unlockcurrentrecord.htm
http://www.elevatesoft.com/edb1d5_tedbdataset_unlockallrecords.htm

Sorry about the lack of docs, they're almost done.

EDB keeps track of manually-locked rows vs. auto-locked rows and will only
unlock those that you manually locked when calling the UnlockAllRecords
method.

--
Tim Young
Elevate Software
www.elevatesoft.com

Sat, Jan 27 2007 2:30 PMPermanent Link

"Johnnie Norsworthy"
"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:959DCDE3-A7B9-4238-9B0C-35BF3294DEF2@news.elevatesoft.com...
> For DBISAM you'll have to use a column to mark the rows.  With EDB, you
> can manually lock as many rows as you would like with these methods:
>
> http://www.elevatesoft.com/edb1d5_tedbdataset_lockcurrentrecord.htm
> http://www.elevatesoft.com/edb1d5_tedbdataset_unlockcurrentrecord.htm
> http://www.elevatesoft.com/edb1d5_tedbdataset_unlockallrecords.htm
>
> Sorry about the lack of docs, they're almost done.
>
> EDB keeps track of manually-locked rows vs. auto-locked rows and will only
> unlock those that you manually locked when calling the UnlockAllRecords
> method.

Stop advertising already! SmileI have to do this in DBISAM (proven
reliability you know), but I will certainly look into all of the new cool
stuff when I purchase ElevateDB, hopefully in the next few weeks. Just
waiting for some money.

Do I need to do some kind of transaction around the:
UPDATE Table WHERE Station=null ORDER BY TimeStamp TOP 10 SET
Station=:StationParam

It certainly could be possible that two stations attempt to perform this SQL
simulataneously.

-Johnnie

Sat, Jan 27 2007 4:31 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Johnnie,

<< Stop advertising already! SmileI have to do this in DBISAM (proven
reliability you know), but I will certainly look into all of the new cool
stuff when I purchase ElevateDB, hopefully in the next few weeks. Just
waiting for some money. >>

Don't worry, I'm not trying to pressue you at all - just letting you know
that relief is on the way. Smiley

<< Do I need to do some kind of transaction around the:

UPDATE Table WHERE Station=null ORDER BY TimeStamp TOP 10 SET
Station=:StationParam >>

Sorry, I completely missed that you had included an ORDER BY in your UPDATE
statement.  You can't use an ORDER BY in an UPDATE statement (TOP either).
You'll have to do a SELECT like this:

SELECT * FROM Table WHERE Station=null ORDER BY TimeStamp

Making sure that RequestLive:=True and that there is an index on the
TimeStamp field in the table.  Then, just update the first 10 records with
the station number.  You'll want to wrap the whole thing in a restricted
transaction on just the one table so as to prevent anyone else from
modifying the same records at the same time.

--
Tim Young
Elevate Software
www.elevatesoft.com

Sat, Jan 27 2007 5:34 PMPermanent Link

"Johnnie Norsworthy"
"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:8AFA0334-C71B-41DD-8690-9BFD012DC678@news.elevatesoft.com...
> Making sure that RequestLive:=True and that there is an index on the
> TimeStamp field in the table.  Then, just update the first 10 records with
> the station number.  You'll want to wrap the whole thing in a restricted
> transaction on just the one table so as to prevent anyone else from
> modifying the same records at the same time.

Thanks!

Image