Are you referring to SQL Server's locks or something that you want to implement for your application?

Technically you probably could do this. The syslockinfo table in the master database provides a means to determine what locks are applied to what resources. Although I'm not sure of the usefulness of doing this.

The SQL Server optimizer and query engine determine what resource will be locked and at what granularity those resource will be locked. For example, this query would probably lock a single row:

SELECT * FROM authors WHERE au_id = '...'

While this query would probably lock the entire table:

SELECT * FROM authors

Of course, if there is not applicable index, SQL Server may decide that it has to perform a table scan and you'll end up with a table lock and not a row lock.

Also SQL Server supports a number of locks: schema, shared, update, exclusive, and range. Again, the optimizer and the query engine will determine the appropriate lock.

And just to make things interesting ;-), the length of time that a lock is held will depend on the type of lock and the Transaction Isolation Level that is in effect. For instance, when the Transaction Isolation Level is set to READ COMMITTED, Shared locks are released as soon as the resource is read. Under REPEATABLE READ, Shared locks are held until the transaction terminates. While, READ UNCOMMITTED doesn't even bother applying shared locks.

What is the problem that you're trying to solve?

-Mike

>Hi,
>
>I am using SQL2000 as back-end,
>
>I want to select records which are not locked and also give a count of locked records.
>
>Any ideas...?
>
>Gratefull if you could help
>
>Kind Regards,
>KUMAR