Lock wait timeout of 1800 seconds expired (8812) - Forum - OpenEdge Pro2 - Progress Community

Lock wait timeout of 1800 seconds expired (8812)

 Forum

Lock wait timeout of 1800 seconds expired (8812)

This question is answered

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....

Dmitri Levin

Alphabroder

Verified Answer
  • 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
  • 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 


  • 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?

    Dmitri Levin

    Alphabroder

  • 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 


  • 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

  • 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....

    Dmitri Levin

    Alphabroder

  • 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 


  • 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.

    Dmitri Levin

    Alphabroder

  • 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 


  • 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....

    Dmitri Levin

    Alphabroder

  • 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 


  • Thanks. That is what I am looking for.

    Dmitri Levin

    Alphabroder