4 ms·
In Microsoft SQL Server row/extent/table locks are of transactional semantics and often turned off with nolock option. What really matters for concurrency is pa
by petrohi 15y ago
In Microsoft SQL Server row/extent/table locks are of transactional semantics and often turned off with nolock option. What really matters for concurrency is page latch, which is per 8K page.
In SQLite readers actually do block writers by default. Writing transactions are committed with lock escalation steps. First shared lock is acquired, then reserved, then pending and finally exclusive. Pending blocks new shared locks and waits until all in-flight shared locks are released. Again this is default rollback journal behavior. As of 3.7 write-ahead log allows readers to be concurrent with writers, but AFAIK is still rarely used.
- MichaelGG 15y agoI recently found out that turning on nolock is a horrible idea. It doesn't just take you out of transactions. That is, you won't just get uncommitted data, but apparently can get completely inconsistent data as internal structures are updated. That is, even rows not part of a current transaction might not be seen, if you use nolock. Edit: Turning ON nolock, that is.
- petrohi 15y agoMy experience with nolock is that you may get inconsistent rows with some fields before and some after update. Or even seeing duplicated rows when b-tree is rebalanced. But I never seen single fields being partially updated. Per Microsoft, page latch protects atomicity of a single field update. This is why nolock was extremely useful for insert-only tables and in our database design we had many of them.