SQL transaction level settings
Posted on 2008-10-27
Thanks in advance. We just switched to a new ERP platform that uses JDBC to connect to a sql server 2005 database. We are experiencing some intermittent lockup issues that affect users since they become a deadlock victims. This is primarily becuase we are running some automated processed during production hours. We can shift these processes to non production hours however some times there are not enough hours to complete these processes. Right now the database setting for using transactions is set to "Read Committed" I was wondering if changed this to some other more tolerable setting to reduce lokups what are the real risks. I am aware of dirty reads which may result in transactional inconsistencies. Can anybody share their real life experiences with the various settings related to transaction settings? It will be greatly appreciated.