I have a long request, which is importing data into the system using transaction.The problem is that when single transaction is running, in that period other requests to the tables that it is using are blocked and are running forever and ending with Timeout Exceptions
The wait operation timed out
You can enable Snapshot Isolation in SQL server, which in conjunction to setting the READ_COMMITTED_SNAPSHOT setting will change the behaviour you are seeing.
While a long-running transaction is in progress, instead of other tables being locked, they will see the rows in that table from before the transaction started.
To enable this:
ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON
This is actually the default behaviour in Entity Framework 6 if your database was created by Code-First migrations.