Urgent! Cannot insert new records in SQL table #2.

Imho still the same problem: locking.

But without locking into it directly, it's hard to tell what's happening.

@ste5an,
What info do you need to look at?
sp_who2?
Thanks,
Ben

Some Experts have all the luck of the world...getting answers accepted when problem is not resolved...
Anyway..in other question i have suggested that you go through SSMS and perform CRUD operations ==> CREATE/ INSERT /UPDATE/DELETE...if everything was OK then you would know that the MsSQL database is good....if not probably you have greater issues maybe hardware related.

Lets start in SSMS.
1) Open two query windows on the database in question.
2) Issue the INSERT command which reports the timeout or add new row in Edit mode
3) Issue the sp_who2 in the 2nd query window during the time insert command waits

sp_who2 must show what process blocks your insert command or the new row saving. Then you may analyze the process which blocks the insert. It has to contain the Program name, workstation name, etc.  The query posted into the previous question may show the command which blocks your insert.

Inserts are not allowed e.g. when Access is reading the data. If the read operation takes long time then insert must wait for an exclusive lock so it is displayed as blocked process.

Hi Experts,

I have currently someone looking at my pc/server.
Will keep you posted.

Thanks,
Ben

Hi Experts,

After working several hours experimenting different approaches (including docmd.movelast on open event of report which didn't work), this is what the guy came to the conclusion...

https://stackoverflow.com/questions/12026199/ms-access-holds-locks-on-table-rows-indefinitely
 
this is your problem i see he used the same solution to make a vew on the table with nolock
 
he has a reference to a page explaining the drawback of using nolock - here it is. when you have time and gedild look at it.
https://blogs.msdn.microsoft.com/davidlean/2009/04/05/sql-server-nolock-hint-other-poor-ideas/
 
 
just to make you feel good, we use nolock a lot here.

Thanks,
Ben

Use a better archtecture Instead of NOLOCK. This includes using snapshot data sets where possible. Also only read the data you need. This means using read-only views for displaying data and views which only select the (single) row to edit using a selection table. Use passthrough queries for report data, by e.g. calling stored procedures which generate the report data. And the most important step is to look at the used queries and add appropriate indices.

I'm doing Access/SQL Server applications for almost 20 years. I never needed NOLOCK.

Hi ste5an,

This includes using snapshot data sets where possible

Its an Access report, no option to select snapshot.

How do you change this for a read-only view?

SELECT     ID, SNV_ID, DateEntered, VisitDateInt, Client_Last_Name, Client_Last_Name_Init, Visit_Date, Visit_Date_Init, Date_Of_Birth, Medical_Record_Number, Shift_From_Hour, Shift_To_Hour, 
                      Purpose_Of_Visit_Goal, Temp, Pulse, RR, BP, O2_Saturation, O2_In_Use, Respiratory_Lung_Sounds, Respiratory_Breathing_Pattern, Bipap, Cpap, Vent, Vent_BIPAP_Settings, 
                      Cardiovascular_Skin_Color, Skin_Temp, Cardiovascular_Edema, Neuro_Alert, Neuro_Oriented, Neuro_Self_Directing, Neuro_Dev_Delay, Neuro_Deficits, Neuro_Notes, GI_Oral_Tube_Feeding, 
                      Current_Feeds, Gastric_Residuals_Present, Residual_Volume, MD_Called, Abdominal_Assessment, Feeding_Tolerance, GU_Incontinent, Number_Of_Diaper_Changes, 
                      Urine_Stool_Volume_Description, Bedbound, WheelChair, Ambulatory, Mobility_Aids, Contractures, Musculoskeletal_Notes, Pain_Score, Pain_Notes, Medication_Changes, Medication_Changes_2, 
                      Integument_Skin_Assessment, Skin_Care_Measures_Taken, Home_Environment_Safety, Treatments_Administered, Patient_Response, Instructions, Understanding, Plan_Goal, 
                      Nurse_Signature_Last_Name, Signature, Date_Signed, Nurse_Name_Stamp_SNV, Nurse_User_ID_num_SNV, Medication_Changes_Yes, Acknowledge_Accuracy, Trach, CCM_Informed, Tube_Type, 
                      Reviewed_Status, Reviewed_By, Reviewed_stamp, Client_First_Name, Client_First_Name_Init, Nurse_Signature_First_Name, Patient_Unable_To_Sign, Reason, Shift_From_Minute, 
                      Shift_To_Minute, Shift_From_Init, Shift_To_Init, Status, ts, SNVNUM
FROM         dbo.Skilled_Nursing_Visit_Note WITH (NOLOCK)

Select allOpen in new window

Thanks,
Ben

The report is based on the following

SELECT SNV_Printed_Qry.*, vnl_SNV_Printed_History.ClientLastName AS CLN, vnl_SNV_Printed_History.ClientFirstName AS CFN, vnl_SNV_Printed_History.VisitDate AS VD, vnl_SNV_Printed_History.ShiftFromHour AS SFH, vnl_SNV_Printed_History.ShiftFromMinute AS SFM, vnl_SNV_Printed_History.ShiftToHour AS STH, vnl_SNV_Printed_History.ShiftToMinute AS STM FROM SNV_Printed_Qry INNER JOIN vnl_SNV_Printed_History ON SNV_Printed_Qry.SNV_ID = vnl_SNV_Printed_History.SNV_ID WHERE (((vnl_SNV_Printed_History.NoPrint)=0 Or (vnl_SNV_Printed_History.NoPrint) Is Null)); 

Select allOpen in new window

SNV_Printed_Qry is the following.

SELECT S.*, Int([Visit_Date]) AS IntVisitDate, H.PrintedDate, H.PrintedBy, H.ID AS PrintedHistoryID, H.ReviewedDate, H.ReviewedBy, H.VendorsID, Mid([Client_Last_Name],1,1) AS L, Mid([Client_First_Name],1,1) AS F, DLookUp("CountOfPN","SNVNotesQry","SNV_ID = '" & [S].[SNV_ID] & "'") AS CountOfPN, DLookUp("CountOfNotes","SNVNotesQry","SNV_ID = '" & [S].[SNV_ID] & "'") AS CountOfNotes, H.NoPrint, TimeValue(CDate(TimeSerial(Nz([Shift_To_Hour]),Nz([Shift_To_Minute]),0)-TimeSerial(Nz([Shift_From_Hour]),Nz([Shift_From_Minute]),0)+1))*24*60 AS Duration, (Abs(DateDiff("n",TimeSerial(Nz([Shift_From_Hour]),Nz([Shift_From_Minute]),0),TimeSerial(Nz([Shift_To_Hour]),Nz([Shift_To_Minute]),0)))/120)-Nz(DSum("CountOfNotes","SNVNotesQry","SNV_ID = '" & S.SNV_ID & "'"),0) AS MissingNotes
FROM vnl_Skilled_Nursing_Visit_Note AS S INNER JOIN vnl_SNV_Printed_History AS H ON S.SNV_ID = H.SNV_ID
ORDER BY S.SNVNUM;

Select allOpen in new window

vnl_SNV_Printed_History is the following

SELECT     ID, SNV_ID, PrintedDate, PrintedBy, ReviewedDate, ReviewedBy, VendorsID, ClientLastName, ClientFirstName, ShiftFromHour, ShiftToHour, ShiftFromMinute, ShiftToMinute, VisitDate, DateEntered, 
                      Initial, NoPrint, L, F, CountOfPN, CountOfNotes, SNVID, Duration, MissingNotes, ts
FROM         dbo.SNV_Printed_History WITH (NOLOCK)

Select allOpen in new window

vnl_Skilled_Nursing_Visit_Note  is the SQL in previous comment.

What are your recommendations?

Thanks,
Ben

I prefer stored procedures. I would shift the entire query to SQL SERVER into a stored procedure and call it by a passthrough query. Especially as you do some DLookup's in the query, which are pretty slow in comparison to a JOIN. So they maybe the real reason for the long lock period you see on SQL Server side.

"Read-only views": You link views into Access without specifying the PK during linking. When using these linked tables, set the recordset type to snapshot and fetch defaults to false (forms). In reports set Record Locks to No locks.

"The report is based on the following":  WHERE vnl_SNV_Printed_History.NoPrint = 0 OR vnl_SNV_Printed_History.NoPrint IS NULL looks like an model issue. Why tri-state? And as it looks like status, it should be value encoded to avoid NULL/tri-state.

p.s. I need to understand this:
"just to make you feel good, we use nolock a lot here." and "Skilled_Nursing_Visit_Note". I would expect an application supporting nursing to be critical. When using NOLOCK you may get wrong data or no data, how do you handle the risk of harming people?

NOLOCK is OK in the most of cases and namely for reporting. Some academic examples do present incorrect data retrieval or even infinite loops caused by nolock but that's not the real life obviously...

Snapshot has much higher requirements for tempDB space and the server RAM.

If you use NOLOCK hint then you know you may retrieve data which were changed or which do not exist any more... If you use locking or snapshot then the change or deletion may also happen before the report is delivered so what's the difference?

The "tristate" values in WHERE clause do not affect the query performance significantly.

Stored Procedures instead of direct queries do have an advantage of the simple update possibility during the run-time, of course, with certain restrictions.

@pcelba

I'm not talking about snapshot isolation.
Snapshot is the dataset type (DAO), which loads the entire set first. In opposite to Dynamic or Dynaset, which loads only the data necessary. E.g. a continuous form loads only the rows to view and keeps longer locks due to the open dataset, when it's Dynamic or Dynaset.

"NOLOCK is OK in the most of cases": In an application where human life is probably at risk?

"If you use NOLOCK hint then you know you may retrieve data which were changed or which do not exist any more..."
Much worse, it may not return existing, committed data.

"If you use locking or snapshot then the change or deletion may also happen before the report is delivered so what's the difference?"
Then the database returns a consistent view of the committed data. I

"Tristate": I said model issue.

"Stored Procedures instead of direct queries": Do you mean ad-hoc queries? The problem is the OP is running the queries in Access using ACE.

Hi Experts,

@Ste5an, @pcelba.

1- The database we are talking about is a reporting database only, no users manually adding/editing records at all, so the issue of data not being accurate is not relevant.

(The database actually resides online in Caspio, we export from there all new info into an Access temp file, and then use Access queries to import into SQL, having an Access FE app to print out reports).

2- The issue is caused by opening a report with many records, today I have tested both the NoLock and the read only view, and both resulted in the same issue I originally posted, see attached what I saw on SSMS at the time I opened the report, however when tried to replicate this issue hour later i couldn't, strange no?

3- I know there is a lot we can do to improve this system, however at this thread I want focus on simply resolving this locking issue.

In reports set Record Locks to No locks

This is how it was set.

Thanks,
Ben
Untitled.png

Especially as you do some DLookup's in the query, which are pretty slow in comparison to a JOIN. So they maybe the real reason for the long lock period you see on SQL Server side.

I removed all the Access functions from the query, and now the SNV_Printed_Qry looks like this.

SELECT S.*, H.PrintedDate, H.PrintedBy, H.ID AS PrintedHistoryID, H.ReviewedDate, H.ReviewedBy, H.VendorsID, H.NoPrint
FROM vnl_Skilled_Nursing_Visit_Note AS S INNER JOIN vnl_SNV_Printed_History AS H ON S.SNV_ID = H.SNV_ID
ORDER BY S.SNVNUM;

Select allOpen in new window

Let see if this solves the problem.

Thanks,
Ben

@Ste5an, so the DAO snapshot (tx for the explanation) is of the similar danger level as NOLOCK... If we have all data on the client then they surely don't contain the latest commits from other users. But they are maybe consistent (depends how the updates are managed and what constraints are defined).

I am saying that because there are many scenarios where the NOLOCK cannot cause any significant problem. Reporting is one of them, web applications are another example. The data integrity must be ensured on save operation only. Reading via several different caches in the web application can cause more surprises.

I am not working on the application having millions write operations per second and we are using NOLOCK in each SELECT query. We and our clients did not observe any problem caused by NOLOCK. The C# application running under IIS lost the control or strayed a few times per year (and Microsoft knows why there is the recycling period). The biggest problems are causing browsers...

I cannot explain the one hour delay for NOLOCK taking into place from point 2 in the previous message.

Hi ste5an,

After some fiddling with your queries

Appreciate that.

you also join the history table twice, which is not necessary

Good catch!
Fixed that by basing the report on the following.

SELECT S.*, H.PrintedDate, H.PrintedBy, H.ID AS PrintedHistoryID, H.ReviewedDate, H.ReviewedBy, H.VendorsID, H.NoPrint, H.ClientLastName AS CLN, H.ClientFirstName AS CFN, H.VisitDate AS VD, H.ShiftFromHour AS SFH, H.ShiftFromMinute AS SFM, H.ShiftToHour AS STH, H.ShiftToMinute AS STM
FROM vnl_Skilled_Nursing_Visit_Note AS S INNER JOIN vnl_SNV_Printed_History AS H ON S.SNV_ID = H.SNV_ID
WHERE (((H.NoPrint)=0 Or (H.NoPrint) Is Null));

Select allOpen in new window

Also your time arithmetic is overkill. You need a view or stored procedure on SQL Server like

Here is the thing, when basing the report on an Access query it opens immediately but ASYNC_NETWORK_IO on SSMS takes longer to go away (if by using either NoLock or Read-Only it does not poses a lock then I would not care much about it).

However if I convert this query to a pass-through and base the report on that, then it takes longer for the report to open, even with 100 records which is a common scenario here and users will not accept that delay.

stored procedure on SQL Serve

Perhaps this may help, can you guide me on this?

Chose only the columns needed.

Mostly are used in this report, don't have the time to analyze this now, unless this is the culprit for the lock.

Thanks,
Ben

However if I convert this query to a pass-through and base the report on that, then it takes longer for the report to open

BTW, I realized this is true even if selecting just one record (preview), and its really slow.

Thanks,
Ben

However if I convert this query to a pass-through and base the report on that, then it takes longer for the report to open, even with 100 records which is a common scenario here and users will not accept that delay

.
Execute that query in SSMS and post the query as well as the actual execution plan (as .sqlplan file).

The query happens to be exactly the same.

SELECT S.*, H.PrintedDate, H.PrintedBy, H.ID AS PrintedHistoryID, H.ReviewedDate, H.ReviewedBy, H.VendorsID, H.NoPrint, H.ClientLastName AS CLN, H.ClientFirstName AS CFN, H.VisitDate AS VD, H.ShiftFromHour AS SFH, H.ShiftFromMinute AS SFM, H.ShiftToHour AS STH, H.ShiftToMinute AS STM
FROM vnl_Skilled_Nursing_Visit_Note AS S INNER JOIN vnl_SNV_Printed_History AS H ON S.SNV_ID = H.SNV_ID
WHERE (((H.NoPrint)=0 Or (H.NoPrint) Is Null));

Select allOpen in new window

Thanks,
Ben

The first column of your primary key in vnl_SNV_Printed_History should be SNV_ID. This should avoid the seek and key lookup.

BUT: What report requires 70000 rows including notes with a total of around 11GB of data?


The amount of data is here the problem. Transmitting 11GB over the network requires some time, thus you have a higher chance for blocking, cause as long as the data is read you may block other processes.

You need to filter the result to the needed rows on SQL Server...

The first column of your primary key in vnl_SNV_Printed_History should be SNV_ID

Not sure what you mean, as the ID field is the PK (I first had the SNV_ID as PK the way it is in Caspio, however some experts here at EE advised me to change it to ID as the SNV_ID is a text field).

BUT: What report requires 70000 rows including notes with a total of around 11GB of data?

I filter it on the FE as follows.

    DoCmd.OpenReport stDocName, acPreview, , sSql

Select allOpen in new window

Private Function sSql() As String
Dim s As String
    If Nz(Me.TextClient_First_Name, "") <> "" Then s = s & " And Client_First_Name like '" & Me.TextClient_First_Name & "*'"
    If Nz(Me.TextClient_Last_Name, "") <> "" Then s = s & " And Client_Last_Name like '" & Me.TextClient_Last_Name & "*'"
    If Nz(Me.TextDate_SignedFrom, "") <> "" Then s = s & " And Date_Signed >= #" & Me.TextDate_SignedFrom & "#"
    If Nz(Me.TextDate_SignedTo, "") <> "" Then s = s & " And Date_Signed <= #" & Me.TextDate_SignedTo & "# +1"
    If Nz(Me.TextNurse_Name_Stamp_SNV, "") <> "" Then s = s & " And Nurse_Name_Stamp_SNV like '" & Me.TextNurse_Name_Stamp_SNV & "*'"
    If Nz(Me.TextNurse_Signature_First_Name, "") <> "" Then s = s & " And Nurse_Signature_First_Name like '" & Me.TextNurse_Signature_First_Name & "*'"
    If Nz(Me.TextNurse_Signature_Last_Name, "") <> "" Then s = s & " And Nurse_Signature_Last_Name like '" & Me.TextNurse_Signature_Last_Name & "*'"
    If Nz(Me.TextNurse_User_ID_num_SNV, "") <> "" Then s = s & " And Nurse_User_ID_num_SNV like '" & Me.TextNurse_User_ID_num_SNV & "*'"
    If Nz(Me.TextProgress_Note_ID, "") <> "" Then s = s & " And Progress_Note_ID like '" & Me.TextProgress_Note_ID & "*'"
    If Nz(Me.TextReviewed_By, "") <> "" Then s = s & " And ReviewedBy like '" & Me.TextReviewed_By & "*'"
    If Nz(Me.TextReviewedDateFrom, "") <> "" Then s = s & " And ReviewedDate >= #" & Me.TextReviewedDateFrom & "#"
    If Nz(Me.TextReviewedDateTo, "") <> "" Then s = s & " And ReviewedDate <= #" & Me.TextReviewedDateTo & "#"
    If Nz(Me.TextReviewed_Status, "") <> "" Then
        If Me.TextReviewed_Status = "Yes" Then
            s = s & " And ReviewedDate is not null"
        Else
            s = s & " And ReviewedDate is null"
        End If
    End If
    If Nz(Me.TextShift_From, "") <> "" Then s = s & " And Shift_From_Hour = " & Me.TextShift_From
    If Nz(Me.TextShift_To, "") <> "" Then s = s & " And Shift_To_Hour = " & Me.TextShift_To
    If Nz(Me.TextSNV_ID, "") <> "" Then s = s & " And SNV_ID like '" & Me.TextSNV_ID & "*'"
    If Nz(Me.TextVisit_DateFrom, "") <> "" Then s = s & " And Visit_Date >= #" & Me.TextVisit_DateFrom & "#"
    If Nz(Me.TextVisit_DateTo, "") <> "" Then s = s & " And Visit_Date <= #" & Me.TextVisit_DateTo & "#"
    If Me.CheckOnlyNotPrinted = True Then s = s & " And PrintedDate is null"
    If CheckOnlyMissingNotes = True Then s = s & " And MissingNotes > 0"
    If Nz(Me.ComboVendor, "") <> "" Then s = s & " And VendorsID = " & ComboVendor
    
    If Nz(Me.ComboNoPrint, "") <> "" Then
        If Me.ComboNoPrint = "Yes" Then
            s = s & " And NoPrint =  1"
        Else
            s = s & " And (NoPrint is null or NoPrint = 0)"
        End If
    End If
    
    
    s = s & " And (Status is null or Status <> 'Draft')"
    sSql = Mid(s, 5)
End Function

Select allOpen in new window

Private Sub Report_Open(Cancel As Integer)
'Debug.Print "Open"
    If Len(Forms!SkilledVisitNotesFilterFrm.OrderBy) > 0 Then
        Me.OrderBy = Replace(Forms!SkilledVisitNotesFilterFrm.OrderBy, "[SkilledVisitNotesFilterFrm].", "")
    Else
        Me.OrderBy = "SNVNum"
    End If
    Me.OrderByOn = True

Select allOpen in new window

Later on in report I also have reference to the filter property as follows.

        If MsgBox("Update date printed", vbYesNo + vbDefaultButton1 + vbQuestion) = vbYes Then
            If Len(Me.Filter) > 0 Then
                s = "Update SNV_Printed_History set PrintedDate = #" & VBA.Date & "#"
                s = s & ", PrintedBy = '" & Forms!Main.ComboInitials & "'"
                s = s & " where ID in (Select PrintedHistoryID from SNV_Printed2_Qry where " & Me.Filter & ")"
                CurrentDb.Execute s
                b = True
            End If
            
        End If

Select allOpen in new window

You need to filter the result to the needed rows on SQL Server...

Yes I know that, the other guy also mentioned this, however didn't got to it yet. (must admit it will take me some time to accomplish that...).


But: while looking now at the table I was shocked to see that we have lost the PK setting on this table!! (see attached).

Perhaps this is the cause of the entire problem occurring suddenly...

As mentioned in previous related thread, we restarted the server last Wed, could that cause such a thing?!

Thanks,
Ben
Untitled.png

Yes I know that, the other guy also mentioned this, however didn't got to it yet. (must admit it will take me some time to accomplish that...).

You should really do this first. Cause you're transmitting a lot of unneeded data.

Has any row in your table an NULL in SNV_ID? When not, then this should be the first column of your clustered index. And yes, being a heap table can explain this problem, cause you may have no order in the physical structure, which can lead to lock escalation (table level).

Perhaps this is the cause of the entire problem occurring suddenly...

Actually looking back at this comment in the previous thread, I see that at the time the problem started we still had the PK in place, so this rules out the PK issue being the cause of this problem.

Any idea what could have triggered such a thing as losing a PK?

Thanks,
Ben

Changing the clustered index gives you a better plan:


Whether it performs really better, you need to compare the numbers and runtime. Use SET STATISTICS TIME ON and SET STATISTICS IO ON. Place it before your query and compare the message window (logical and physical reads matter). Test it with the old key and the new key.

Hi Experts,
Currently this is not happening, lets hope by now problem got fixed after applying some of Ste5an suggestions.
If problem persists will have to convert the filter to SQL as suggested.
(Worse case if nothing helps, will post Urgent! Cannot insert new records in SQL table #3-:).
Thanks to ALL participants!
Special thanks to ste5an for walking me thru all this...
Ben

btw, there is an glitch in your filter code (SQL Injection):

When ever you use a string as parameter for building a SQL query, you need to escape it. It may contain quotes. Thus your function should/must look like this e.g.

Private Function SqlWhereCondition() As String

  ' Picked only each data type once. Do this for all parameters.

  Dim Result As String

  ' Strings.
  If Nz(Me.TextClient_First_Name.Value, "") <> "" Then
    Result = Result & " AND Client_First_Name LIKE " & SqlQuote(Me.TextClient_First_Name.Value & "*")
  End If

  ' Dates.
  If Nz(Me.TextDate_SignedFrom.Value, "") <> "" Then
    Result = Result & " AND Date_Signed >= " & SqlDateJet(Me.TextDate_SignedFrom.Value)
  End If

  ' Numbers (Integers)
  If Nz(Me.TextShift_From.Value, "") <> "" Then
    Result = Result & " AND Shift_From_Hour = " & CLng(Me.TextShift_From.Value)
  End If

  ' Booleans.
  Result = Result & " AND" & Iif(Nz(Me.TextReviewed_Status.Value, "") = "Yes", "", " NOT") & " ReviewedDate IS NULL" 'Why not a CheckBox or ComboBox?

  If CheckOnlyMissingNotes Then
    Result = Result & " AND MissingNotes > 0"
  End If

  Result = Result & " AND " & Iif(Nz(Me.ComboNoPrint.Value, "") = "Yes", " AND NoPrint =  1", " AND (NoPrint IS NULL OR NoPrint = 0)")
  SqlWhereCondition = Mid(Result, 5)

End Function

' Methods in a standard module.
Public Function SqlDateJet(ADate As Date) As String

  SqlDateJet = Format(ADate, "\#m\/d\/yyyy#")

End Function

Public Function SqlQuote(AText As String, Optional ADelimiter As String = "'") As String

  SqlQuote = ADelimiter & Replace(AText, ADelimiter, ADelimiter & ADelimiter) & ADelimiter

End Function

Select allOpen in new window

Hi ste5an,

Do not recall receiving an email about your latest comment...
Will test that and let you know.

Thanks,
Ben

Thank you ste5an!