I periodically receive the following exception.
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
I've added the following to my criteria to increase the timeout time.
.SetTimeout(180)
1) Is there a way to add this to my nhibernate configuration so that 180 is the default time?
2) what are the consequences to increase the timeout? Will this increase or decrease the likely hood of deadlocks?
command_timeout - Specify the default timeout of IDbCommands generated by NHibernate
Taken from Table 3.1. NHibernate ADO.NET Properties in
http://www.nhforge.org/doc/nh/en/index.html#configuration-hibernatejdbc
Ad 2. Connection timeout is not help you with deadlocks. Timeout is the time which the client waits for DB response and if the time is out DB just sends error state.
On the other hands, deadlocks are resolvable as one transaction has a lock and waiting for other lock which is owned by other transaction whilst it's waiting for resource locked by first transaction. Note that when DB detects this issue it releases error immediately - not after any timeout.
See:
When you increase timeout the only thing you will allow is longer waiting when any transaction holds a lock which are you waiting for.
E.g. when you have a client which deploys larger data into your system and it performs lock on table basis. Deployment operations can take 60 seconds. Suppose another client which reads the data from table, this client is blocked for 60 seconds until it's able to read the data. Suppose timeout = 30 seconds - this always fails, on the other hand, same situation with 90 seconds timeout will work.
Depends on situation but you should provide as small transactions as possible to enforce better latency and throughput.
