Solved

File Sharing Lock Count Exceeded

Posted on 2013-12-24
12
1,268 Views
Last Modified: 2014-01-25
I have logic that passes thru a local table with 300K records.  What the routine does is simple but it keeps stopping with the 'File sharing locks exceeded' when I get to around record 10,000.

I did some research about this issue on EE and added the statement


DAO.DBEngine.SetOption dbMaxLocksPerFile, 200000

At the top of the processing.  There was no change, the error still occurred around record 10,000.

I also saw that registry revision is another way of handling this issue but that wouldn't work because I would have to do it on every user machine.  There are close to 40 users.

When I first wrote the routine it processed all 300k records with no issues, this problem came later.

Since this is a local table there is no need for any locks on the table.  No one but the current user will access it.

I pretty much use the same open statement on all of my ADO files.  Is there something that I can do to make the open more suitable for this process.  I don't know the effect of the options.  Here is the open I use.

rsIn.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockPessimistic

Open in new window


I tried both optimistic and pessimistic locking with no change.

Here is the processing loop:

Dim CurrBRT As Long
Dim PrevBRT As Long
PrevBRT = 99999999

Dim BlockNum As Long
BlockNum = 0
'
Dim totRecs As Long
Dim recsRead As Long
Dim displayCount As Long
Dim displayMax As Long
Dim dispMsg As String

DAO.DBEngine.SetOption dbMaxLocksPerFile, 200000  ' or higher if necessary   'Microsoft DAO 3.6 Object Library

'wkDateStamp = Date
'wkDateTimeStamp = Now
'
selectString = "Select * from aSynch_Import_Raw " & _
               " Order By [ID] "
'
Dim rsIn As ADODB.Recordset
Set rsIn = New ADODB.Recordset
rsIn.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockPessimistic    'adLockPessimistic    'adLockOptimistic
'
'Debug.Print selectString
'
If rsIn.EOF Then
    MsgBox "No Payment Records to Process.  Requested operation is terminated."
    Exit Sub
Else
    If rsIn.RecordCount > 0 Then
        '
        rsIn.MoveLast
        rsIn.MoveFirst
        '
        totRecs = rsIn.RecordCount
        displayMax = 50
        '
        While Not rsIn.EOF
            '
            recsRead = recsRead + 1
            displayCount = displayCount + 1
            If displayCount < displayMax Then
            Else
                dispMsg = "Processing Rec " & Trim(Str(recsRead)) & " Of " & Trim(Str(totRecs))
                wkStatusRtn = SysCmd(acSysCmdSetStatus, dispMsg)
                displayCount = 0
                DoEvents
            End If
            DoEvents
            '
            CurrBRT = Nz(rsIn!BRT, 0)
            '
            If CurrBRT = PrevBRT Then
            Else
                BlockNum = BlockNum + 1
                PrevBRT = CurrBRT
            End If
            '
            rsIn!BlockID = BlockNum
            '
'            If Mid(Nz(rsIn!Period, 0), 5) = "1231" Then
                rsIn!PrincipalNum = convertImportDbl2DecStrToNumber(Nz(rsIn!PrincipalStr, 0))
                rsIn!InterestNum = convertImportDbl2DecStrToNumber(Nz(rsIn!InterestStr, 0))
                rsIn!PenaltyNum = convertImportDbl2DecStrToNumber(Nz(rsIn!PenaltyStr, 0))
                rsIn!OtherNum = convertImportDbl2DecStrToNumber(Nz(rsIn!OtherStr, 0))
                rsIn!AttyFeesNum = convertImportDbl2DecStrToNumber(Nz(rsIn!AttyFeesStr, 0))
                rsIn!LienNum = convertImportDbl2DecStrToNumber(Nz(rsIn!LienStr, 0))
                rsIn!EligExpNum = 0
 '           Else
 '               rsIn!EligExpNum = convertImportDbl2DecStrToNumber(Nz(rsIn!OtherStr, 0))
 '           End If
            '
 '           rsIn!SynchYear = Val(Mid(Nz(rsIn!Period, 0), 1, 4))
            '
            rsIn.Update
            '
            ' write payments deactivated event record
            '
            rsIn.MoveNext
        Wend
            
    End If
End If
'
rsIn.Close
Set rsIn = Nothing
Stop

Open in new window


Any ideas?
0
Comment
Question by:mlcktmguy
12 Comments
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39739190
I checked to make sure and I do have other processing loops that go thru over 300k records without this issue but this one throws the error every time.

One possibly key difference is that in this case the loop involves only one table.  I am passing thru a table and updating each records as I pass thru.

Very confused.
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 39739320
A question that you probably do already, but are you setting all recordset variables to nothing in all prior operations?

I also saw that registry revision is another way of handling this issue but that wouldn't work because I would have to do it on every user machine.  There are close to 40 users.

So I don't have to touch all machines I have this code in my DB that does the registry edit on DB startup:
Dim I As Long

I = RegKeyRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxLocksPerFile")
If I <= 15000 Then
    RegKeySave "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxLocksPerFile", "45000", "REG_DWORD"
End If

Open in new window


It's based off the registry editor code below. I found this somewhere on the internet and has been working fine for me.
Option Compare Database
Option Explicit

'reads the value for the registry key i_RegKey
'if the key cannot be found, the return value is ""
Function RegKeyRead(i_RegKey As String) As String
Dim myWS As Object

  On Error Resume Next
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'read key from registry
  RegKeyRead = myWS.RegRead(i_RegKey)
End Function


'returns True if the registry key i_RegKey was found
'and False if not
Function RegKeyExists(i_RegKey As String) As Boolean
Dim myWS As Object

  On Error GoTo ErrorHandler
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'try to read the registry key
  myWS.RegRead i_RegKey
  'key was found
  RegKeyExists = True
  Exit Function
  
ErrorHandler:
  'key was not found
  RegKeyExists = False
End Function

'sets the registry key i_RegKey to the
'value i_Value with type i_Type
'if i_Type is omitted, the value will be saved as string
'if i_RegKey wasn't found, a new registry key will be created
Sub RegKeySave(i_RegKey As String, _
               i_Value As String, _
      Optional i_Type As String = "REG_SZ")
Dim myWS As Object

  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'write registry key
  myWS.RegWrite i_RegKey, i_Value, i_Type

End Sub


'deletes i_RegKey from the registry
'returns True if the deletion was successful,
'and False if not (the key couldn't be found)
Function RegKeyDelete(i_RegKey As String) As Boolean
Dim myWS As Object

  On Error GoTo ErrorHandler
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'delete registry key
  myWS.RegDelete i_RegKey
  'deletion was successful
  RegKeyDelete = True
  Exit Function

ErrorHandler:
  'deletion wasn't successful
  RegKeyDelete = False
End Function

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 39739324
change the lock from pessimistic to optimistic
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39739362
Thanks for the responses, as I mentioned above I already tried switching between Optimistic and Pessimistic with no change.

jimpen:  

First question: Would a user have to have a certain level of authority to execute the code that revises the registry?  If so, it may work for me when I test it but won't work for all users in the production environment.

Is this code specific to operating system?

I cut and pasted your code into my app and got a runtime error.  I attached the message
 

EE Error
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39739375
The specific line of code that threw the error was:

  myWS.RegWrite i_RegKey, i_Value, i_Type

which is the last line in Sub RegKeySave
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39739399
First question: Would a user have to have a certain level of authority to execute the code that revises the registry?

I can't tell you. All our users are local admins on their workstations.

But the error is because it's trying to use VBS to modify the registry on a Win7 machine.

I've been using it on XP/2K3 machines up until now.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 75
ID: 39739587
Since you are using ADO (right?), I'm not sure DAO.DBEngine.SetOption dbMaxLocksPerFile applies anyway. And if it's not working, then the Reg entry is not going to work either.

Not sure what is going on ... sorry.

mx
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39739595
Thanks for taking an interest in this.

Very strange because it will periodically run thru all the records without issue.

When I first put the routine together and tested it, it ran to completion.

I was doing some other testing today and by mistake I hit the button to process the 300K records.  It ran to completion.

Tried it again and it threw the error at around rec 10,000.

Got out of the application re-booted the computer and it is throwing the error at 10,000.

I must be missing something obvious but I sure don't know what it is.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39739600
Well for right now, just go ahead and edit the registry manually and see if it works. If it does then we can see if we can find an automated way.
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39744996
I had to set this aside temporarily due to higher priorities.  Will follow up as soon as I get a chance.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39745044
Not a problem.
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39745093
Just wanted to keep you posted.  I appreciate your help and interest in my question. I think we'll get there eventually.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now