I used to run very 'SELECT' query against a production database which are being actively updated every 5 minutes. Whenever I ran the 'SELECT' query, the database got deadlocks and connection-timeout.
What I am wondering is why even simple 'SELECT' query can cause those deadlocks; the execution time of the 'SELECT' query was just less than a second, and the number of result set was just one.
Often I didn't commit, after I ran the query and got result set in SQL management studio in 'autocommit' off mode. Can it cause deadlocks?
Your answers or explanations will be most appreciated.
Thank you folks in advance!
See this Technet article on deadlocking - it uses the case of a single table with only 2 rows to discuss how a deadlock can be created. Once you get into real-world systems with far more tables, connections and code-paths predicting and preventing deadlocks becomes much harder.
If you are 'just' executing a SELECT then why would you have autocommit mode off? This will hold any locks and block other connections until you commit/rollback.