GilaMonster (4/23/2013)
when you have a lot of fine-grained locks in transaction you'll get a problem with accessing data? 🙂
No. If there are too many locks, SQL will just escalate to a table lock unless escalation is disabled. Lots of locks can cause blocking, but that's about it.
I said that information: c# code calls in loop some simple procedures which perfoms only atomic insert/update/delete (by one row). There's no reason for escalation. More loop amount, more amount of locks in session (and - again - no escalation will trigger).
GilaMonster (4/23/2013)
Both temp tables and table variables are specific to the session that created them (ignoring global temp tables), hence any locks taken are fairly irrelevant as they will never block other users.
Table variables however have that whole 'no stats' problem, which could negatively impact query performance unless the table variables are very small.
I didn't speak about blocks. Only about locks placed by SQL Server and which I saw in sys.dm_tran_locks for specific session.
And because there're huge amount of fine-grained lock I see perfomance degradation in that session.