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.