Select on large table.

[LineVoltageHalogen]

Greetings All, I was wondering what would happen if I were to do a
"select * from table" on a table that has about 5 million rows. Would
my read block other writers to the same table? Would it block other
readers? I know SQL uses optimistic lockign by default but I am not
sure what this means to other users trying to access the same table?
Any advise would be greatly appreciated.

TFD

[Erland Sommarskog]

LineVoltageHalogen (tr****************@yahoo.com) writes:

Greetings All, I was wondering what would happen if I were to do a
"select * from table" on a table that has about 5 million rows. Would
my read block other writers to the same table? Would it block other
readers? I know SQL uses optimistic lockign by default but I am not
sure what this means to other users trying to access the same table?
Any advise would be greatly appreciated.

Other readers would not be blocked.

Writers would probably be blocked for the duration of the query, since
I would expect SQL Server take out a table lock. That would mean that
the table would be locked until your client has gotten all rows from
SQL Server.

With row locks, locks would be released when the client has received
the rows, as SQL Servers default isolation level is READ COMMITTED.

In any case, getting five millions is a hefty operation and not really
recommendable.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[David Housman]

If you're using access, you might have timeout problems. You can alter
that setting in the Enterprise manager.
I suggest multipart queries if you're selecting from multiple tables or
cursing through the rows if your doing data processing.

-DJ

[Gert-Jan Strik]

It will not block readers, since the statement will use read locks, and
read locks are shared.

When I run "SELECT * FROM ..." on a 18 million row table (2.5GB), and
then run sp_lock on a seperate connection, I only see intend shared page
locks (different pages, but just one at a time), and an intend shared
table lock (and a shared database lock).

So this basically means writers are not blocked, apart from the
occasional page lock (which are so short that they didn't show in my
sp_lock experiment).

If I run "SELECT * FROM .. (TABLOCK)", then it is a different story.
Then there are no page locks but the (requested) shared table lock. From
that moment on the transactions on that table are piling up, because
then writers are locked.

Hope this helps,
Gert-Jan

LineVoltageHalogen wrote:

Greetings All, I was wondering what would happen if I were to do a
"select * from table" on a table that has about 5 million rows. Would
my read block other writers to the same table? Would it block other
readers? I know SQL uses optimistic lockign by default but I am not
sure what this means to other users trying to access the same table?
Any advise would be greatly appreciated.

TFD