Solved

Accessing data in sql server without a record lock

Posted on 2014-10-24
16
143 Views
Last Modified: 2014-11-23
Hello,

We have a bit of a problem with locks in SQL server at the moment. I'm trying to access the data in readonly, and I'm having a bit of a problem. Even as I access it in readonly, it still makes a lock on the record it seems. I'm testing this in access at the moment because it is what I'm used to. I'm using an SQL passthrough query to get data from a read only view. I'm using the options (dbOpenSnapshot, dbReadOnly) to open the recordset.

Any of these options should prevent a lock on the record. But they all lock my record and I cannot update it next.

Im doing this on a windows 7 computer.
0
Comment
Question by:vanroybel
  • 8
  • 6
16 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40401213
Can you post your code please.
0
 

Author Comment

by:vanroybel
ID: 40401235
Txt_order is a textbox with the order num to test.
TmpImprim is a local variable that will stock the latest value of the field and negate it (the underlying field is a boolean).

V_DOCENTETE_PHI is a read only view. I created it using this  code I found online as an example :
create view  table_view
as
select * from table_name
union all
select * from table_name where 1=0

this code will only try to switch the value of DO_Imprim from false to true or from true to false.
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("SQL_passthrough_select")
    qdf.SQL = "select DO_Imprim from V_DOCENTETE_PHI where DO_Piece='" & Txt_order.Value & "'"
    Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
    qdf.Close
    Set qdf = Nothing
    If rs.RecordCount > 0 Then
        If rs(0) = 0 Then tmpImprim = 1 Else tmpImprim = 0
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
        DoEvents
        Set db = CurrentDb()
        Set qdf = db.QueryDefs("SQL_passthrough_update")
        qdf.SQL = "UPDATE F_DOCENTETE SET F_DOCENTETE.DO_Imprim = " & tmpImprim & _
            " WHERE DO_Piece=' " & Txt_order.Value & "'"
        qdf.Execute
        qdf.Close
        Set qdf = Nothing
end if

Open in new window

This code won't work and doesn't update the value. When I remove the first query and don't open the recordset on that line before, I can update the value.
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40401261
It appears that you are using the first bit of code to check whether there are any values for Txt_order.Value by opening a Recordset and returning a value.

My suggestion is that, instead of doing that, you use a Stored Procedure which can return either the value of tmpImprim that you want, or -1 if there are no records.

Additionally, I would convert the second part into a stored procedure as well.

That way, everything is done on the SQL Server side, and you don't have to concentrate on locks.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40401268
My guess as to why you have a problem is this:

1. You open db.
2. You open qdf.
3. You open rs, based on qdf.
4. You close and set qdf to Nothing (but rs is still open?).
5. You access rs (which is based on qdf, which is now closed).
6. You may or may not close rs, depending on whether the IF statement is true.
7. You may or may not close db, depending on whether the IF statement is true.

Things are the wrong way wrong. Close and set items to Nothing from the inside out, i.e. close rs before qdf, and then db. And make sure they are closed, regardless of the results of the IF statement.
0
 

Author Comment

by:vanroybel
ID: 40401279
That is not the problem. I put a value that will always give a result in the txtorder textbox. Beside, my problem is that when I am in this If statement, I close the recordset and try to update. the update doesn't work. The update is also in the IF statement. So if the If statement is not true there is no update and no problem.
0
 

Author Comment

by:vanroybel
ID: 40401282
Just to be complete here is the code with the close in the else


Set db = CurrentDb()
    Set qdf = db.QueryDefs("SQL_passthrough_select")
    qdf.SQL = "select DO_Imprim from V_DOCENTETE_PHI where DO_Piece='" & Txt_order.Value & "'"
    Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
    qdf.Close
    Set qdf = Nothing
    If rs.RecordCount > 0 Then
        If rs(0) = 0 Then tmpImprim = 1 Else tmpImprim = 0
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
        DoEvents
        Set db = CurrentDb()
        Set qdf = db.QueryDefs("SQL_passthrough_update")
        qdf.SQL = "UPDATE F_DOCENTETE SET F_DOCENTETE.DO_Imprim = " & tmpImprim & _
            " WHERE DO_Piece=' " & Txt_order.Value & "'"
        qdf.Execute
        qdf.Close
        Set qdf = Nothing
else
        rs.close
        set rs=nothing
end if

Open in new window

0
 

Author Comment

by:vanroybel
ID: 40401284
Should I only close QDF after I am done with the linked recordset? It doesn't seem to make a problem but I will modify my code and see if it changes anything.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:vanroybel
ID: 40401293
Updated version :
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("SQL_passthrough_select")
    qdf.SQL = "select DO_Imprim from V_DOCENTETE_PHI where DO_Piece='" & Txt_order.Value & "'"
    Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
    If rs.RecordCount > 0 Then
        If rs(0) = 0 Then tmpImprim = 1 Else tmpImprim = 0
        rs.Close
        Set rs = Nothing
        qdf.Close
        Set qdf = Nothing
        db.Close
        Set db = Nothing
        DoEvents
        Set db = CurrentDb()
        Set qdf = db.QueryDefs("SQL_passthrough_update")
        qdf.SQL = "UPDATE F_DOCENTETE SET F_DOCENTETE.DO_Imprim = " & tmpImprim & _
            " WHERE DO_Piece=' " & Txt_order.Value & "'"
        qdf.Execute
        qdf.Close
        Set qdf = Nothing
        
        Set qdf = db.QueryDefs("SQL_passthrough_select")
        qdf.SQL = "select DO_Imprim from V_DOCENTETE_PHI where DO_Piece='" & Txt_order.Value & "'"
        Set rs = qdf.OpenRecordset
        qdf.Close
        Set qdf = Nothing
    Else
        MsgBox "This order does not exist"
        launched = False
        qdf.Close
        Set qdf = Nothing
        rs.Close
        Set rs = Nothing
    End If

Open in new window


I still have the same problem. Also, as before, when I try to update the value without opening the first recordset then it can be updated. It only stops working when I open the line before. So it really is a record lock probably.
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 500 total points
ID: 40401299
Well, as I say, the best way to avoid locks is to use Stored Procedures, and then there is no risk for locks from outside SQL Server. I'm out of ideas regarding the above code.

One of the problems I have above line 17 in its current form is that there is a danger of SQL injection.
0
 

Author Comment

by:vanroybel
ID: 40401930
I can use a stored procedure to call the data I need without locking the records? What I need to do is a select without locking the data, and from what I remember from the classes 10 years ago stored procedure are more like programs that you call.

I won't worry about SQL injection because this is a tool that I am using for myself, that is on my computer only. It won't be used by anyone else. Also we are a small company and I'm pretty sure nobody knows what SQL injection is except me. Maybe my boss knows too but He wouldn't use it.
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 500 total points
ID: 40401958
You can use Snapshot Isolation - no locks are acquired. See http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx for more details.
0
 

Author Comment

by:vanroybel
ID: 40405908
Thanks for the link. I'll try to look at this today if I have the time.
0
 
LVL 59

Expert Comment

by:LeeTutor
ID: 40452858
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40452859
I think my answer was sufficient to answer the question.

The question was how to prevent locks, and I have suggested several ways to prevent the locks.

If vanroybel thinks that my answer was not sufficient to answer his question, then he should post such.
0
 

Author Comment

by:vanroybel
ID: 40461537
Thanks for your answers Phillip, I was away from work as I was sick last week.
Your answer was sufficient for the problem, I now need a bit of time to put everything in stored procedure and see if my problem is resolved.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This Micro Tutorial will teach you how to the overview of Microsoft Security Essentials. This is a free anti-virus software that guards your PC against viruses, spyware, worms, and other malicious software. This will be demonstrated using Windows…
This Micro Tutorial will give you basic overview of the control panel section on Windows 7. It will depth in Network and Internet, Hardware and Sound, etc. This will be demonstrated using Windows 7 operating system.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now