Run an SQL script in transaction without it blocking other scripts accessing the same table

Question

Can anyone tell me if there is a way I can execute an SQL Server script in transaction without it blocking other scripts that try to access the same table?

I created a program in c# that accesses my transaction database (readUncommited). I can’t commit or rollback while the program is running.

However, I need other programs (that are running out of transaction) to access the same tables to update, Insert, etc. However, they crash and sometimes enter deadlock

Can anyone tell me if it is possible to create a c# transaction that does not block access to other processes?

Answer 1

What you want to do is something against the nature of a transaction. If you do not want a more time consuming line, table, page, etc lock, then you should not execute a transaction, just run the commands and leave engine of SQL Server manage this.

Just to think about what you are proposing to do: imagine a script that within a transaction changes a client’s name, and while the transaction has not yet been completed ( commit/rollback) another script changes the same information from the same record, as the database will decide which change persists when both do commit? This could also generate the situation of deadlock (read more here: What is Deadlock in SQL Server?)

This does not make sense, it is to avoid that there are Locks. You can access a resource with lock for reading, one for example a command select (readUncommited as commented in the question), but not for competing changes as it is blocked.

If there is no transaction, the first script will win a lock and the second will queue and wait for the lock to finish, then the most current information will be the second, but there was lock anyway, it is not possible to change two requests at the same time.

To answer your question, you can’t do that. It is not quite transaction itself that blocks the resources, are the change commands, and this happens with or without transaction, the difference is that in a transaction is more noticeable because usually the transaction takes a little longer, but you can’t avoid a lock when a change command is executed, so that the database guarantees data integrity.

Answer 2

There is no way to do this, unfortunately this hurts consistency rules of SQL Server (and any other sgbd). You can try to do a database redundancy, in case, a secondary one that will be updated whenever the other has an Insert, update or delete (similar to a Trigger). In this case, you can access the secondary bank.