Removing a Clustered Index (1 Viewer)

SQL_Hell · Aug 14, 2008

Yep set it to 'yes'

MSAccessRookie · Aug 14, 2008

SQL_Hell said:
Yep set it to 'yes'

It is done and will be tested as soon as we can do so, although I do not understand how this will resolve the issue (I need to be able to explain the reasons for another test to management).

SQL_Hell · Aug 14, 2008

When the DBA said there was deadlocking what other processes was involved in the deadlock? there is always 2 or more.

Are there other applications using this database or just your access front end?

MSAccessRookie · Aug 14, 2008

SQL_Hell said:
When the DBA said there was deadlocking what other processes was involved in the deadlock? there is always 2 or more.

I will have to get back to you with the specifics, but I know that there are other people using the same table via a different (read only) query.

Are there other applications using this database or just your access front end?

Only one application uses the database, but up to 25 people at a time are able to use it (Split database with each user having their own FE), and between 5-10 of them are involved in the testing.

I hope these answers are what you wanted

georgedwilkinson · Aug 14, 2008

Just butting in with a little of my experience and research. I haven't really been keeping track of the whole thread but, did you know:

1. When you open a recordset in Access (whether you know the recordset is open or not) against a SQL Server database, Access will quickly load the first several records, as needed. It will then load the rest of the records at its leisure until the entire recordset is read (whether you ever look at the records or not).

2. SQL Server (many DBMSs) does you a favor by locking all the records in a query (aka result set) until all the records have been retrieved to prevent modification of that data after you get your first record and before you've gotten your last.

3. Adding 1 and 2 together creates tons of opportunities for lockups and deadlocks, especially in Access applications connected to SQL databases that are used by many users and has a lot of records in a lot of tables on a lot of forms that all display at the same time, whether you want it to happen or not.

The solution is to filter the data on your forms before they open (i.e. use a query that limits the result set instead of the whole table). This will reduce the lazy loading of entire tables from SQL Server and will allow more people to hit the database without waiting for each other's locks.

As far as the deadlocks, the same thing generally applies but you really need to find the cause of the deadlock. Applying the fix will reduce the frequency of deadlocks but won't eliminate them.

Banana · Aug 14, 2008

This may be specific to SQL server, but does selecting "No Locks" for query helps any at all?

MSAccessRookie · Aug 14, 2008

Banana said:
This may be specific to SQL server, but does selecting "No Locks" for query helps any at all?

What do you mean by that?

Banana · Aug 14, 2008

In Access's query builder, if you open properties windows (you have to click on gray area to actually bring up the query's properties as it just show field properties by default), there is a property "Locking Type" where you can choose Edited Records, Table (?), or No Locks.

In my case (MySQL), I specify No Locks for any read-only recordsets, and Edited Records for any recordsource. However, unlike SQL Server, InnoDB does not require a lock to read the row as it uses MVCC to sidestep that problem entirely.

MSAccessRookie · Aug 14, 2008

Banana said:
In Access's query builder, if you open properties windows (you have to click on gray area to actually bring up the query's properties as it just show field properties by default), there is a property "Locking Type" where you can choose Edited Records, Table (?), or No Locks.

In my case (MySQL), I specify No Locks for any read-only recordsets, and Edited Records for any recordsource. However, unlike SQL Server, InnoDB does not require a lock to read the row as it uses MVCC to sidestep that problem entirely.

The query already had "No Locks". The attachment shows the properties window so you can see the rest of them.

Pat Hartman · Aug 14, 2008

Locks are defined server-side rather than in Access. Generally, you want to use optimistic locking. That prevents most deadlocks because the record isn't locked until it is about to be updated. This does result in messages warning about a record being changed by a different user but in most applications, this problem is rare. Deadlocks occur most often when programs need to update several tables and program 1 does it A then B and program 2 does it B then A. The answer to that is to be consistant with multi-table updates.

As to the bound/unbound issue, if you create your bound forms correctly and use filtered recordsources, you will not have problems with deadlocks so using unbound forms is simply overkill and defeats the entire purpose of using Access. You would be better off with WinForms.

georgedwilkinson · Aug 14, 2008

The "Record Locks" property ONLY applies to writing the record, not to reading the recordset. See my post above.

datAdrenaline · Aug 15, 2008

>>>> This may be specific to SQL server, but does selecting "No Locks" for query helps any at all? <<<<
>>What do you mean by that?<<

No Locks = Optimisitic Locking ... the dbms locks the record prior to attempting the write of your changes.

Edited Record = Pessimistic Locking ... the dbms locks the record as soon as you edit a field/column value.

-----

Its been said ... but I will throw my twist on it ... I BROWSE records via a Passthru query (typically with a form/subform arrangement .. sorta looks like a spreadsheet) , I edit records via a form that is bound to a recordsource (an SQL Statement) that returns just ONE record ... I design the form with a recordset similar to this ...

SELECT * FROM SomeTable WHERE 1=0

That way I have access to the fields during design time ... then during runtime I use a procedure to open my form and set the recordsource to a specific record....

Code:

DoCmd.OpenForm strFormname, , , , , acHidden
With Forms(strFormname)
            
    'Set the forms record source and turn on the form
    .RecordSource = "SELECT * FROM SomeTable WHERE PKID = " & SelectedID
    .Visible = True
    .SetFocus
            
End With

...

Just some more info to ponder ...

SQL_Hell · Aug 15, 2008

georgedwilkinson said:
As far as the deadlocks, the same thing generally applies but you really need to find the cause of the deadlock. Applying the fix will reduce the frequency of deadlocks but won't eliminate them.

.

I agree with this completely, you would not believe the amount of times I seen people say "oh just add no lock to your query" this is not a solution at all... it's a workaround and a crap workaround at that.

Using no locks opens yourself up to dirty reads and on a system where people are constantly updating records this is a bad idea.
The best way is to re-design the queries so they are more effiecient and if that doesn't work then there is always the new shapshot isolation mode to try.

MSAccessRookie · Aug 15, 2008

Code:

[COLOR=black][FONT=Verdana]DoCmd.OpenForm strFormname, , , , , acHidden[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]With Forms(strFormname)[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]'Set the forms record source and turn on the form[/FONT][/COLOR]
[COLOR=black][FONT=Verdana].RecordSource = "SELECT * FROM SomeTable WHERE PKID = " & SelectedID[/FONT][/COLOR]
[COLOR=black][FONT=Verdana].Visible = True[/FONT][/COLOR]
[COLOR=black][FONT=Verdana].SetFocus[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]End With[/FONT][/COLOR]

I liked your code sample above, but I am not sure if I understand the best way to implement it in my code (below - Beginning of Code Attempt). Any additional advice would be appreciated.

Code:

[COLOR=black][FONT=Verdana]Private Sub ResearcherID_DblClick(Cancel As Integer)[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]Dim rst As Recordset[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana][COLOR=red][B]' DoCmd.OpenForm "fmrPeople", acNormal[/B][/COLOR][/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana][COLOR=red][B]DoCmd.OpenForm strFormname, , , , , acHidden[/B][/COLOR][/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]If IsNull(Me.ResearcherID) Then[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana][COLOR=red][B]' If the field on the main form is BLANK, open the Add Contact[/B][/COLOR][/FONT][/COLOR]
[B][COLOR=#ff0000]' Form and start at the next available record (ADD A RECORD)[/COLOR][/B]
 
[/FONT][/COLOR]DoCmd.GoToRecord , , acNewRec[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]' Set the default date to the current date[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]' and Position the cursor on the Title Field[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana][Forms]![fmrPeople].Form.DateAdded = Date[/FONT][/COLOR]
[COLOR=black][FONT=Verdana][Forms]![fmrPeople].Form.Title.SetFocus[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]Else[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana][COLOR=red][B]' If the field on the main form is not BLANK, open the Add Contact[/B][/COLOR][/FONT][/COLOR]
[B][COLOR=#ff0000]' Form pointing at the selected record (UPDATE A RECORD)[/COLOR][/B]
 
   [COLOR=red][B]{ NOT SURE WHAT TO DO HERE }[/B][/COLOR][/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]Set rst = Forms!fmrPeople.Form.RecordsetClone[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]rst.FindFirst "[Person_ID] = " & CStr(Me.ResearcherID.Column(1))[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]If Not rst.NoMatch Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Forms!fmrPeople.Form.Bookmark = rst.Bookmark[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Else[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]MsgBox CStr(Me.ResearcherID.Column(0)) & " Not Found!"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End If[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]' Set the default date to the current date[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]' and Position the cursor on the Title Field[/FONT][/COLOR]
[COLOR=black][FONT=Verdana][Forms]![fmrPeople].Form.DateAdded = Date[/FONT][/COLOR]
[COLOR=black][FONT=Verdana][Forms]![fmrPeople].Form.Title.SetFocus[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]rst.Close[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Set rst = Nothing[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]End If[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana][COLOR=red][B].Visible = True[/B][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][COLOR=red][B].SetFocus[/B][/COLOR][/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]

SQL_Hell · Aug 19, 2008

Did you try my suggestion of setting data entry to yes??

Rather than try and code a very similar end result in VBA, the idea is to stop you returning all the records when you open the form, which should stop the locking

MSAccessRookie · Aug 19, 2008

SQL_Hell said:
Did you try my suggestion of setting data entry to yes??

Rather than try and code a very similar end result in VBA, the idea is to stop you returning all the records when you open the form, which should stop the locking

My apologies for not responding. That was a good try, but what it did was get only one record. While this was great for the add a new record feature, it did not work with the Modify a record option, since there was no recordset to find the selected record in.

SQL_Hell · Aug 19, 2008

Ok no probs,

I would have buttons on the form to put the form in different modes, for example add new record button, search for record button (here you can do the modify record bit, datAdrenaline's example would be good for this)

Basically you want to avoid returning the whole record set as I think this is what is causing you problems.

Just out of interest and because i dont think anyone has asked this question, how many records in the underlying table?

MSAccessRookie · Aug 19, 2008

SQL_Hell said:
Ok no probs,

I would have buttons on the form to put the form in different modes, for example add new record button, search for record button (here you can do the modify record bit, datAdrenaline's example would be good for this)

I had buttons there and I was asked to remove them. Apparently, some of the users thought that "Add Record" referred to writing the current contents of the Form to the database, and they kept losing updates.

Basically you want to avoid returning the whole record set as I think this is what is causing you problems.

I totally agree. I also found some articles describing how SQL server handles Access Query vs SQL Server View implementation. Apparently, when you implement an Access Query, you have several types of write locking choices available (the one we use is "No Locks", which does not lock the record until a write is requested). On the other hand, an SQL Server View request for data locks the table until all of the requested data is transferred.

If this is true, then this is most likely the source of the problem, and the answer lies in finding a way to determine of the table is locked before the write and to delay the write (retrying "n" times before timing out) until the locking status has passed.

Just out of interest and because i dont think anyone has asked this question, how many records in the underlying table?

The table is what I would describe as small to medium sized and currently contains about 9000 records that are about 320 characrters allocated for each (Plus one varchar(Max) (for comments) that is rarely used. The actual usage is an average of 100 characters or less.

Thanks again for responding so quickly. See my replies above.

SQL_Hell · Aug 19, 2008

To be honest mate, someone has told you to remove the buttons and now your trying to do everything with the one form and by the sounds of it your data is quite wide even if you dont have many records.

I would tell them that you are going back to the old way because this is causing performance issues, and a simple bit of user training can't be too much to ask can it?

Maybe you could even make your buttons look like the default record selectors then they hopefully wont know the difference.

MSAccessRookie · Aug 19, 2008

SQL_Hell said:
To be honest mate, someone has told you to remove the buttons and now your trying to do everything with the one form and by the sounds of it your data is quite wide even if you dont have many records.

I would tell them that you are going back to the old way because this is causing performance issues, and a simple bit of user training can't be too much to ask can it?

Maybe you could even make your buttons look like the default record selectors then they hopefully wont know the difference.

I wish I had that flexibility, but as a contractor, I am bound to the wishes of my client. I recommended the buttons in the beginning, and programmed the button to do what it was supposed to do, but in hte end, the users felt the need to remove it.

I think my ultimate answer lies in identifying whether a record is already locked (no matter what the reason) before an attempt to write it is made.

Removing a Clustered Index (1 Viewer)

SQL Server DBA

AWF VIP

SQL Server DBA

AWF VIP

AWF VIP

split with a cherry atop.

AWF VIP

split with a cherry atop.

AWF VIP

Attachments

Super Moderator

AWF VIP

AWF VIP

SQL Server DBA

AWF VIP

SQL Server DBA

AWF VIP

SQL Server DBA

AWF VIP

SQL Server DBA

AWF VIP

Similar threads

Users who are viewing this thread