If my ASP.Net application crashes just after HOLDLOCK is issued?

Will the database get locked if my ASP.Net application that is calling a stored procedure in which a HOLDLOCK for table1 is issued to SQL Server, suddenly crashes just after the stored procedure is called?

Holdlocks works on tables, pages, or rows it won't lock an entire database. If the commit and/or rollback is in the stored procedure, or contained in the batch sent to the SQL Server, then no. The batch and/or stored procedure will run to completion.

If you do something like issue this to the SQL Server: BEGIN TRANSACTION (Or start a transaction using the transaction object), then issue a SELECT ... (WITH HOLDLOCK) then crash before your ASP.NET application rollsback or commits the transaction, and the database server is remote, then yes, it'll be locked until the SQL Server realizes the connection is dead, and I'm not sure how long that would take. If it's a local SQL Server, then it realizes it immediate, and rollsback the transaction.

|||So, it seems that thesafestpractice as far as preventing SQL Server being held up in above scenario, is to provide rollbacks/commits inside stored proecedures rather than in ASP.Net code through ADO.Net.Is that correct?|||

Is it safest? Yes.

However, like I said, I haven't tested myself to see how long it will take SQL Server to detect a dead connection. It could be seconds, it could be hours. And it only really makes a difference if you are running in a web-farm environment, or you have other applications (that don't also run on the web server machine) that need access to the table data in a quick manner -- AND you can't tolerate the database to be down should a machine totally crash (Which is pretty darn rare). I would suggest that if you are deploying to such an environment you test this out yourself.