How to handle multiuser issues in Powerbuilder

[Karen]

I am not a Powerbuilder programmer. A company has recently installed a powerbuilder
app where I work that uses our SQL database. They recently informed me that
they will be implementing row-level locking on the main table because of
our high number of blocks during the course of the day in their software.
Having 20+ years of programming under my belt, I was wondering if Powerbuilder
has automatic record locking functionality, or if that must be programmed
in for a multi-user system? It seems to me that if they are going to add
this functionality, why was it not there to begin with? Thanks for all answers
in advance!!

[Joe \Nuke Me Xemu\ Foster]

"Karen" <kscer@hotmail.com> wrote in message <news:3c56c552$1@10.1.10.29>...

> I am not a Powerbuilder programmer. A company has recently installed a powerbuilder
> app where I work that uses our SQL database. They recently informed me that
> they will be implementing row-level locking on the main table because of
> our high number of blocks during the course of the day in their software.
> Having 20+ years of programming under my belt, I was wondering if Powerbuilder
> has automatic record locking functionality, or if that must be programmed
> in for a multi-user system? It seems to me that if they are going to add
> this functionality, why was it not there to begin with? Thanks for all answers
> in advance!!

Dunno if ProblemBuilder is "powerful" enough to handle this technique,
but using timestamp or "generation" fields works fine for me in other
languages, with absolutely no record or page locking needed whatsoever.
When retrieving a row you might want to update or delete, make sure to
also retrieve the timestamp or generation field as well, then make it
part of the WHERE clause or pass it to the stored proc, like so:

update mytable set f1 = 'larry', f2 = 'curly', f3 = 'moe',
generation = (mytable.generation mod 2000000000) + 1
where mytable.key = 123456 and mytable.generation = 789

If no row is modified, then someone or something else must have updated
or deleted the row "behind your back", and you can attempt to re-load
the row to see what the changes are.

Note that the timestamp or generation field should /not/ be part of the
primary key.

--
Joe Foster <mailto:jlfoster%40znet.com> Got Thetans? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!

[Tim Ellison]

What db server are you all using? There are possibly a number of issues
they may have that pertain to the manner in which they are accessing the
data.

If the db servre is SQL Server, then they need to turn AutoCommit ON,
otherwise, each Select statement becomes part of a single transaction (at
least until a commit in PB) which can definitely increase blocking.

The other thing that may help with reducing blocking on the main table is
using table hints when selecting. If I remember correctly, changing the
query to include a hint for the table (WITH NOLOCK) will not create a shared
lock on the table. Again, if autocommit is turned off, an exclusive lock
request (one that is required to perform an update) cannot happen until the
shared lock is released.

HTH

Tim Ellison


"Joe "Nuke Me Xemu" Foster" <joe@bftsi0.UUCP> wrote in message
news:3c5828e6@10.1.10.29...
> "Karen" <kscer@hotmail.com> wrote in message
<news:3c56c552$1@10.1.10.29>...
>
> > I am not a Powerbuilder programmer. A company has recently installed a
powerbuilder
> > app where I work that uses our SQL database. They recently informed me
that
> > they will be implementing row-level locking on the main table because of
> > our high number of blocks during the course of the day in their
software.
> > Having 20+ years of programming under my belt, I was wondering if
Powerbuilder
> > has automatic record locking functionality, or if that must be
programmed
> > in for a multi-user system? It seems to me that if they are going to add
> > this functionality, why was it not there to begin with? Thanks for all
answers
> > in advance!!
>
> Dunno if ProblemBuilder is "powerful" enough to handle this technique,
> but using timestamp or "generation" fields works fine for me in other
> languages, with absolutely no record or page locking needed whatsoever.
> When retrieving a row you might want to update or delete, make sure to
> also retrieve the timestamp or generation field as well, then make it
> part of the WHERE clause or pass it to the stored proc, like so:
>
> update mytable set f1 = 'larry', f2 = 'curly', f3 = 'moe',
> generation = (mytable.generation mod 2000000000) + 1
> where mytable.key = 123456 and mytable.generation = 789
>
> If no row is modified, then someone or something else must have updated
> or deleted the row "behind your back", and you can attempt to re-load
> the row to see what the changes are.
>
> Note that the timestamp or generation field should /not/ be part of the
> primary key.
>
> --
> Joe Foster <mailto:jlfoster%40znet.com> Got Thetans?
<http://www.xenu.net/>
> WARNING: I cannot be held responsible for the above They're
coming to
> because my cats have apparently learned to type. take me away,
ha ha!
>
>