Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a SQL Azure server with some stored procedures with TABLOCKX hints that I call from C# code on a Windows Azure web role VM.

I'm kinda paranoid about those hints - what if there's some network protocol failure and the transaction doesn't end or something like that and a lock is held forever and all invokations of that stored procedure block on the statement with the TABLOCKX hint and all my stuff comes to a halt.

Is it somehow possible to tell SQL Server that the lock should not be held for more than say one minute so that it is unconditionally released after that time?

share|improve this question

1 Answer 1

You can explicitly specify using :

SET LOCK_TIMEOUT timeout_period

timeout_period

Is the number of milliseconds that will pass before Microsoft SQL Server returns a locking error. A value of -1 (default) indicates no time-out period (that is, wait forever). When a wait for a lock exceeds the time-out value, an error is returned. A value of 0 means to not wait at all and return a message as soon as a lock is encountered.

At the beginning of a connection, this setting has a value of -1. After it is changed, the new setting stays in effect for the remainder of the connection.

The setting of SET LOCK_TIMEOUT is set at execute or run time and not at parse time.

The READPAST locking hint provides an alternative to this SET option.

CREATE DATABASE, ALTER DATABASE, and DROP DATABASE statements do not honor the SET LOCK_TIMEOUT setting.

Refer to : Support for SET statements in Azure

UPDATE:

Is it somehow possible to tell SQL Server that the lock should not be held for more than say one minute so that it is unconditionally released after that time?

No. As I mentioned in the comments section.

THe database wont be able to maintain its ACID properties.

Either the query fails and returns an error or the lock is held by SQL Server until the transaction is completed and then released.

share|improve this answer
    
I think the OP is asking about how to limit how long a lock is held, not how long it is waiting to get a lock. –  Thomas Stringer Dec 12 '13 at 14:56
    
@ThomasStringer: Yes, exactly, I want to lock to disappear after some period of time, not the query to fail. –  sharptooth Dec 12 '13 at 14:58
    
@sharptooth I guess that is not possible - a lock to disappear after some period of time. The database wont be able to maintain its ACID properties if you do that. –  Kin Dec 12 '13 at 14:59
    
@sharptooth I'm not sure how you're currently implementing "everything", but most providers have a command timeout (System.Data.SqlClient's SqlCommand.ConnectionTimeout is set to 30 seconds by default). If the query doesn't finish in that time, it'll back out just like you want. –  Thomas Stringer Dec 12 '13 at 15:05
    
@ThomasStringer Correct me if I am wrong, but it wont back out like OP is thinking. It will do the same thing - return an error. SqlCommand::CommandTimeout Property => Gets or sets the wait time before terminating the attempt to execute a command and generating an error. –  Kin Dec 12 '13 at 15:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.