Exclusive Row Locking in SQL SERVER 2000

Hi
I have a table with 1 million records. I Have 5 applications (identical) that read from that table and perform the actions. Now, I want an exclusive locks on the data selected. The sql statement is below.

select top 1000 * from Numbers
With (***I need this part *** Exclusive lock on the selected data, not allowing other apps to even read)
where IsSent = 1

How to achieve this. Please explain.

Regards,
Noorul

You can do something like below:

begin tran

select top 1000 * from Numbers
With (xlock, rowlock)
where IsSent = 1

And you will have to keep the transaction open for the locks to be effective. But this is not really a good way to use the database engine. Holding a transaction open with locks consumes lot of resources & with above locking you will essentially be blocking any process that tries to do anything with these rows. You will be better off having another column in the table that tracks the state of each row. You can update the selected row(s) from each application and work with that data offline. There are also other ways of doing the same.

|||Thanks Uma,

Ok, if that would consume more resources then how about splitting the table into 5 tables (with no repetition of course) and using those tables? If so, then could you provide me the code to how to split the tables plz. I am relatively new to SQL

Regards,
Noorul