Powerhouse/AIX- MS-SQL Server locking issues

Fri Dec 2 18:12:24 CST 2005

Scenarios like this could be why SQLserver have supplied the autoincrement
option on the column. 

I did some testing once, my notes are below, but I don't recall how rigorous
the results were, only that the use was happy and did'nt contact me again
:-)

A summary of the required code for locking SQLserver rows at the procedural
level is below.  

Care must be taken with this approach for the following reason, if a
sequential read to table getnext is active on another screen it is likely to
use a full table lock, this will prevent the designer procedure lokem, see
example below, from reading the row. Therefore access to the 'get-next'
table should be kept to a minimum at all times.

If a second screen reads the table getnext by keyed/indexed access, and as
long as it is not reading the same row, there appears to be no access
conflict.  If a second screen reads the same row, the serializable tx will
wait until the row is available. This is because the row is regarded as
uncommitted and the serializable tx reads using 'repeatable read'.

If two screens attempt to run the designer procedure lokem, see example
below, a deadlock error will be issued to the screen which commits the
transaction latest.  

This should rarely happen if access to the 'get-next' table is kept to a
minimum at all times.

TRANSACTION lockem read write serializable

file getnext designer TRANSACTION lockem for query, process, update

proc designer lokem  nodata

begin

start transaction lockem

    GET getnext Via seg1 using "C"

    LET seg2 of getnext = t_display

    LET t_display = seg2 of getnext

    PUT  locktest

commit transaction lockem

display  t_display 

display seg2 of getnext

end

Regards, Joe. 

  _____  

From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
[mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On Behalf Of
Goparaju, Vidyasagar
Sent: 02 December 2005 14:51
To: Peter Bateman
Cc: powerh-l at lists.sowder.com
Subject: RE: Powerhouse/AIX- MS-SQL Server locking issues

Hi Peter,

Here is the example that I have been working on. 

I was able to get thru the 'DEADLOCK' issue with the following transaction
model. I have tried various models. But the following does not result in
DEADLOCK. But is not complete as per my requirement.

screen test transaction model dual

transaction myupdate READ WRITE 

file shipment_dates in tpsdb designer transaction myupdate

procedure initialize

begin

   start transaction myupdate

   get shipment_dates via file_no,tracing_Date_no using 1,1

   if accessok

   then begin

          info message=ascii(file_no) + "  " + ascii(time_updated) now resp

          let time_updated = time_updated + 1

          put shipment_dates

          commit

        end

end

build 

My requirement is when I run the same screen from two different sessions

simultaneously, second session should wait on the GET as it should be locked

by the first one. First one should release the record after the commit and

then second one grabs the record and increments the counter. We have this

type of processing everywhere in the application and LOCK before read and

Unlock/COMMIT after PUT was the only way to do it. As we don't have these

options on SQL Server it is a real pain.

Thanks for all the input and suggestions.

Sagar

TradePoint Systems 

-----Original Message-----
From: powerh-l-bounces+vgoparaju=tradepointsystems.com at lists.sowder.com
[mailto:powerh-l-bounces+vgoparaju=tradepointsystems.com at lists.sowder.com]On
Behalf Of Peter Bateman
Sent: Thursday, December 01, 2005 8:14 PM
To: powerh-l at lists.sowder.com
Subject: RE: Powerhouse/AIX- MS-SQL Server locking issues

                   Hi Sagar:

                 1)   SQL Server needs to have its log truncated frequently.

                     When it is full SQL Server waits for some process to
truncate it.

                     So what looks like a lock wait situation may in fact be
a log wait

                     situation.

       2)                   Try to the place puts to  the tables in the same
order in each 

                              screen of a  group of related  QUICK screens.

        3)                   Use AUTOCOMMIT on your lookups.

                      Good luck,

                      Peter

Hi 
Listers,

We have successfully migrated our application from VMS/RMS environment to
AIX/Oracle. Except some performance issues in some areas we look good there.

Now we are on to AIX/MS-SQL Server environment under the same version of
Powerhouse PH.8.43.D1 and running the same code with minor changes to fix
some compilation errors. 

We are having major problems with locking and deadlocks in this environment.

I have experimented with some transactions within Quick, but nothing really
works. Not sure whether we need any changes on the ODBC connection, database
end or Powerhouse.

Any suggestions would be greatly appreciated.

Thanks

Sagar

TradePoint Systems

  _____  

Free yourself from those irritating pop-up ads with MSN Premium:
<http://g.msn.com/8HMAENCA/2734??PS=47575> Join now and get the first two
months FREE*

STATEMENT OF CONFIDENTIALITY:
The information contained in this electronic message is intended for the
exclusive use of the addressee(s) and may contain confidential information.
If you are not the intended recipient of this email, be advised you have
received this message in error and that any use, dissemination, forwarding,
printing, or copying is strictly prohibited.  Please notify TradePoint
Systems LLC immediately at (603) 889-3200 and destroy all copies of this
message and any attachments.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20051203/40e1a7c1/attachment.html