Consultor Eletrônico

Status: Unverified

FACT(s) (Environment):

MS SQL DataServer.

FACT(s) (Environment):

Progress 9.1C

SYMPTOM(s):

Under increased load, the SQL server dsv application with multiple session inserting records will hang, causing only one user able to insert a record into a table.

<file-name> in use by <user> on <tty>. Wait or choose CANCEL to stop. (2624)

The problem can be demonstrated by running one session loading records, after centain amount of time, error 2624 will appear on the screen.

No errors in dataserv.lg file

Problem can be observed by running SQL query analyzer, record insert contention can be seen.

Add "-Dsrv AUTOCOMMIT,1" to dataserver connection string, the problem disappears.

SQL server database migrated from Progress database in 9.1C, Progress_recid column was added and insert trigger created

Insert trigger in SQL server is created like following:

CREATE TRIGGER _TI_tab1 ON tab1 for insert as
if ( select max(inserted.PROGRESS_RECID) from inserted) is NULL
begin
update tab1 set PROGRESS_RECID = @@identity
where PROGRESS_RECID is NULL
select convert (int, @@identity)
end
go
CREATE UNIQUE INDEX tab1#_#progress_recid ON tab1
(PROGRESS_RECID)
go

Running Progress 9.1D but with the insert trigger to maintain progress_recid created in 9.1C, problem still exists.

If the insert trigger is dropped, the problem disappears.

Can not upgrade to 9.1D

CAUSE:

The insert trigger created by protomss utility in 9.1C to maintain progress_recid column is blocking on resources. Specifically, the UNIQUE constraint on the index progress_recid causes the lock wait on the second user.

FIX:

The contention problem on insert triggers has been addressed in 9.1D.

Manually modify SQL server database and insert triggers referencing the insert triggers created in 9.1D.

Please reference following solution for exact steps performed

P62712, How to manually replace pre-9.1D insert triggers with 9.1D insert triggers?