john 60195 (1/3/2013)
After some more read maybe this would lock a record (and only one record) until all work is done
No.
Rowlock just says *start* with row locks. Not only ever take row locks. If there's a lot of row locks taken, SQL escalates to table locks.
It is a very, very bad idea to start a transaction and then allow the user to input stuff, watch the screen, go for lunch, take the weekend off, etc. You'll end up with major blocking and probably a tonne of support calls for 'my session's hanging/timing out'. Transactions should always start and commit in a single database call.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability