By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,741 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Weird record level locking with a self join

P: n/a
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
Mar 18 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Tue, 18 Mar 2008 10:25:48 -0700 (PDT), Paul H
<go****@comcraft.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
Mar 19 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.