Skip to main content

Aculocks table becomes LOCKED unable to login users?

  • July 11, 2018
  • 5 replies
  • 0 views
A
  • Anonymous

HI All

We are using Acu4gl v9.3.2

A large client has a great number of Users logging in and we had instance of the Aculocks table becoming locked

It was so bad we had to revert back to v9.2.1

 

We have a stored procedure that I have a suspicion may have had a hand in.

  sp_AcuRemoveUnusedLocks_1

 

Not sure what info is relevant

    5 replies

    D

    HI All

    We are using Acu4gl v9.3.2

    A large client has a great number of Users logging in and we had instance of the Aculocks table becoming locked

    It was so bad we had to revert back to v9.2.1

     

    We have a stored procedure that I have a suspicion may have had a hand in.

      sp_AcuRemoveUnusedLocks_1

     

    Not sure what info is relevant

    Is it using a read next or read previous cobol read statement? If so if you didn't specify "with no lock" the read will lock all the records. That's back from when Acucorp was still an independent company. They changed the default from READ NEXT RECORD WITH NO LOCK to READ NEXT RECORD WITH LOCK when locking info not specified.
    D

    HI All

    We are using Acu4gl v9.3.2

    A large client has a great number of Users logging in and we had instance of the Aculocks table becoming locked

    It was so bad we had to revert back to v9.2.1

     

    We have a stored procedure that I have a suspicion may have had a hand in.

      sp_AcuRemoveUnusedLocks_1

     

    Not sure what info is relevant

    Hey JimmyBoy, what is happening that makes you think the aculocks table becomes locked? How did you determine the aculocks table was locked?

    Do you have a level 9 runtime trace file? That will show exactly what stored procedures are being executed.

    Usually the sp_AcuRemoveUnusedLocks_1 store procedure is implemented to RESOLVE connection issues due to stranded lock table entries. That is the most common reason for a failed connection - stranded lock table entries.

    Do you have an sp_AcuInit stored procedure? That is an optional procedure that will be called first upon connection. If there isn’t one then you’ll probably want to create one to execute sp_AcuRemoveUnusedLocks_1 upon each connection. Here’s how to do that:

    Execute the following SQL within the Microsoft SQL Query Analyzer:

    ----------------------
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    use master (or a specific database you are using)
    go
    create procedure sp_AcuInit
    as execute lockdb..sp_AcuRemoveUnusedLocks_1
    go
    grant execute on sp_AcuInit to public
    go
    ------------------------

    In this example lockdb is the name of my lock database. You will substitute the name of your lock database.

    If the Acu4GL stored procedures are not installed in the master database then create sp_AcuInit in the database(s) where they are installed.

    While most of SQL Server may not be case sensitive, the names of stored procedures must match case. Please make sure the stored procedure names are exactly as shown above.
    A
    • Anonymous
    • July 12, 2018

    HI All

    We are using Acu4gl v9.3.2

    A large client has a great number of Users logging in and we had instance of the Aculocks table becoming locked

    It was so bad we had to revert back to v9.2.1

     

    We have a stored procedure that I have a suspicion may have had a hand in.

      sp_AcuRemoveUnusedLocks_1

     

    Not sure what info is relevant

    Hi Doug, unfortunately thisis all somewhat retrospective in that it occurred back in 2016 as soon as we put them on version 9.2.3
    Due to the loking issues encountered we reverted back to 9.1.2.1
    A
    • Anonymous
    • July 12, 2018

    HI All

    We are using Acu4gl v9.3.2

    A large client has a great number of Users logging in and we had instance of the Aculocks table becoming locked

    It was so bad we had to revert back to v9.2.1

     

    We have a stored procedure that I have a suspicion may have had a hand in.

      sp_AcuRemoveUnusedLocks_1

     

    Not sure what info is relevant

    CMS_5F00_DATABASE_5F00_INFO.xlsx

    Hi Doug

    I hope you can see the xls attached

    attached is a capture of locks that were in place at the time.

    It got so jammed up that people could not log on

    D

    HI All

    We are using Acu4gl v9.3.2

    A large client has a great number of Users logging in and we had instance of the Aculocks table becoming locked

    It was so bad we had to revert back to v9.2.1

     

    We have a stored procedure that I have a suspicion may have had a hand in.

      sp_AcuRemoveUnusedLocks_1

     

    Not sure what info is relevant

    I looked at the CMS_DATABASE_INFO.xlsx but I do not have any expertise in deciphering whether there is a problem showing there.

    We have reports of a bottleneck in the AcuLocks table under heavy usage which turned out was caused by having Auto Close turned on. Turning Auto Close off restored normal performance. Auto Close is a SQL Server property which you can check in the SQL Server Management Studio; right click the database, select Properties, then go to Options. It's at the very top of the Other Options list. Auto Close should be set to False.

    Our technical database contains no other reports of misbehavior with the AcuLocks table.

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement