Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2008-09-23 : 13:33:35
|
| Hi,I have the following table...CREATE TABLE [dbo].[tbl_chalet]( [ChaletID] [int] NOT NULL, [ChaletClassID] [int] NOT NULL, [Berth] [int] NOT NULL, [ForSale] [bit] NOT NULL}Berth can be 4, 5, 6, 7, or 8.If I run the following query in one tab...BEGIN TRANSACTIONSELECT * from tbl_chalet WITH (XLOCK) WHERE Berth = 4..and then run the same query on another tab, the second execution blocks until the first has commited.The execution plan says its doing a clustered index scan. So as far as the second query is concerned, is the whole table locked until the first query has commited?Second question, is it possible for one query to access the table at one end and the other to access the table at the other resulting in a deadlock? Or is it effectively the clustered index that's locked and so only one query can access it at a time?Third question, I added an index to the 'Berth' column and it still did a clustered index scan. I would have thought the query could have used this new index. Why doesn' it?Cheers, XF. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-23 : 13:41:36
|
| XLOCK places an exclusive lock on your table. Only when first query ends, it can be accesed by other queries.Its possible to create deadlock when more than one process tries to acess the table simultaneuosly.Adding an index does not mean that it will be used. the usage of index depends on the number of records in table, columns used in WHERE condition, the select list columns,... Based on several of these factors the query engine will dtermine optimal way to get data which might or might not be involving the index usage. |
 |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2008-09-23 : 14:54:42
|
quote:
XLOCK places an exclusive lock on your table.
I don't think that's correct. It puts an exclusive lock on the rows retrieved by the query.Its possible to return other rows from the same table for example...SELECT * FROM tbl_chalet WHERE ChaletID = 439(Chalet 439 does not have a berth of 4)...can be read even whilst the table's 4 berths have been XLOCK'ed with the query from my first post. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-23 : 15:26:23
|
| The query optimizer chose to ignore your index due to the selectivity of the data. A column with only 5 values should not be indexed due to the selectivity. You can view what is locked via sp_lock or through the GUI. A lock can escalate to a table lock if it thinks you are accessing a large amount of the data in the table. So if you had only 2 rows in the table and locked one row, it will probably exclusively lock the entire table. visakh16, your deadlock comment is not correct in that more needs to happen to cause a deadlock.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
|
|
|
|
|
|
|
Am I locking the table?