How to improve the liveness of a system when having a long-running transaction blocking multiple reading transactions Is there any recommended approach for improving the application liveness when having, over the same database object, multiple reading transactions, and one long-running transaction which is updating the data?
For example, a transaction Ti is updating several records of [CUSTOMER] table (which can last hours long), and multiple Tj transactions are trying to read the number of orders of each client. I'm assuming that if Ti gets an exclusive lock, all Tj transactions will be suspended resulting in a bad performance (low liveness).
My application is at design time, and I haven't chosen a concrete DBMS yet.
My goal is that Tj are served with the newest version of [CUSTOMER] before Ti began and that Ti doesn't block Tj.
SQL Server offers Read Committed Snapshot Isolation and readers don't blocker writers and vice versa. As Mustaccio said though, the real culprit or problem here is that an update really shouldn't take hours. When done properly, with good table design that includes proper normalization, changing date should take a matter of seconds.