Accessing data in sql server without a record lock

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.
vanroybelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Can you post your code please.
0
vanroybelAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
vanroybelAuthor Commented:
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
vanroybelAuthor Commented:
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
vanroybelAuthor Commented:
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
vanroybelAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
vanroybelAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
vanroybelAuthor Commented:
Thanks for the link. I'll try to look at this today if I have the time.
0
LeeTutorretiredCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
vanroybelAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.