Hi,
we are using SQL Server 2005.
We have a problem because of an application which tries to
insert a duplicate row in a table with primary key. This insert fails
of course but the locks are not released within this transcation.
This is not locking up on MS SQL Server 2000. But we can see the lock is still applied there.
Can someone help me on this?
Thanks,
Hmmm. What locks are held by the INSERT? Is this a stored procedure you are calling?
Can you post the code you are calling? It sounds like you might be in a Serializable isolation level, perhaps, and an explicit transaction is being started but not cleared. Are you using connection pooling?
|||We are not using connection pooling. The application is written in 'C' langugage and we are using Standard ODBC drivers provided by Microsoft. There are no explicit locking done for this insert query. After failure if I commit or rollback the lock gets released. I can see locks are also held in MS SQL Server 2000 also but they are not blocking other users to accress that perticular table. I have not chnaged isolation level in both (MS SQL Server 2005 and MS SQL Server 2000). The same application also runs with Oracle 9i and on failure of insert statment dos not locking oracle table. I am trying to figure out what locking mechanisum is changed in MS SQL Server 2005. Do I need to change any default settings in MS SQL Server 2005 which will not lock the table on failure of insert because of duplicate rows.|||Try looking at the output of this query:
select login_name,
case des.transaction_isolation_level
when 0 then 'Unspecified' when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted' when 3 then 'Repeatable'
when 4 then 'Serializable' when 5 then 'Snapshot'
end as transaction_isolation_level,
request_session_id, resource_type, resource_subtype, request_mode,
request_type, request_status, request_owner_type,
case when resource_type = 'object' then object_name(resource_associated_entity_id)
when resource_type = 'database' then db_name(resource_associated_entity_id)
when resource_type in ('key','page') then
(select object_name(object_id) from sys.partitions
where hobt_id = resource_associated_entity_id)
else cast(resource_associated_entity_id as varchar(20))
end
from sys.dm_tran_locks dtl
left outer join sys.dm_exec_sessions des
on dtl.request_session_id = des.session_id
where request_session_id <> @.@.spid
and this:
select der.session_id, der.wait_type, der.wait_time,
der.status as requestStatus,
des.login_name,
cast(db_name(der.database_id) as varchar(30)) as databaseName,
des.program_name,
der.command as commandType,
execText.text as objectText,
case when der.statement_end_offset = -1 then '--see objectText--'
else SUBSTRING(execText.text, der.statement_start_offset/2,
(der.statement_end_offset - der.statement_start_offset)/2)
end AS currentExecutingCommand,
der.open_transaction_count
from sys.dm_exec_sessions des
join sys.dm_exec_requests as der
on der.session_id = des.session_id
cross apply sys.dm_exec_sql_text(der.sql_handle) as execText
where des.session_id <> @.@.spid --eliminate the current connection
to see if you have a statement executing and
See what kinds of locks are being held and what the isolation level is. It is probably just not being disconnected and has a transaction open. Then try profiler to see exactly what is being sent. There is likely a setting that you need to set, but without seeing the code this is not easy to do.
|||Thanks for reply.
The first query is giving follwing result:
Login Tran_Isolation re_ses resource_type mode type Status Owner Table
sasi ReadCommitted 56 PAGE IX LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 KEY X LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 KEY X LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 RID X LOCK GRANT TRANSACTION 72057594243186688
sasi ReadCommitted 56 PAGE IX LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 KEY X LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 KEY X LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 KEY X LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 KEY X LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 OBJECT IX LOCK GRANT TRANSACTION ACLS6999
The second query is not returning any rows.
|||I think you are going to have to use profiler and trace the activity on your server for this process. Post the queries if you can and then we can see what might be wrong.
