so if i have a select on a table that takes long 25 seconds and an insert on the same table is running at the 10th second ,the insert will wait for select ?or it depends on the range of pages?
It does depend on a number of things. For one, it depends on the transaction isolation level
. At the default level of READ COMMITTED, SQL Server generally releases shared locks as soon as it has read a row or page. You can see a nice diagram of that in Craig Freedman's blog entry
about the default isolation level:
If the READ_COMMITTED_SNAPSHOT
database option is enabled, SQL Server keeps versions of rows to avoid taking shared locks at all when the client requests (or does not change) the default isolation level.
Whether an INSERT is blocked by the SELECT in your example also depends on the data range and granularity of locking. SQL Server decides whether to take locks on rows, pages, or the whole table at the start of the reading operation. As the operation progresses, it may decide to escalate finer-grained locks to the partition or table level, if the number of held locks reaches a particular threshold (it never escalates locks to the page level, it's partition/table or nothing). Most often, SQL Server starts with row or page-level locking. This would be very unlikely to block the INSERT in your example, not least because individual row or page locks would not be held for very long, as mentioned.
Should SQL Server decide to start with a shared table-lock (not an intent shared lock, as I hope you now understand), that would obviously be held until the whole operation completed, and the INSERT would be blocked. SQL Server does not often do this of its own accord, however. It is much more likely that a table lock was explicitly requested by the query writer using a TABLOCK hint.