Record Locking/SQL (2000 SR1/SQLS 2000)

Topic

New Reply

When a user opens a record on a form, I want to lock that record so no one else can edit it. In Ac2000, I can use the choices on the Options/Advanced tab, but of course that doesn’t work when the back db is SQL Server. Is it simply a matter of changing one of the db’s properties on the server (in the Management Console)? I’m 3 hours away so I’d prefer to arrive there with knowledge rather than get stuck, come home, and then ask you guys

TIA

James

Reply | Quote

Replies

I believe it depends on whether you are using ODBC linked tables or ADP. We use the former in most cases, and don’t do anything from a locking perspective. As a result you do get an occasional error message out of ODBC/SQL Server. I’m making a WAG at this point as I haven’t tested it, but at least in prior versions, ODBC mirrored the settings in the Options/Advanced tab and I don’t see any reason why they would have changed that. Further, I don’t believe there are any settings at the server or database level that control those kind of things. In SQL Server, I understand that the record locking strategy is set based on the connection that is being made and the access method. You might want to look at “Understanding Locking in SQL Server” in SQL Server Books Online. Is there some specific reason you want to lock a record being read? Usually if we do locking, it’s when a record is edited.

Reply | Quote

Sorry, I meant edited not just read…

I asked because the Ac2000 Help file says this:
>>>
Note When you edit data in a linked SQL database table by using ODBC, Microsoft Access doesn’t lock records; instead, the rules of that SQL database govern locking. In this instance, regardless of the record-locking setting you choose for your database, Microsoft Access always acts as though the No Locks setting has been selected.
<<<

Thoughts? Meanwhile I'll cvheck the Books Online you suggested.

James

Reply | Quote

Guess I should have looked at Access Help – I’ve gotten cynical about ever finding things in it I guess! I think the statement is probably correct however – I’ve been playing with editing of linked ODBC tables this morning in a project, and we get the message that a record has been edited by another user if you attempt to edit the same record twice in succession. But the error message is coming from SQL as an ODBC error message rather than as an Access error message. There’s a bunch of masking that goes on with ODBC error messages – there are some articles on TechNet about it and one in particular that has a bunch of complicated code so you can see the actual ODBC error message.

In any event, SQL will lock your record (or records) for you when you start editing, and other users will get an error if you leave things at the default. Is there a high probability of two users trying to edit the same record?

Reply | Quote

Not a high probability, no. The problem the customer was having was the one you found: unfounded error messages that the record had been edited by someone else (which it hadn’t). Result: inability to edit anything. I’m not fussed about an occasional user clash, but I do want to fix these erroneous errors (!). Is it OK if you open a different record then go back?

James

Reply | Quote

That seems to be about the only one we see as well – it happens pretty frequently when a user adds or edits a record and then tries to go back almost immediately and correct an error they made. It’s as if the change either hasn’t been completely written to disk in SQL, or the log hasn’t been created or something. I actually suspect it may have something to do with the ODBC driver and the MDAC version you are using as well. We are currently researching the issue, and I post something if I find out more about it. Please do the same if you discover a cure.

Reply | Quote

Many thanks — I will report back if I find anything. Hope you find a fix!

James

Reply | Quote

Remember that problem I had with records claiming to have been saved by another user? It is reportedly getting worse – I think my customers are getting annoyed by losing 20 minutes’ work when a record gets dumped. Do we have any idea what might be causing this problem? I had been led to believe that there were no problems using an MDB and linked tables to an SQL Server db, but *if* that’s the reason that we’re getting these errors, that’s a big problem in my book. Would these false errors still occur with an ADP? (And no, I really don’t want to go down that road if I can help it.)

Sorry to sound desperate but I thought I was going to get a happy customer by moving from Jet to SQL-S. Instead we have simply moved from one problem to another.

James

Reply | Quote

James:

I don’t think that it’s necessarily a MDB with linked tables to a SQL Server that’s rocking the boat, but rather how SQL Server is managing the locks. If you haven’t yet, do as Wendell suggested and check out the topic, “Understanding Locking in SQL Server” in SQL Server Books Online as a starting point. The short form is that it doesn’t necessarily lock only one record, but may lock a page, a series of 8 pages, i.e. an extent, the whole table, or the whole db!

So, for instance, you are editing record# 2 and SQL has locked #2 for you. I want to edit record# 3, but it is on the same data page as record# 2. If SQL has locked the whole data page that #2 is on3, then the record I want is locked as well!

Good luck!

Reply | Quote

Sorry, we haven’t had any luck yet in isolating what is going on, and other priorities keep bumping the problem into the background. Will be sure to post if we find anything. BTW, what do you mean about records getting dumped. We don’t see that happening – we get an error message, but then you can proceed and the record is saved.

Reply | Quote

Just a thought out of left field, which actually has nothing to do with the database being in SQL Svr or JET.

I have had a problem with ‘erroneous’ locks from a form – when the form uses a recordsetclone of the forms recordset. Unfortunately I have never found a ‘cure’ for the problem.

On another tac. Can you go into SQL Server and actually see who is locking what and when? This may give you an idea of what SQL Server is doing at least.

Reply | Quote

Jayden, I’m not using a clone (as far as I know!). Thanks though.

Regrettably the SQL Server concerned is a 3 hour drive away. You’re quite right, if I was there I might be able to see what was being locked, although right now I don’t know where I would look to find that information. At this stage I’m trying to learn and understand as much as I can before I make the trip.

James

Reply | Quote

By a record getting “dumped”, do you mean that they run into a locking conflict with another user and can’t save their work? If you’re using page locking instead of record locking, that’s entirely possible, as Shane has pointed out. However, there’s also something seriously wrong with an interface design if it takes 20 minutes to enter a record before it can be saved.

I’ve never seen this kind of problem with *either* an Access or a SQL Server back end, so it may be more an interface design issue than a problem with the database back end.

Reply | Quote

Firstly, thanks to you all for replying. I apologise for my slightly incoherent post yesterday.

Here’s a summary of the situation: my customers have 8 PCs with Ac2000 SR1. Until a couple of months ago, they were sharing an MDB stored on a central PC (not exactly a server, but it wasn’t being used for anything else), but repeated data corruption drove us to look at SQL Server as a back end. They now have Small Business Server 2000 installed as a proper server, and all appears to be well. The existing MDB now has linked tables to the tables on SQL Server.

There is a main menu form – some buttons for reports etc, plus a list box of jobs in hand. Doubleclicking a job opens that record in the data entry form, which is where we have the problem. Often, apparently (yesterday they said 4 or 5 times a day for each user, and that it’s getting worse; it didn’t do it at all for the first couple of weeks), when they have made some changes and click the Save & Close button, they get a ‘Write Conflict’ error along the lines that someone else has saved this record while they were editing it. This is entirely false. As a result, they cannot save the changes they have made (I take your point about 20 mins and the interface – perhaps we can come back to that later).

The form, for some reason, is set up as No Record Locks (on my copy – I may have changed this at the client), but the db default setting is Edited Record.

I’m sorry but I never did read “Understanding Locking in SQL Server” in SQL Server Books Online. I’m embarrassed to say that I don’t know how to find it. Please advise!

As Wendell has indicated, this is a problem he has seen under similar setups but we are unsure of the cause. I’m wondering whether it might have something to do with the fact that these DAO linked tables use *two* connections to SQL Server per user. I presume that re-engineering this db as an ADP would remove this record locking issue, but would no doubt introduce numerous other gotchas.

Many thanks for your input. I hope you can provide some further insights.

James

Reply | Quote

If DAO is all you’re familiar with, then don’t jump into an ADP yet.

If you can’t find it on your SQL Server CD, you can download SQL Server BOL from the Microsoft site. Be warned however, that it’s a huge download (about 35Mb, if I remember correctly).

I’m not sure what you mean by using two connections per user. Could you explain that, please?

Reply | Quote

I have used ADPs but not on a live project, only as a method of seeing data in an SQL Server db. I’d prefer to stay with DAO if I can.

Re: the SQL Server Books, I only have MSDE, so I guess it’s the big download for me. That could prove extremely tricky. Unless it’s possible for some kind soul to somehow extract the relevant section for me.

2 connections per user: Wendell wrote (12-Apr-02) “In addition, every object that is bound to a table will require two connections – no idea why. That makes using ODBC connections using the MSDB engine unusable for more than one or two users.”

I continue to appreciate your help with this. I’m probably going to need a bit of hand-holding to sort out this write conflict problem.

James

Reply | Quote

While you wait hours to download the SQL Server Books online, you might want to look at MS Knowledge Base article Q162361 and the articles it links to. It will give you the short version of record locking issues.

As to the two connections per user, that was based on monitoring the connections for users with one, two or three Access databases (all using ODBC connections), and we would see 2, 4 and 6 connections respectively. I haven’t checked this since we began working primarily with SQL Server 2000, but will try to do so today or tomorrow.

Finally, Charlotte is correct in being concerned if you users are taking 20 minutes from the time they begin to edit a record to the time they finish it. You should probably take a hard look at how the user interface works and see if you can’t streamline it, if for no other reason than if a workstation decides to go south a user may well have lost 20 minutes of work. How many concurrent users, and how many total users do you have on your system?

Reply | Quote

I’ve taken a look at the KB article you suggested (for which thanks), although it seems to be mainly dealing with the kind of blocking that occurs when a huge query is run. We’re not getting slowdowns, and unless I totally misunderstand I don’t think that’s the problem. There may however be an issue with record locking settings…

However, what is odd is that this problem did not appear at all for the first few weeks. Then it happened occasionally, and in the last fortnight it’s happening “several times a day to each user” and is “terrible”. My colleague who assisted with the install (being a network and NT Server guru) set up some Active Directory stuff there earlier this month and he suspects that may be involved.

I agree that 20 mins is a long time on one record, so I should explain. We took a conscious decision to have a main record screen where everything was visible at a glance, and I still believe that for this application the interface is appropriate. The users like it a lot for this reason (among others). The problem is that we have a memo field for notes: specifically, as this app is for a car body repair workshop, the memo field contains all the free text information about the work to be done: parts to be replaced, what to respray and so on. It’s this which can take them a while to research and key in. It’s not 20 minutes of typing. Then when it comes to printing a job card to work from, we put the vehicle and owner details at the top from the other fields and plop the memo contents at the bottom of the page with as much room as it needs. Simple and effective (and we had to keep it simple to keep the whole thing to a low budget, or there would have been no sale). So that’s the reason for the 10, 20 minutes on one record. But that’s not the problem. Even if I redid the whole thing so they never spent more than 3 mins on a record, they’d be just as angry when it fails to save their changes half a dozen times a day! We have 8 users – and probably 5 or more concurrent most days.

I’m still actively considering using an ADP, even if only as a stopgap. I have coded quite a bit in ADO before (plus there shouldn’t be too much actual ADO code needed anyway). Of course, if I do I’ll probably hit other conflicts and problems, but where would the fun be otherwise?

James

Reply | Quote

Putting everything on the main screen is a choice I would never have made, but since you made it already there’s no point in belaboring it. However, if the big problem is the memo field, then I see no way around that. If you allow them to have the form open and in edit mode for 20 minutes, I’m not surprised if they’re locking each other out., especially if the locks are using page locking.

This sounds more like a user training issue than a database issue, but you might want to try giving them an unbound popup form as a scratchpad to use in recording/editing their notes while they’re doing their research. Then, you could have a button to add the notes to the memo field in the main form. Lock the memo field on the main form and make them do all entry and edits through the popup form. That would prevent a lot of the lockouts because the only time the main form record will need to be in edit mode is when the memo field is being written back to the main record. Save the main form record as soon as they hit the memo field to prevent the kind of lockouts you’re getting now.

Reply | Quote

OK, hands up. Maybe having everything on the one form is not the right way of doing this. I can’t see another way right now though, and it wouldn’t solve what seems to me to be the problem: if SQL Server *is* using page locking, then conflicts will happen. All I want to do is tell it to use row/record locking, if that’s possible. And I’d be staggered and bewildered if it wasn’t.

You suggested:
> you might want to try giving them an unbound popup form as a scratchpad

Funnily enough, that’s what I’ve done already – that Edit button opens an unbound form as a dialog. The field value is passed back and forth via a global variable. Thanks for the pointer, maybe you’re filling in a vital gap in my knowledge here, but I don’t quite understand about “the only time the main form record will need to be in edit mode is when the memo field is being written back to the main record.” Surely the main form record is in edit mode all the time it’s open? I’m hoping I’ve misunderstood and that we’re tantalisingly close to a fix of sorts.

Thanks again

James

Reply | Quote

It depends on how you’ve set up your locking. For example, if your form actually contains a bunch of subforms, then not all the tables will be in edit mode, only the one currently being edited. The issue of *when* a lock is actually placed on a record has been written about at length, and one of the neatest tricks is that if you wrap an edit in a transaction, the record isn’t locked until you issue the Update call. That does a lot to reduce potential locking conflicts. Also, if you save the current record (assuming it’s dirty, of course) before you pop up your dialog, then nothing should be in edit mode unless you code is putting it there.

Access is inherently multiuser. Unfortunately, VBA isn’t, so you have to learn to program specifically to handle situations like this.

I just thought of another method–use Notepad. You can pop it up using the Shell command, so it should be possible to export the contents of the current memo field to a text file. Then use Shell to open that file in notepad. That would give them a scratchpad with the ability to save the file if anything went wrong. Of course, your programming would be more complicated, but that’s what they pay us for, right?

Reply | Quote

Thanks Charlotte…

>>
It depends on how you’ve set up your locking. For example, if your form actually contains a bunch of subforms, then not all the tables will be in edit mode, only the one currently being edited.
<>
The issue of *when* a lock is actually placed on a record has been written about at length, and one of the neatest tricks is that if you wrap an edit in a transaction, the record isn’t locked until you issue the Update call. That does a lot to reduce potential locking conflicts.
<>
Also, if you save the current record (assuming it’s dirty, of course) before you pop up your dialog, then nothing should be in edit mode unless you code is putting it there.
<>
I just thought of another method–use Notepad.
<>
Of course, your programming would be more complicated, but that’s what they pay us for, right?
<<

If I was being paid to fix this I might be slightly less stressed about it

Thanks again

James

Reply | Quote

We’re in different conversations. The dialog *I* was talking about isn’t the one you’re launching now, it’s the unbound dialog I suggested. The one you’re launching now creates another edit on the same record.

Reply | Quote

Sorry Charlotte, I know you’re suggesting an unbound dialog. But I think that is what I’m doing now: if the user clicks the Edit field, I copy the memo value from the field on the form into a global variable, open a form (for which RecordSource is blank) with one big field (which says Unbound and for which the ControlSource is blank) and set the form field to equal the variable. Then when they close the form I reverse the process. That is an unbound form, right? The error message I mentioned, I got when I closed this form and the code tries to update the form field on the main record form.

Is this what you meant?

Many thanks – I’m trying to learn!

James

Reply | Quote

Yes, that’s what I meant. But did you save the record on the bound form before popping up the unbound form? If not, that may be where your conflict is coming from. Alternatively, there may be a problem with the way you’re updating the main form record.

If you simply hide the popup form, control should return to the calling form but the value in the popup will still be available. At that point, the calling form itself can grab the value from the unbound form, insert it into its own record and close the unbound form.

Reply | Quote

My code doesn’t save the record before popping up the unbound dialog. We only get an error if the record is saved by some other method (either by Records/Save Record or if the user prints an Estimate for instance) before they edit the memo. Thanks for your advice. I’ll try the hide form technique you suggested along with some other possibilities (e.g. subform), test it well here before I update the users’ apps. Early reports suggest that using Zoom (instead of my Edit button and popup) has helped, so I’m hopeful. I’ll let you know how I get on.

James

Reply | Quote

Very interesting thread. Here are my 2 cents. I recently have been having some issues with a memo field when I upsized an app to SQL 7. My resolution includes adding a tab to the tab form, putting a subform on the new tab which is bound to a new comments table where I record the date each comment is entered and each comment gets its own record. The comments table has a one-to-one relationship to the parent table – in my case employees. Though you may not want a separate record for each comment it might help your situation since once the user enters the subform they are no longer on the main form record.

Reply | Quote

Neat idea, Paul. Say I was to set up a one-to-one related table with just the memo field. If I put a bound subform onto a fourth tab on my tabbed form (see screenshot from earlier post) as you suggest – let me understand this – if the user starts to edit the data in the subform are you saying that the record on the main form is no longer in edit mode? Does that unlock that record or something until the user comes out of the subform, even though the form is still open?

Or could I equally well just open the new bound memo form as a modal form (ie not a subform)?

James

Reply | Quote

Hi James
Wanted to update you on our issues with error messages – I think we probably have a different problem. It sounds to me from the thread that your issues is probably a genuine locking problem, related in large part to the edit process your users employ. In our case, the problem is temporary, and the user can actually save the changes the second time. My gut suspicion is that either ODBC is slow getting a response back that the record has been saved (our problem always occurs with inserting new records and then promptly editing them), or SQL is taking a longer than expected time to save the log file info on the record.

On another subject, you asked about how to tell what locks were in place. In Enterprise Manager you can view the locks in a database by user. It takes some detective work to determine what record in a table is being locked, but the overall view should let you decide whether the same record is being locked by multiple users.

Reply | Quote

Thanks Wendell. In a funny way it’s a bit of a relief if your locking problems are not the same as mine, as I’m starting to be more confident that mine is an interface-weirdness-induced problem rather than an SQL Server one. Thanks for the info on Enterprise Manager and for all your help on this problem. I hope I’m on the home straight now…

James

Reply | Quote

The parent form record is saved when you move to the subform, and vice versa, at least it is with a jet backend. I can’t swear the same is true of SQL Server because i’ve never really noticed.

Reply | Quote

It is my understanding that once you leave the form and focus shifts to the subform the record on the main or parent form is no longer in edit mode so therefore should not be locked. I went with the subform for consistency since the other tabs contain subforns as well in my app. A modal pop up form should work as well but I would test thoroughly whichever way you decide to go.

Reply | Quote

Interesting… thanks for your advice!

James

Reply | Quote

It is a little curious that the problem didn’t show initially, but I’m not sure how making changes to the AD could cause it. More likely, IMHO, is user acceptance and use growing over time. That is, I’m betting the problem was always there, but not manifested until people began using the new app more regularly. Still, if your friend has a vague feeling it’s something in the Active Directory, slaughter a chicken, sprinkle holy water, dance around a bit and hope for the best! Best of luck!

Reply | Quote

I must admit it seemed unlikely to me, but you never know. I trust this guy, but on this occasion his hunch may be wrong.

James

Reply | Quote

Hey James,
I just linked up an “MDE” to a SQL 2000 DB using ODBC. It is working great, although I did have locking problems like you mention when I first started working with it. I am not sure if it was mentioned earlier so I apologize up front.

Any record I created using the MDE and SQL showed up as locked (Changes made by another user). However, this was on a test DB and no one else had touched it. If I went direct into the table and tried to edit the record, it also said it was locked. What I found was that there is a problem with “Bit” fields in SQL. The tables will lock a record when a null value is written to the bit field. Make sure all of your tables that have bit fields are having valid values written to them at record creation time. You can also set the default value in the table to 0 or 1 whatever the case is. Hope this helps.

Thanks,
Mark

Reply | Quote

Thanks Mark, that’s an interesting one – I do have two Bit fields in the table concerned, both AllowNulls and are set by the values of tickboxes. I’ve have thought that if this was my problem it would be happening all the time though. Does it do the same with an MDB?

James

Reply | Quote

Hi James,
I have found that “Allow Nulls” cannot be set to true for bit fields. Without valid values being written to the bit fields in a record, you will get sporadic (frustrating and un-repeatable!) locking problems. But most of the time you will not be allowed to edit a record with those values set for null. Try setting the default value of those fields to 0 or 1, which ever is the correct way. Or you can write a value to them at record creation time.

Thanks,
Mark

Reply | Quote

Thanks Mark. I’ll set Allow Nulls to No and Default Value to 0. The quest continues…

James

Reply | Quote