Lock wait timeout of 1800 seconds expired (8812)

Posted by Dmitri Levin on 13-Feb-2018 14:56

Dear All

What could be the reason for a locking problem in pro2sql on Windows sql side?

Lock wait timeout of 1800 seconds expired (8812)

That is what we found in the log file replproc20180213-1.log

How could we find which database this error is from?

I assume replication agent does not lock any records in the main Progress database.

Then it should be either pro2sqldsb or SQL schema holder....

OpenEdge Pro2 - Forum
Posted by mfurgal on 13-Feb-2018 15:00

Hi Dmitri:

The most likely cause is a table lock on the MSSQL side.  Make sure all client have their ISOLATION LEVEL set to READ UNCOMMITTED.  Also, make sure there are no unique indexes (other than the prrowid index) on the MSSQL side.

Mike
-- 
Mike Furgal
Director – Database and Pro2 Services
PROGRESS Bravepoint
617-803-2870 


All Replies

Posted by mfurgal on 13-Feb-2018 15:00

Hi Dmitri:

The most likely cause is a table lock on the MSSQL side.  Make sure all client have their ISOLATION LEVEL set to READ UNCOMMITTED.  Also, make sure there are no unique indexes (other than the prrowid index) on the MSSQL side.

Mike
-- 
Mike Furgal
Director – Database and Pro2 Services
PROGRESS Bravepoint
617-803-2870 


Posted by Dmitri Levin on 13-Feb-2018 16:29

Thank you Mike. We do not have any indexes at all on MSSQL side, other than the ones pro2sql has.

Do you know if there is a way to list  ISOLATION LEVEL of all connected clients on SQL server database side?

Posted by mfurgal on 13-Feb-2018 16:32

I do not know how to look at the isolation level for connected clients.  But I believe you can set a default in the ODBC DSN so it does not need to be set explicitly for each connection each time they connect.

Mike
-- 
Mike Furgal
Director – Database and Pro2 Services
PROGRESS Bravepoint
617-803-2870 


Posted by vprasad on 13-Feb-2018 22:08

1) "Lock wait timeout of 1800 seconds expired (8812)" - This error is from MSSQL

To control wait for initial lock , Keep below connection parameter in .pf file

-Dsrv PRGRS_NATIVE_LOCKWAIT,<n> # Controls wait for initial lock. Time in milliseconds.

2)To enable isolation level for connected clients, Keep below connection parameter in .pf file

-Dsrv TXN_ISOLATION,1   # Sets the default level to Read Uncommitted

4)Below wiki page might help

wiki.progress.com/.../viewpage.action

Posted by Dmitri Levin on 14-Feb-2018 13:47

vprasad: pro2sql product comes from PSC with those parameters:

 -Dsrv TXN_ISOLATION,1

 -Dsrv PRGRS_NATIVE_LOCKWAIT,500

Mike, our MSSQL clients do not use ODBC. I can either check clients connection type on MSSQL side or I guess I can check _Lock on OE DataServer for MSSQL side....

Posted by mfurgal on 14-Feb-2018 13:52

Dmitri:

These Dataserver settings are for the client writing to MSSQL and will not solve the problem.

The problem is that the pro2 client (thread) is trying to write to the MSSQL database to create a record, but some other process using the MSSQL database is doing a table lock because that clients ISOLATION LEVEL is wrong.  If the clients connecting to MDSQL to read the data are not ODBC, then what clients are they and can you have them set their ISOLATION LEVEL to READ UNCOMMITTED?

Mike
-- 
Mike Furgal
Director – Database and Pro2 Services
PROGRESS Bravepoint
617-803-2870 


Posted by Dmitri Levin on 14-Feb-2018 14:49

Thanks Mike. I understand all of that and already start working with our MS folks to press them with ISOLATION LEVEL :)

I also set up a batch job on pro2sql Windows side to check _Lock table every 5 min. So far I did not catch any locks. Do you think that approach will work with fdm4repsh database? If not I will think of an other way to catch big fish. Obviously no one will confess here of using the wrong ISOLATION LEVEL.

Posted by mfurgal on 14-Feb-2018 14:56

Are you looking for _Lock on the OpenEdge database?  Or on the MSSQL database?  There is no value for _Lock on OpenEdge as that is not where the issue is.

Mike
-- 
Mike Furgal
Director – Database and Pro2 Services
PROGRESS Bravepoint
617-803-2870 


Posted by Dmitri Levin on 21-Feb-2018 16:49

I am looking for locks on MSSQL database of course. When I query _lock on our_db_name_repsh ( Schema Holder ), will I see anything interesting. I mean will I see any locks on MSSQL database? I can probably test it by locking something in MSSQL and checking _lock in Schema Holder....

Posted by mfurgal on 22-Feb-2018 08:02

Dmitri:

The schema holder is an independent OE databases.  The _lock on this database will only show you activity on this OE database.  It will not show you anything related to the MSSQL database.  The purpose of this database is to do schema mapping and that is it.  You need to use the native MSSQL tools to see the locks on the MSSQL database.

Mike
-- 
Mike Furgal
Director – Database and Pro2 Services
PROGRESS Bravepoint
617-803-2870 


Posted by Dmitri Levin on 23-Feb-2018 16:47

Thanks. That is what I am looking for.

This thread is closed