Unwanted page locking 
Author Message
 Unwanted page locking

A friend of mine is writing a VB 4 application using the Jet DB engine.  It'll
be a multiuser DB, and she's having some locking problems that we can't figure
out.

Say user A is accessing the database to read some info (ie. this person isn't
adding to or modifying the DB).  Now user B wants to add to the DB.  She
can't, because it's locked.  To the best of our debugging, it seems that some
sort of page locking (2 KB pages?) is taking place, since depending on which
record user A is viewing, user B may or may not be locked out.

The point is, user B shouldn't be locked at all.  We even had user A open the
DB in read-only mode, but still user A is locked out.  User A wouldn't even be
modifying records, only adding new ones, so the problem of simultaneous
modifications on a single record is moot.

Are we missing something here?  We've been through 2 large VB books plus the
online help, and everything we've read indicates that nothing should be locked
unless we do a recordset.edit or recordset.addnew (in the case of pessimistic
locking) or recordset.update (in the case of optimistic locking).  User A's
program is doing none of those.  It's just reading through the database and
displaying information on the screen, yet as soon as user B tries a
recordset.update, a lock error occurs.  Specifically, Couldn't save; currently
locked by user 'name' on machine 'name'. (Error 3186).

If anyone has any suggestions, I'd be most appreciative.  TIA.

__
Devin Redlich



Mon, 31 Jan 2000 03:00:00 GMT  
 Unwanted page locking

Quote:
>Subject: Unwanted page locking

>Date: Thu, 14 Aug 97 22:28:14 GMT

>A friend of mine is writing a VB 4 application using the Jet DB engine.
>It'll
>be a multiuser DB, and she's having some locking problems that we can't
>figure
>out.

>Say user A is accessing the database to read some info (ie. this person
isn't

>adding to or modifying the DB).  Now user B wants to add to the DB.  She
>can't, because it's locked.  To the best of our debugging, it seems that
some

>sort of page locking (2 KB pages?) is taking place, since depending on
which
>record user A is viewing, user B may or may not be locked out.

>The point is, user B shouldn't be locked at all.  We even had user A open
the

>DB in read-only mode, but still user A is locked out.  User A wouldn't
even
>be
>modifying records, only adding new ones, so the problem of simultaneous
>modifications on a single record is moot.

>Are we missing something here?  We've been through 2 large VB books plus
the
>online help, and everything we've read indicates that nothing should be
>locked
>unless we do a recordset.edit or recordset.addnew (in the case of
pessimistic

>locking) or recordset.update (in the case of optimistic locking).  User
A's
>program is doing none of those.  It's just reading through the database
and
>displaying information on the screen, yet as soon as user B tries a
>recordset.update, a lock error occurs.  Specifically, Couldn't save;
>currently
>locked by user 'name' on machine 'name'. (Error 3186).

>If anyone has any suggestions, I'd be most appreciative.  TIA.

>__
>Devin Redlich


Devin,

Even when you are adding records, JET places 2KB lock on the
table/dynaset. What you need to do is to sprinkle your code liberaly with
Dbengine.Idle after opening your tables/dynasets because this frees all
locks . If you are using transaction procesing  beware that if you are
doing masive updates/inserts JET will lock the table. Also, you need to
call it after updates/inserts with Dbengine.Idle dbrefreshcashe to force
"write" to the table/dynaset and release all the locks.

When you are programming db apps with JET be prepared to deal with these
types of errors.

 1.) Update conflicts
 2) Insert Conflicts
 3) Delete conflicts

 For every JET I/O, you need error traps. ( On Error Goto <ErrorHandler>)
What you need to do is have a CASE statement to address all the errors
your sub/function might encounter. When you get a hold of that, you might
want to centralize your error handling.

Another solution is to have your records filled with 2kb worth of data so
you have a record locking. If   you decide to implement this, you need to
watch your Dynasets which may or may not have 2KB records .It wastes a lot
of space!

Still another solution is to write the locking scheme yourself-- a lot of
work!

Another posibility is to use a 3rd party database engine that employs
record locking scheme. ( CodeBase, Rocket (SDE), Btrieve/Smithware
controls)

 Here is a Function  from msaccess97 help file which will list most of the
errors for you. Run it in MSAccess ( or modify it in VB)  and it will
create all the error codes/descriptions for you.

The following procedure creates a table containing many of the error codes
and strings used or reserved by Microsoft Access and by the Microsoft Jet
database engine. Not all error codes are included in the resulting table,
as some exist outside the range of error codes evaluated by this procedure
(0 to 4500).

Function AccessAndJetErrorsTable() As Boolean
        Dim dbs As Database, tdf As TableDef, fld As Field
        Dim rst As Recordset, lngCode As Long
        Dim strAccessErr As String
        Const conAppObjectError = "Application-defined or object-defined
error"

        On Error GoTo Error_AccessAndJetErrorsTable
        ' Create Errors table with ErrorNumber and ErrorDescription
fields.
        Set dbs = CurrentDb
        Set tdf = dbs.CreateTableDef("AccessAndJetErrors")
        Set fld = tdf.CreateField("ErrorCode", dbLong)

tdf.Fields.Append fld
        Set fld = tdf.CreateField("ErrorString", dbMemo)
        tdf.Fields.Append fld

        dbs.TableDefs.Append tdf
        ' Open recordset on Errors table.
        Set rst = dbs.OpenRecordset("AccessAndJetErrors")
        ' Loop through error codes.
        For lngCode = 0 To 3500
                On Error Resume Next
                ' Raise each error.
                strAccessErr = AccessError(lngCode)
                DoCmd.Hourglass True
                ' Skip error numbers without associated strings.
                If strAccessErr <> "" Then

' Skip codes that generate application or object-defined errors.
                        If strAccessErr <> conAppObjectError Then
                                ' Add each error code and string to Errors
table.
                                rst.AddNew
                                rst!ErrorCode = lngCode
                                ' Append string to memo field.
                                rst!ErrorString.AppendChunk strAccessErr
                                rst.Update
                        End If
                End If
        Next lngCode
        ' Close recordset.
        rst.Close
        DoCmd.Hourglass False
        RefreshDatabaseWindow
        MsgBox "Access and Jet errors table created."

AccessAndJetErrorsTable = True

Exit_AccessAndJetErrorsTable:
        Exit Function

Error_AccessAndJetErrorsTable:
        MsgBox Err & ": " & Err.Description
        AccessAndJetErrorsTable = False
        Resume Exit_AccessAndJetErrorsTable
End Function

Hope this helps
George



Tue, 01 Feb 2000 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Unwanted page feed

2. Unwanted page break in Enterprise

3. How to lock a tab page

4. Lock Word document pages

5. Lock Pages:

6. Help: correction to aspx page, file locking

7. Page locking Jet 3.5

8. Jet Page Locking

9. Page vs Recordset Locking

10. Page Locking Using Datacontrol

11. Simulating Page Locks

12. Page Locking with Access 7.0

 

 
Powered by phpBB® Forum Software