How do long running transactions negatively impact overall system performance, and what strategies help avoid this problem?

A long running transaction holds its locks for an extended period, potentially blocking many other transactions trying to access the same data, and can also cause the transaction log to grow significantly since its log records cannot be cleared until the transaction finishes, so strategies to avoid this include breaking large operations into smaller batches, minimizing the work done between BEGIN and COMMIT, and avoiding user interaction or slow external calls within an open transaction.

-- Avoid holding a transaction open across many small batches
-- Instead, batch large updates into smaller, faster transactions
WHILE EXISTS (SELECT 1 FROM Orders WHERE Status = 'Pending' AND OrderId < 10000)
BEGIN
  BEGIN TRANSACTION;
  UPDATE TOP (1000) Orders SET Status = 'Processed' WHERE Status = 'Pending';
  COMMIT TRANSACTION;
END;