Kbase P27459: Receiving error 2624 with FOR EACH ... NO-LOCK accessing SQL Server database
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
9/12/2008 |
|
Status: Verified
SYMPTOM(s):
Error 2624 under heavy load
<file-name> in use by <user> on <tty>. Wait or choose CANCEL to stop. (2624)
Under increased load, the SQL server application with multiple session inserting records will hang,causing only one user able to insert a record into a table.
The problem can be demonstrated by running one session loading records, after certain amount of time, error 2624 will appear.
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.
FACT(s) (Environment):
MS SQL DataServer.
Progress 9.1C
Windows
MS SQL
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.
Upgrade to Progress 9.1D and redo migration.
After the migration, verify the insert trigger is created like following:
create trigger _TI_tab1 ON tab1 for insert as if ( select
PROGRESS_RECID from inserted) is NULL begin update t set PROGRESS_RECID =
i.IDENTITYCOL from tab1 t JOIN inserted i ON t.PROGRESS_RECID_IDENT_ =
i.PROGRESS_RECID_IDENT_ select convert (int, @@identity) end
If upgrading to 9.1D is not possible the problem can be worked around by performing the following:
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?