SqlDataReader and sp_releaseapplock

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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?