If you have problems with transactions locking your tables, you might want to use Snapshot transactions.
Snapshot transactions work like this:
When the SNAPSHOT isolation level is enabled, each time a row is updated, the SQL Server Database Engine stores a copy of the original row in tempdb, and adds a transaction sequence number to the row.
This ensures that the table is not locked while in tranaction(s).
If a snapshot transaction attempts to commit an update to a row that was changed after the transaction began, the transaction is rolled back, and an error is raised.
To enable it, use following script ( This will not hang like other examples )
declare @sql varchar(8000)
select @sql = ‘
ALTER DATABASE Libomcore SET SINGLE_USER WITH ROLLBACK
IMMEDIATE ;
ALTER DATABASE Libomcore SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE Libomcore SET MULTI_USER;
ALTER DATABASE Libomcore SET ALLOW_SNAPSHOT_ISOLATION ON;
‘
Exec(@sql)
