Slow Sql Transaction Blocks Table

I have a very slow sql transaction, which inserts new rows in the table. All other 'select' queries from another connections wait for this transction to unlock the table. Is it po

Solution 1:

One quick 'get out of jail' card is enabling read committed snapshot on the database, see Choosing Row Versioning-based Isolation Levels, also mentioned in Deadlocked!. When RCSI is enabled on the database your butonn2 click read will do exactly what you want: it will read an old version of the row, w/o waiting for button1 to commit.

To enable RCSI simply run this once:

ALTER DATABASE [test]  SET READ_COMMITTED_SNAPSHOT ON;

Of course there is no free lunch: enabling row-versioning will occur a cost in tempdb IO and size. See Row Versioning Resource Usage. For an Express instance there will be no measurable impact.