What is ROWLOCK and what it does

Question

1) I am curious to know what is ROWLOCK and what it does ?

2) when ROWLOCK lock the rows ? can it lock the rows when it is used in Select statement ?
if yes that means rows gets locked so from different session people will not be able to select those records which is locked by ROWLOCK ?

3) when ROWLOCK release? do we need to issue any sql to release lock?

4) if i issue this sql SELECT * FROM TESTTABLE WITH (ROWLOCK, READPAST) WHERE Lock=0
does it means it will lock all rows whose Lock has 0 value so from other session those records will not be selected ?
when this lock will be released ?

please share knowledge.

Answer 1

Normally, when many rows in a page are lock, SQL Server escalates to a page lock, which is more efficient than many locks on the individual rows, but can have the side effect of keeping locked some rows within the same page that would otherwise not have been locked. Similarly, when many pages in a table are locked, SQL server can escalate to a full table lock.

ROWLOCK forces the locks to be taken only on rows. That is, it prevents the locks to be escalated to pages or table. By itself, ROWLOCK does not cause anything to be locked, and it does not control when the locks are released. The effect is that if anything has to be locked (for other reasons unrelated to the presence of the ROWLOCK hint) then these locks will be kept at row level and not escalated to a higher level.

Answer 2

ROWLOCK is a query hint, all hints are explained at Hints (Transact-SQL) - Table