What are some best practices for implementing row-level locking?

Learn about the IRLM. Too many row level locks, and the IRLM will escalates the lock to a page, so it has fewer to manage. Too many page-locks, and the IRLM escalates the lock to a table lock. Learn how to build reasonable cursors to avoid lock escalations. SQL locks are not compatible with utility locks… learn why. Learn the lock effect of a Foreign-key probe within an ERD.

Ultimately the IRLM has the final decision on locks. Hints, clauses and modes merely suggest a strategy. DB2 has a Utility Read ( aka with UR ) clause to ignore the lock, however, it’s risky, if a previous DML statement hasn’t committed the data from instance memory to the data page, you could be selecting data that is old, and not current.

If you can fix the underlying schema design or approach your solution then avoid explicit locking. I have always advised engineering teams the same but also helped implementing in as efficiently as possible as a stop-gap for complex/ legacy/monolith applications. With an increased number of locks resources contention will rise exponentially. especially memory resources. So make sure your db server has the capacity to add physical memory in hundreds of GB/TB scale. If your concurrent queries are accessing overlapping data, explicit rowlock does not resolve that. You will still experience significant blocking among concurrent queries.

ERDs with a circular FK reference will prevent a commit from happening if an update is attempted. Too many indexes can be costly, in both Lock management and timeouts. Many DBAs fail to realize every index for a table, wether chosen by the access method of an explain-plan or not must be managed. It take 5-times longer to write an index than it takes to read it. So if a table has 30-indexes, and only 1 is used, the remaining 29 still have to be updated consuming instance resources. RDBMS utility locks drain SQL DML locks, causing a wait-state for SQL-DML lock requests to wait until the utility lock releases, hence a possible timeout. Overloading the IRLM will cause a rollback for poorly designed cursors.

Proceed with Extreme Caution. Evaluate the average row size, and based on that number of rows you will have on one page. If you have hundreds of rows on a page stop right here because you won't see any increase throughput. All the contention will just switch from row level to page level and explicit locking will have no positive impact. Do a thorough testing in an exact mirror environment before deploying in prod. This will be not just a logical test but an equally important test on how server resources are going to be impacted so you need an environment where you have similar hardware specs to see how much memory, cpu, locking, blocking etc.. jumps. Validate the callers/queries will always be accessing non overlapping data.

I consider Explicit Row Level Locking to be a very last resort because of all the pitfalls that have been expressed here. First exhaust all possible alternatives before resorting to using ERLL.