Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 8 of 8 total
Thread C/S question
Thu, Mar 23 2006 9:59 PMPermanent Link

"Ralf Bertoldi"
Hello,

I don't know how to manage this behaviour in C/S:

myTable:
Field1: id -autoinc (from 1 -100)
Field2: mayvalue - string (anything...)


Client 1 (live query or table):
'SELECT mytable WHERE field1=10'
myQuery.edit;  <-- record locked

Client 2 (live query or table):
'SELECT mytable WHERE field1=20'
myQuery.edit;  <-- record locked

Client 3 (live query or table):
'SELECT mytable WHERE field1=30'
myQuery.edit;  <-- record locked


Now Client 1 would like to post some values to the table...

'starttransaction...'
'UPDATE mytable SET field2='some value' WHERE (field1 IN (10,20,30))'

That would raise an error.... because the record's are locked...

So, how could make Client 1 sure that he has write access to the
record's 20 + 30 before he starts's his transaction?

It's kind of trial and error to start a transaction and wait...
Possible to make sure that it's possible ton have write access before
Client 1 start's to lock and start anything?


TIA

Ralf
Fri, Mar 24 2006 1:44 AMPermanent Link

Eryk Bottomley
Ralf,

> I don't know how to manage this behaviour in C/S:

I would probably just switch the application to optimistic locking mode
- then clients 2 and 3 don't hold any record locks so they don't block.

Eryk
Fri, Mar 24 2006 1:25 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Ralf,


<< Now Client 1 would like to post some values to the table... >>

Why not just do the update directly in the record buffer that is being
edited in the live query and Post ?  Or am I not understanding the issue ?

<< So, how could make Client 1 sure that he has write access to the record's
20 + 30 before he starts's his transaction? >>

I'm still trying to understand why Client 1 has the 10 record locked to
begin with.... Smiley

<< It's kind of trial and error to start a transaction and wait... Possible
to make sure that it's possible ton have write access before Client 1
start's to lock and start anything? >>

If you use an UPDATE statement, it will essentially use a transaction
anyways.

--
Tim Young
Elevate Software
www.elevatesoft.com

Sat, Mar 25 2006 8:26 PMPermanent Link

"Ralf Bertoldi"
Tim Young [Elevate Software] wrote:
> Why not just do the update directly in the record buffer that is
> being edited in the live query and Post ?  Or am I not understanding
> the issue ?

sorry, maybe that was an incorrect example...


Think of following...
a table of parts. Every part has a field called "instock" (boolean)

Now client no.1 would like to switch the "instock" field of a lot of
records.

There is a major rule: "All or none"

At the same time client no.2 is editing one of the parts..

I know that it would be possible to call the update statement and when
it fails "none" will be the result...  in this simple example that
would be a perfect way...

But if the whole example would be more complex it would make sense to
have a look at other record's if they are locked and in edit mode...

So client no.1 would know "Don't start what you are planning, you can't
bring it to the end because anybody is editing a record to where you
would like to post anything..sooner or later..."

At the moment I handle this (a lot of tables are involved) inside a
transaction with rollback.. normally, no user is running into the
rollback..  but in theory it could happen.. so I'm just thinking of
this..

maybe there would be another way that I didn't see until now...

regards,
ralf
Sat, Mar 25 2006 8:33 PMPermanent Link

"Ralf Bertoldi"
> I would probably just switch the application to optimistic locking
> mode - then clients 2 and 3 don't hold any record locks so they don't
> block.
>

Eryk,
good idea. I never had a look at this..

but mostly I really need the locking feature. If user no.1 is editing
anything no one else is allowed to edit the same record...

Thanks,
ralf
Sat, Mar 25 2006 10:45 PMPermanent Link

"Robert"

"Ralf Bertoldi" <bertoldi@gmx.net> wrote in message
news:D9CEF1C6-17A3-41ED-9499-6B0CC3867477@news.elevatesoft.com...
>
> but mostly I really need the locking feature. If user no.1 is editing
> anything no one else is allowed to edit the same record...
>

It makes sense theoretically but (depending on the application, of course)
optimistic works great. For the rare case when the user updates a record
that has been updated by another station, you can let them know that is the
case, and have them re-enter the update.

Robert

Mon, Mar 27 2006 12:10 PMPermanent Link

Chris Erdal
"Ralf Bertoldi" <bertoldi@gmx.net> wrote in news:C129BDB0-D7C1-4511-B0FE-
04957FCCCE5B@news.elevatesoft.com:

> maybe there would be another way that I didn't see until now...
>

Add an "options" table and add to it the stock reference, the number you're
thinking of removing and your userid.

Anyone wanting to remove stock must then read the amount in stock and
subtract the sum of the options on that article in the options table. They
can continue if the result is positive, otherwise they get an error.

When a user confirms or aborts a sale, he/she removes all their own rows in
the options table.

No-one blocks anyone else, and if the stock-management is efficient almost
no-one ever gets an error (there are almost never more options on an
article than the number in stock).

(on re-reading I see you talked about yes/no for in stock, rather than a
quantity, but the idea may still be OK)
--
Chris
Mon, Mar 27 2006 3:00 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Ralf,

<< So client no.1 would know "Don't start what you are planning, you can't
bring it to the end because anybody is editing a record to where you would
like to post anything..sooner or later..." >>

Frankly, it is just as quick to simply try the transaction and rollback as
necessary then it is to test/acquire all of the necessary record locks up
front.

--
Tim Young
Elevate Software
www.elevatesoft.com

Image