Weird record level locking with a self join

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Paul H
    #1

    Weird record level locking with a self join

    I have an Employees table with the following fields:

    EmployeeID SupervisorID
    Fred Bob
    Bob
    John Bob
    Mary Bill
    Bill

    I have created a self join in the relationships window, with
    referential integrity and cascading updates set. My database settings
    are as follows:

    Default record locking: "no locks"
    Open db using record level locking: checked

    If I Change "Bob" to "Bobby" in the EmployeeID column I get the
    following error:
    "Could not update; currently locked. (Error 3218)"

    If I perform the same action with record level locking unchecked, the
    change "Bobby" propagates, as expected, to the SupervisorID column:

    EmployeeID SupervisorID
    Fred Bobby
    Bobby
    John Bobby
    Mary Bill
    Bill


    If I set Default record locking to "Edited Record" the above behaviour
    is the same.

    I simply can't get my head round this; I would have expected the
    opposite of what is happening. So my understanding is of the above
    behaviour is:

    If I select record level locking I can not edit the current record.
    If I select page level locking I can!! Huh!?! Surely the current
    record is part of the page!

    I just want to create a self join and cascade updates from EmployeeID
    to SupervisorID. But I also want to understand what is happening
    above.

    Paul
    Tags: None
    • Tom van Stiphout
      #2
      Re: Weird record level locking with a self join

      On Tue, 18 Mar 2008 10:25:48 -0700 (PDT), Paul H
      <google@comcraf t.freeserve.co. ukwrote:

      I'm going to avoid the real issue: Your EmployeeID and SupervisorID
      fields should be numeric - Long Integer, and no cascade update is
      needed. Names like "Bob" should go in a FirstName field.

      -Tom.

      >I have an Employees table with the following fields:
      >
      >EmployeeID SupervisorID
      >Fred Bob
      >Bob
      >John Bob
      >Mary Bill
      >Bill
      >
      >I have created a self join in the relationships window, with
      >referential integrity and cascading updates set. My database settings
      >are as follows:
      >
      >Default record locking: "no locks"
      >Open db using record level locking: checked
      >
      >If I Change "Bob" to "Bobby" in the EmployeeID column I get the
      >following error:
      >"Could not update; currently locked. (Error 3218)"
      >
      >If I perform the same action with record level locking unchecked, the
      >change "Bobby" propagates, as expected, to the SupervisorID column:
      >
      >EmployeeID SupervisorID
      >Fred Bobby
      >Bobby
      >John Bobby
      >Mary Bill
      >Bill
      >
      >
      >If I set Default record locking to "Edited Record" the above behaviour
      >is the same.
      >
      >I simply can't get my head round this; I would have expected the
      >opposite of what is happening. So my understanding is of the above
      >behaviour is:
      >
      >If I select record level locking I can not edit the current record.
      >If I select page level locking I can!! Huh!?! Surely the current
      >record is part of the page!
      >
      >I just want to create a self join and cascade updates from EmployeeID
      >to SupervisorID. But I also want to understand what is happening
      >above.
      >
      >Paul

        Comment

        Previous template Next
        Working...