question

JakubowskiDonald-3775 avatar image
0 Votes"
JakubowskiDonald-3775 asked ErlandSommarskog answered

SqlDataReader and sp_releaseapplock

Here's my process flow:

  1. Create a database connection in a using clause.

  2. Call sp_getapplock.

  3. Create a SqlDataReader to get data in or out of a using clause.

  4. Close SqlDataReader using clause or call reader.Close().

  5. Call sp_releaseapplock

  6. Close db using clause.

When I execute step 5, an exception is thrown because I no longer hold the lock.
So two questions:
Why does closing the SqlDataReader terminate my lock?
Can I safely omit the call to sp_releaseapplock and rely on the closing of the SqlDataReader to release and clean up that lock?

sql-server-generaldotnet-csharp
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered

Unless you created a transaction before call sp_getapplock, it was released at the end of implicit transaction. If it was part of the select query, it was released when the select finished. If you did it in its own sql statement, it released at the end of the statement.

If the lock is part of the datareader sql (it should be) than it release should be part of the batch also.

You get an error calling before reader completes, because a connection allows only one active query.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 commented

It doesn't. There is no interaction at all between SqlDataReader and app locks. There is something else wrong.

Either sp_getapplock failed, or the resource name is wrong when you call sp_releaseapplock or you are on a different session. App locks are released when the session is closed.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I've looked at sys.dm_tran_locks and the lock is created successfully. If I execute a stored procedure instead of using an SqlDataReader and then release it, the lock is successfully released. If I try to execute an sp_releaseapplock before the reader is closed I get an exception indicating that the reader must be closed first. If I close the reader and then execute sp_releaseapplock an SqlException is thrown, number 1223, that says I don't have that lock. Looking at sys.dm_tran_locks after the reader is closed, the lock is no longer there. So closing the SqlDataReader and/or disposing it at the close of the using clause does indeed affect the locks.

0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

The locks which are associated with the current transaction will be released when the transaction commits or rollback. If the lock is associated with the current session, then the lock releases when the session is logged out or killed. If the server shuts down, all the locks will be released

Reference


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

In addition to other posts: application locks can either be on session or transaction level, as specified by the @LockOwner parameter. Transaction is the default. Maybe Session is what you intended?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.