By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,413 Members | 1,594 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,413 IT Pros & Developers. It's quick & easy.

Select on large table.

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Jul 23 '05 #2

P: n/a
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

Jul 23 '05 #3

P: n/a
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

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.