I am using a JSP/JDBC which is having a select statement. The query refers to a VIEW created based on a join between 2 tables. The JSP page displays the records which are fetched as a result of the query executed on the VIEW.
Problem faced :
The VIEW gets locked and the JSP page doesnt display any records.
Only when I release the lock on the view, I am able to see only a fraction of the records which are queried by the same VIEW.
BTW, There are no inserts or any other operations perfomed on these 2 tables from any other client/JSP pages.
How did you determine that the problem was a deadlock by the way? In normal operation SQL Server will use shared locks for views, and shared locks cannot cause deadlock problems with each other - shared locks can co-exist quite happily for the same row, page or table (hence the name "shared"). Such a lock should only cause a problem if some other process needs to update something which currently has a shared lock against it. It should also be released fairly quickly unless your transaction isolation level is set to SERIALIZABLE or REPEATABLE READ or you've used a HOLDLOCK locking hint.