Thread: For Tom Lane

For Tom Lane

From
"rubensoda@inwind.it"
Date:
From      : "Tom Lane" tgl@sss.pgh.pa.us
To          : "gabriele zelasco" rubensoda@inwind.it
Cc          : pgsql-general@postgresql.org
Date      : Thu, 26 May 2005 17:50:29 -0400
Subject : Re: [GENERAL] Locking rows

> "gabriele zelasco" <rubensoda@inwind.it> writes:
> > I would like to start a transaction with a sql function.
> > When user press "edit" button on my form, i would lock the current row.
> > After user has modified data on form, pressing "save" button I would save t=
> > he modified row by sql update function and so commit.
>


> This is widely considered a very bad way to design an application.
> Consider what happens when the user leaves for lunch, or otherwise
> lets the app sit for a long time.  See the list archives for prior
> discussions of the issue.
>
> But in any case, the answer to your question is to use "SELECT FOR
> UPDATE" to retrieve the row.  And you can't start a transaction
> inside a function, because by definition you'll already be in one.
> 
>             regards, tom lane
>

Thanks for answer Tom

"Consider what happens when the user leaves for lunch"

Well, I've already thought about it.But I'm working with
VS2003 and disconnected dataset.. so when user edit data
he's modifying an "old" disconnected row, while real updated row
is in the database..
So my strategy would be (as I already written):

1. refresh data recalling current row from database to the form's fields
2. lock the row
3. update modified data in the database through stored procedure (function)
4. commit and unlock the row

Have you another idea that could work better with disconnected objects ?



the answer to your question is to use "SELECT FOR UPDATE" to retrieve the row

Well, I've created a sql function with just sql command :
select * from table where condition FOR UPDATE
but it doesn't lock the row !

If I write "begin;" before command , inside function,
I get the error (begin is not allowed...)

So what I have to do if I would lock a row from inside vb net code?
Thanx in advance



____________________________________________________________
Navighi a 4 MEGA e i primi 3 mesi sono GRATIS.
Scegli Libero Adsl Flat senza limiti su http://www.libero.it



Re: For Tom Lane

From
Scott Marlowe
Date:
On Fri, 2005-05-27 at 09:57, rubensoda@inwind.it wrote:

>
> Thanks for answer Tom
>
> "Consider what happens when the user leaves for lunch"
>
> Well, I've already thought about it.But I'm working with
> VS2003 and disconnected dataset.. so when user edit data
> he's modifying an "old" disconnected row, while real updated row
> is in the database..
> So my strategy would be (as I already written):
>
> 1. refresh data recalling current row from database to the form's fields
> 2. lock the row
> 3. update modified data in the database through stored procedure (function)
> 4. commit and unlock the row
>
> Have you another idea that could work better with disconnected objects ?

While this ensures that the update is atomic, it doesn't ensure that no
one else is trying to edit it at the same time.

What you might want to do is either optimistically lock it, or use
application level locking.  To use optimistic locking, you'll need to do
something like make an md5 of all the fields being edited, then, right
before you write back the data, check to see if the md5 you created at
the beginning still matches by re-reading the data and md5ing it again.
If it doesn't match, then you can throw a "mid air collision" error, so
to speak, and tell them that the record changed underneath them, or do
some kind of merging / or whatnot.

If you want to do application level locking, then create a field and use
that for locks.  Just make it a timestamp field and put in the current
time value when the lock is taken.  When the predetermined timeout
occurs, the user lock is removed by the next person to access it, or
offer them chance to, or email the original locker, etc...  Handle it
the way you want or need to.

Re: For Tom Lane

From
"Jeff Eckermann"
Date:
"Scott Marlowe" <smarlowe@g2switchworks.com> wrote in message
news:1117557422.20484.35.camel@state.g2switchworks.com...
> On Fri, 2005-05-27 at 09:57, rubensoda@inwind.it wrote:
>
>>
>> Thanks for answer Tom
>>
>> "Consider what happens when the user leaves for lunch"
>>
>> Well, I've already thought about it.But I'm working with
>> VS2003 and disconnected dataset.. so when user edit data
>> he's modifying an "old" disconnected row, while real updated row
>> is in the database..
>> So my strategy would be (as I already written):
>>
>> 1. refresh data recalling current row from database to the form's fields
>> 2. lock the row
>> 3. update modified data in the database through stored procedure
>> (function)
>> 4. commit and unlock the row
>>
>> Have you another idea that could work better with disconnected objects ?
>
> While this ensures that the update is atomic, it doesn't ensure that no
> one else is trying to edit it at the same time.
>
> What you might want to do is either optimistically lock it, or use
> application level locking.  To use optimistic locking, you'll need to do
> something like make an md5 of all the fields being edited, then, right
> before you write back the data, check to see if the md5 you created at
> the beginning still matches by re-reading the data and md5ing it again.
> If it doesn't match, then you can throw a "mid air collision" error, so
> to speak, and tell them that the record changed underneath them, or do
> some kind of merging / or whatnot.

The ODBC driver uses the ctid value to check whether a record has changed;
an updated row will always have a new ctid.  That would probably be the most
economical way to check.

>
> If you want to do application level locking, then create a field and use
> that for locks.  Just make it a timestamp field and put in the current
> time value when the lock is taken.  When the predetermined timeout
> occurs, the user lock is removed by the next person to access it, or
> offer them chance to, or email the original locker, etc...  Handle it
> the way you want or need to.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>