June 20, 2008 at 2:25 am
Its going TAB lock when am inserting grater than 7000 records .it has clustered index also .please tell me why its happening .
June 20, 2008 at 6:09 am
Locking is determined by the query engine. It always goes with a least cost lock. From the sounds of things, you don't have too much data in the table so the engine has determined that simply locking the table is cheaper than trying to lock pages or rows.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 20, 2008 at 6:35 am
is there any way to prevent the table lock while inserting ?. that is permanent temp table so we are deleting the data from that table at the end of the transaction because of that there will be no data on the table. each transaction will take more than 15 minute to complete entire transaction .so if we launch more the two report system hanging.
June 20, 2008 at 6:59 am
You can... But it could lead to problems. You just have to use a table hint to control the locking mechanism.
UPDATE dbo.Mytable
WITH (ROWLOCK)
SET MyCol= 'SomeValue'
...
Be VERY careful doing stuff like this though. It will absolutely affect the behavior of your system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 20, 2008 at 7:05 am
i have decided to use table variable to avoid this kind of lock .i hope this will be better performance . thanks for your reply.
June 20, 2008 at 7:31 am
It depends... we don't really know enough about what you're trying to do to tell you whether you're headed in the right direction or not...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2008 at 7:41 am
Table variables have locks on them too...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 21, 2008 at 5:51 am
-- create a source table
create table tv_source(c1 int, c2 char(100))
go
declare @i int
select @i = 0
while (@i < 100)
begin
insert into tv_source values (@i, replicate ('a', 100))
select @i = @i + 1
end
-- using #table
create table #tv_target (c11 int, c22 char(100))
go
BEGIN TRAN
INSERT INTO #tv_target (c11, c22)
SELECT c1, c2
FROM tv_source
-- using table variable
DECLARE @tv_target TABLE (c11 int, c22 char(100))
BEGIN TRAN
INSERT INTO @tv_target (c11, c22)
SELECT c1, c2
FROM tv_source
-- Now if I look at the locks, you will see that only
-- #table takes locks. Here is the query that used
-- to check the locks
select
t1.request_session_id as spid,
t1.resource_type as type,
t1.resource_database_id as dbid,
(case resource_type
WHEN 'OBJECT' then object_name(t1.resource_associated_entity_id)
WHEN 'DATABASE' then ' '
ELSE (select object_name(object_id)
from sys.partitions
where hobt_id=resource_associated_entity_id)
END) as objname,
t1.resource_description as description,
t1.request_mode as mode,
t1.request_status as status,
t2.blocking_session_id
from sys.dm_tran_locks as t1 left outer join sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address
June 21, 2008 at 8:01 am
I don't understand why you think there's a problem... no one else has access to the Temp Table or Table Variables... why are you using a transaction on them? Why would you ever need to do a rollback on a temporary object?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply