set transaction isolation level repeatable read;
go

	begin try	
		begin transaction;

		insert into tableB with(rowlock)
			select * from tableA with(rowlock) where status = 1

		commit transaction;
	end try
	begin catch
		if xact_state() <> 0
		begin
			rollback transaction;
		end
	end catch;

quote:

---

Originally posted by Sachin.Nand

Maybe you can use nolock hint when you want to read the table in some other session.But ofcourse it will be a dirty read and consistency of the data cant be guaranteed.

PBUH

---

quote:

---

Isn't there any way to prevent rowlock locking the whole table?

---

quote:

---

Originally posted by Lumbago

quote:

---

Isn't there any way to prevent rowlock locking the whole table?

---

You can read smaller chunks of data so that lock escalation will be prevented, but I don't there there are any ways to prevent lock escalation. It is however possible to read the table if you use the READ COMITTED SNAPSHOT isolation level on the *reading* spids ->

http://msdn.microsoft.com/en-us/library/ms188277.aspx

- Lumbago
My blog-> www.thefirstsql.com

---