Hi I am using llblgen Pro to help convert an application using an ISAM database into SQL. All's going reasonably well up to now but I have hit the old concurrency issue.

The way we handle concurrency in our system at the moment is

a. On a File Maintenance screen, the user can search for a record and bring it to the screen. If they attempt to change any field, then we 1. Place a record lock on the record 2. Re Read the record and redisplay 3. Continue with the editing. Once the user saves the record we release the lock

b. In general processing (say updating a summary table) we 1. Lock the record 2. Re read the record 3. Update the fields (eg salesmtd, qtymtd) 4. Write the record back 5. Remove the Lock

When I thought of moving to sql, the way I thought we would do the above updates was with an sql statement like

update summarytable set salesmtd = salesmtd + sale, qtymtd = qtymtd + qty where summary_month = 100 and itemid = 123

So there is no need for concurrency

However, I don't think that this is the best method with llblgen. How would you do updates like this and take into account concurrency

c. There are probably lots of other types of updates as well. How do you handle general updates in a multi user database. Does adding transaction tracking aid? Certainly option b above would have to be done in a transaction

Hi Phil,

LBLGen Pro offers Concurrency Control which is a way you can tell the framework whether a save/delete is valid depending on that concurrency check. Here are some related threads: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=10960 http://llblgen.com/tinyforum/Messages.aspx?ThreadID=16823

Using locks is not recommended, but if you really need it, you can provide your own transaction when perform an action, with the transaction you can set your preferred IsolationLevel. See Transactions for more information.

daelmo wrote:

Hi Phil,

LBLGen Pro offers Concurrency Control which is a way you can tell the framework whether a save/delete is valid depending on that concurrency check. Here are some related threads: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=10960 http://llblgen.com/tinyforum/Messages.aspx?ThreadID=16823

Using locks is not recommended, but if you really need it, you can provide your own transaction when perform an action, with the transaction you can set your preferred IsolationLevel. See Transactions for more information.

Hi I have several issues with this a. I dont want the update to fail, I want it to prevent a situation where a concurency issue could occur. I guess thats why a locking mechanism seems a reasonable approach

b. How do transactions work at a basic level. If I did the following

i. Start transaction ii. Update Item 123 iii. Update Item 456 iv. End Transaction

What happens if another user tries and reads item 123? Will it allow this (I would prefer that it does). Also in the above, if I perform two updates on item 123, will this cause an issue?

phil.salomon wrote:

a. I dont want the update to fail, I want it to prevent a situation where a concurency issue could occur. I guess thats why a locking mechanism seems a reasonable approach

What is the scenario?

phil.salomon wrote:

b. How do transactions work at a basic level. If I did the following

i. Start transaction ii. Update Item 123 iii. Update Item 456 iv. End Transaction

What happens if another user tries and reads item 123? Will it allow this (I would prefer that it does). Also in the above, if I perform two updates on item 123, will this cause an issue?

That depends on the optimistic/pessimistic level of the transaction. See this for more info: http://msdn.microsoft.com/en-us/library/system.data.isolationlevel.aspx http://goo.gl/c7x3P Also read my link in my above post (Transactions).

daelmo wrote:

phil.salomon wrote:

a. I dont want the update to fail, I want it to prevent a situation where a concurency issue could occur. I guess thats why a locking mechanism seems a reasonable approach

What is the scenario?

phil.salomon wrote:

b. How do transactions work at a basic level. If I did the following

i. Start transaction ii. Update Item 123 iii. Update Item 456 iv. End Transaction

What happens if another user tries and reads item 123? Will it allow this (I would prefer that it does). Also in the above, if I perform two updates on item 123, will this cause an issue?

That depends on the optimistic/pessimistic level of the transaction. See this for more info: http://msdn.microsoft.com/en-us/library/system.data.isolationlevel.aspx http://goo.gl/c7x3P Also read my link in my above post (Transactions).

Sorry its taken so long to get back to you.

1. There are two scenarios. a. The user reads a row into a form for editing. He makes a heap of changes and then saves it back. Whilst the changes are being made, you dont want other users to change the row. If we let the user make the changes and then we get a concurrency exception, then the only fallback strategy would be to re read the row and get the user to start again

b. We might be processing a number of rows. Our normal code at the moment is

    Lock a Record
    Read a record
    Make changes
    Save Record back
    Unlock record

If we implement currency control, I guess we could do something like

    Read a record
    Make changes
    Save if back
    if (concurrency error then do it again)

Would this be the normal approach?

1. I have read the transaction information. I understand at a technical level. I'm not so sure how programmers use the transaction control. I did play around with it in ADO.net and had some troubles. I want the user to be able to read a record regardless of the transaction level. I want to wrap larger processing operations around a transaction. For instance, when we process an invoice we update a heap of tables. I want all these updates to be surrounded with a transaction.

When I played around in ADO.net the issue I had was to do with updating the same row twice from within the transaction

eg

Start Transaction Read Item 123. Update Sales and write back Read Item 123. Update Sales and write back <--- Lock occured. End Transaction

If we implement currency control, I guess we could do something like

    Read a record
    Make changes
    Save if back
    if (concurrency error then do it again)

Would this be the normal approach?

Yes it is.

You can either forget about concurrency, and then a row will retain the values of the last update executed.

Or use a concurrency control, to inform the user executing the last update that there was an update just executed on the row he was holding, so he can view the changes done by the other user, and decide whether to proceed with his changes (re-do the update), or accepts what has been already done.