Hi there,

I am using an ORM layer to persist my memory objects on ElevateDB. And it is working just fine.

However, I have a problem with record locking in some very specific cases.

I wonder if there is any SQL command I could be using to request a lock to a specific record. I am not sure how to deal with that, so let me explain:

This ORM creates SQL statements that are sent over http. So I do not have the ElevateDB components at had to request directly a record locking.

I need for example to increment the Stock Quantity of a item, in the Stock table. This needs to be an "atomic" operation, and necessary to have a lock for just an instant.

Can it be done by SQL statements?

I cant change the behavior of this ORM, so I was first thinking in request a lock, use the ORM to change a record (it will end up generating a UPDATE statement) and then release the lock.I cant change the statements that the ORM is creating, but I can generate mine and send it directly if necessary.

Any ideas are appreciated. Thanks!

Eduardo


The only thing that comes to mind is to somehow wrap the update in a transaction.

Roy Lambert

I am already doing this.

However, if I have for example Stock.Available = 5

Then I start a transaction to update this number, and add 1 to it, what will block other process to do the same?

If happens to be at same time both will get a Stock.Available = 5. The first one that writes will change it to 6, and the other one will change to 6 instead 7.

I have not able to make this kind of test, but I need to find a way to make sure that it will not happen ever.


Roy Lambert wrote:

Eduardo


The only thing that comes to mind is to somehow wrap the update in a transaction.

Roy Lambert

Eduardo,

Currently there is no way to explicitly lock a row from PSM/SQL (Tim does have it in his stack of requests for sure .
As Roy said, starting a transaction is the way to achieve what you want.
Keep in mind that you must keep transactions short to ensure that you aren't blocking other sessions.
The scenario that you have described is not going to happen because when one session starts a transaction no other session can do the same until the first one is committed or rolled back. Thats why it is very important to keep them short.

--
Fernando Dias
[Team Elevate]

Eduardo

A more lowly way of achieving the same outcome is to have an "InUse" boolean column in the table, which is usually false.

If you write SQL to:

UPDATE Table SET InUse = true

Do other processing and update the table

UPDATE Table SET InUse = false

You only have to test the value of "InUse" and pause / wait if the answer is true in order to have the effect you desire.

Adam Brett wrote:

> Eduardo
>
> A more lowly way of achieving the same outcome is to have an "InUse"
> boolean column in the table, which is usually false.
>
> If you write SQL to:
>
> UPDATE Table SET InUse = true
>
> Do other processing and update the table
>
> UPDATE Table SET InUse = false
>
> You only have to test the value of "InUse" and pause / wait if the
> answer is true in order to have the effect you desire.

I was thinking that would work, except that an interruption of a
connection would leave it locked. I have in the past used a timestamp
for such things, so you lock it with the current time, and then you can
update anyway if the timestamp is 5 minutes old or something.

Trouble is, these things really need the ORM to do this stuff, and if
it can't handle "multi-threaded" operation then I think I'd want to
consider carefully if it was fit for the purpose.

--

Matthew Jones

Eduardo


That is an entirely different question, and can be a lot more complex. I know some systems that allow negative stock pretty much for this reason. Part of the answer lies in having a separate "in use" flag sort of like Adam suggests but I prefer a separate table into which you write the stock code of the item being updated. The process would go along the lines of:

1. Check stock levels
2. Check if the stock code is in the InUse table if so stop
3. If not there add it
4. Check stock levels again, if they've changed worry
5. If stock levels acceptable then update/downdate
6. Delete stock code from InUse table
7. Cup of tea


Roy Lambert

Roy Lambert wrote:

> 4. Check stock levels again, if they've changed worry

This of course can be managed by using the "rows changed" indicators,
where you do an "Set LockControl = true where LockControl = false", and
check that you changed it. If you didn't then you have to go around
again, as someone else beat you to it.

But to me, that's just a sign that you aren't doing it right. Either
you can have a transaction with the guarantees that brings, or you need
to design the system so that multiple users can be allocating stock at
the same time. Perhaps by having orders, so the stock level is "on the
shelf - sum of the orders". But again it comes back to that controlling
ORM I think - if it can't handle either scheme then you need to work
out what it can do.

--

Matthew Jones

Adam,

I am using this approach with a software written with DBISAM. Besides DBISAM has lock control by its components this particular software was based all in the SQL language and then I have created exactly what you said, an 'inuse" field to know that someone is using.

It works, it is not bad. For small/medium access it does work. Eventually with a high demand it can fail, since locking mechanism should be done closest possible to the OS.

This is probably the option I will have for my Orders table. However I am thinking in use a Semaphore table, where I add a record with "who is using what", after using it gets deleted, and then I can give some kind of user locking window administration.....


Adam Brett wrote:

Eduardo

A more lowly way of achieving the same outcome is to have an "InUse" boolean column in the table, which is usually false.

If you write SQL to:

UPDATE Table SET InUse = true

Do other processing and update the table

UPDATE Table SET InUse = false

You only have to test the value of "InUse" and pause / wait if the answer is true in order to have the effect you desire.

Matthew


>to design the system so that multiple users can be allocating stock at
>the same time.

<full pedant mode ON>

I'm just felling a bit awkward so I'm going to take issue with anyone allocating stock at the same time. This does not map to the real world and should never even be considered. You should never ever even consider updating / downdating stock simultaneously, it has to be a controlled sequential operation.

I write as not only a pedant but also someone who has had to sort out the actual physical mess in the stores and stock take vs perpetual inventory <VBG>

<full pedant mode OFF>

Roy