Multiple batches and concurrency...

Posts   
 
    
hlesesne avatar
hlesesne
User
Posts: 47
Joined: 22-Jul-2004
# Posted on: 04-Oct-2004 22:33:07   

I am building a mail queueing system using LLBL Gen and am trying to figure out how to get around some concurrency issues.

I have a table structure similar to the following:

tblBatch BatchID BatchOpened BatchCount BatchComplete

tblBatchMail BatchMailID BatchID MailID

tblMail MailID CompanyID UserID MailDate LetterID

tblLetter FromAddress FromName ToAddress ToName Subject Body ContentType

Mail is queued by creating a tblMail Entity and a corresponding tblLetter. All of this works great.

The problem that I am having is when I pull batches to our web clients. The process is the following

1) Create tblBatch Entity 2) build a tblMail collection based on certain criteria of both tblMail and tblLetter
3) create tblBatchMail Entities for each tblMail entity in the collection 4) pull down the tblMail Collection to the web client for processing.

Everything works fine with this as long as I just have a single web client processing at one time… I was hoping to get thoughts on the best way to accomplish this when I have to scale out to multiple web clients all requesting batches simultaneously…without assigning mail to mutiple batches.

I had considered a simple

Synclock(me) BuildBatch End Sync

But didn’t know if there was a better way – i.e. move the batchId assignements directly to tblMail and then do an updatemulti() on the tblMail Collection…

Any thoughts or suggestions would be greatly appreciated.

Best Regards,

Hal

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 05-Oct-2004 10:01:41   

I don't see why multiple webclients would be of any problem, UNLESS you're sharing a DataAccessAdapter object among users via the Application object for example.

Or is your system setup differently? (i.e.: a service which handles multiple requests from remote clients) And what are the errors you're running into?

Frans Bouma | Lead developer LLBLGen Pro
hlesesne avatar
hlesesne
User
Posts: 47
Joined: 22-Jul-2004
# Posted on: 05-Oct-2004 14:22:03   

Actually the problem is isolating the batches so that each web client gets a unique set of mail...

For example... if I pull a batch of 100 messages (MailID 1 - 100) and begin to assign those messages to a batch by inserting records into tblMailBatch, then a second web client requests another batch before all of the first batch is assigned and gets MailID 50-150, then I have concerency issues when the second batch tries to assigned 50-100 to their batch...

So far, it has appeared to me that the best way to do this is either 1) to move the batch assignments to tblMail and just do an updatemulti on a now fK in tblMail or 2) Use a synclock block to make sure that I am only doing a single assignment at a time.

Thanks for your response on this - I recognize that it may not be as mush of an LLBL topic as an application design topic and I certainly appreciate any insight.

Otis wrote:

I don't see why multiple webclients would be of any problem, UNLESS you're sharing a DataAccessAdapter object among users via the Application object for example.

Or is your system setup differently? (i.e.: a service which handles multiple requests from remote clients) And what are the errors you're running into?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 05-Oct-2004 14:53:59   

Ah simple_smile

You can solve this in two ways. 1) in one transaction, first create the tblBatch entity, save it, refetch it and with that ID save the rest of the entities for the mailbatch. All done? commit transaction. I assume the batchID is an autonumber/sequenced id? 2) create first all entities in memory in a graph. Then save the batch entity recursively. This will take care of everything, and will save everything in one transaction again using an autonumber/sequenced key on batch.

Frans Bouma | Lead developer LLBLGen Pro
hlesesne avatar
hlesesne
User
Posts: 47
Joined: 22-Jul-2004
# Posted on: 05-Oct-2004 15:08:23   

Otis wrote:

2) create first all entities in memory in a graph. Then save the batch entity recursively. This will take care of everything, and will save everything in one transaction again using an autonumber/sequenced key on batch.

In this option, would it mean that any saves with concurrency issues would just be discarded... I am assuming you mean that I would do something like this:


Dim MailCollection as new MailCollection
MailCollection.GetMulti(WhateverPredicate)

Dim MailBatchCollection as new MailBatchCollection
for each Mail as MailEntity in MailCollection
 dim MailBatch as New MailBatchEntity
 MailBatch.BatchID = myBatchID
 MailBatch.MailID = Mail.MailID
 MailBatchCollection.add (mailbatch)
next

MailBatchCollection.SaveMulti(True)

But this does not keep duplicate messages out of MailCollection for other clients does it? Am I way off base on what you meant?

Thanks again for the replies.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 05-Oct-2004 15:38:42   

Oh, I see, you want a mail to end up in just 1 batch? So if user A selects 1-50, another user, B, can't select 25-50 for his batch ?

Frans Bouma | Lead developer LLBLGen Pro
hlesesne avatar
hlesesne
User
Posts: 47
Joined: 22-Jul-2004
# Posted on: 06-Oct-2004 21:27:24   

Otis wrote:

Oh, I see, you want a mail to end up in just 1 batch? So if user A selects 1-50, another user, B, can't select 25-50 for his batch ?

Right, I think I am leaning toward moving the batch assignments directly to tblMail and doing an updatemulti. But any advice would be greatly appreciated.

Thanks,

Hal

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 06-Oct-2004 21:55:43   

You need some sort of locking mechanism I think, but I always avoid row locks as these are pretty drastic (and could hurt the application).

A single update call could solve the problem a bit, you still need a check query though. You can also try a serialized transaction, which locks rows exclusively.

Frans Bouma | Lead developer LLBLGen Pro
hlesesne avatar
hlesesne
User
Posts: 47
Joined: 22-Jul-2004
# Posted on: 07-Oct-2004 03:51:47   

Otis wrote:

You need some sort of locking mechanism I think, but I always avoid row locks as these are pretty drastic (and could hurt the application).

Yeah - I because there is a single point of entry, I think I will be able to get by with a

synclock (me)
 'pull batch
end sync

In order to grant exclusive access.

Otis wrote:

A single update call could solve the problem a bit, you still need a check query though. You can also try a serialized transaction, which locks rows exclusively.

In looking further I agree that I want to stay away from the row locking and serialized transactions so as not to inhibit new messages from being placed in tblMail-tblLetter. Hopefully the synclock will work correctly. I will also add a unique constaint on MailID/BatchID to tblMailBatch just in case simple_smile ...

Thanks again for your responses to this potentially off-topic post... best regards and thanks as always for your tireless devotion to your customers.

Hal