Any query that locks a resource (i.e. table) will try to lock just the rows it wants. If this amounts to a significant enough portion of the table, then the whole table gets locked. Check to make sure your large select query is making use of indexes by looking over the execution plan in Query Analyzer.
I know recordsets can be opened with a "lock mode". Either optimistic, or pessimistic. I forget how the two of those affect the database. Pessimistic, may lock the table, and optimistic may just keep a local copy of the recordset. Either way, you are right, Brett. No where clause, or a vague where clause will equate to a table lock.
Hey I have been reading about that, and I found that when you select a table, it get locked in shared mode. It means you can't insert, update or delete any record during the query,
how do I change this ??? Is there any way to make a transaction during the query time ??
As was mentioned above, the select query could be run with the (nolock) hint, but that is dangerous. You may want to look over the design of the system, especially this large select. Is this for a report? If so, then you may want to offload reporting to a standby server that is updated by either replication or logshipping.
Well, but in have another problem with the concurrency, when I execute the select many times (pushing F5 in the web application) and at the same time I try to update a record of the table, I get everything locked (as if I had the table locked) even if the query is so small, I can wait for a long time and I cotinue stuck.
Does sql server unlock the table automatically when a query is finished ?
Is there any way to force the unlock ?
I'm really confused with SQL Server, cuase I always have worked with Oracle !
Oracle and MS-SQL take opposite approaches on locking. Oracle uses the methodology that readers can only block readers and writers can only block writers. Read and write operations never block each other in Oracle. If you need to ensure that it is safe to read (that the read will be repeatable or serializable), you are forced to explicitly state that in your code if you are using Oracle.
MS-SQL uses the methodology that locking/blocking should be symetric. If you attempt a read that is potentially dangerous, you should acquire the appropriate locks before you do it. If you attempt any change to the database (insert/delete/update), you should acquire the appropriate locks before you do that. If a lock that you want requires you to wait for a resource, the server enforces that wait unless you explicitly choose to do without that service.
The Oracle method is simpler, in that most users never need to worry about locking to do simple tasks. The MS-SQL method is much safer, in that you don't need to worry about whether it is safe to do something, since the server won't accidentally allow you to hurt yourself (and get an incorrect answer).
You can make an argument for either method. I prefer the server to protect me unless I explicitly choose to do something that might be dangerous... Then I know when I'm unprotected and that I need to pay careful attention, instead of always having to think about it.
If you want to confine the risk to a single table in an SQL statement, use the (NOLOCK) hint. This is VERY DANGEROUS, but sometimes justifiable.
If you want to "swim with the sharks" (not anything I'd recommend), then use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. This is a lot like cleaning outhouses with gasoline... It makes things simple at first because it disolves anything and disinfects whatever it touches, but it makes one heck of a mess when (that's when, not if) it blows up on you!