November 4, 2004 at 8:08 pm
Problem: Have sp with select statement which is blocking an insert statement in another sp.
Sps are being executed via two different instances of the same vendor application running on two different virtual machines using the same ID.
Question: How can a Select statement cause blocking?
Server OS: Windows 2000 Server
Sql Version: 8.0.818
Applcation Language: VisualAge for SmallTalk
App DB Acces: MDAC 2.7 Sp1 ODBC
Enterprise manager shows spid 74 blocking spid 148
Last Sql for spid 74 shows usp_selectRecByKey;1
Application is doing synchronous two phased implicit transactions in the following sequence:
App Instance 1
Begin Trans
Execute usp_insertRec val1, val2, val3
If error, Rollback Transaction
Else
Execute usp_SelectRecByKey val1
Execute DB2 transaction
If DB2 success, commit Sql Transaction
Else
Rollback Sql Transaction
App Instance 2
Begin Trans
Execute usp_insertRec val1, val2, val3 (at this point the process is shown as being blocked by Instance one usp_SelectRecByPK @pk = val1)
If error, Rollback Transaction
Else
Execute usp_SelectRecByKey val1
Execute DB2 transaction
If DB2 success, commit Sql Transaction
Else
Rollback Sql Transaction
usp_insertRec detail:
create procedure usp_insert @val1 int, @val2 varchar(20), @val3 varchar(15)
AS
Set NOCOUNT ON
Insert tbl (field1, field2, field3) Values (@val1, @val2, @val3)
usp_SelectRecByKey @val1 int
AS
Set NOCOUNT on
-- field1 is PK on table
Select field1, field2, field3 from tbl with (NOLOCK) where field1 = @val1
November 4, 2004 at 9:34 pm
You must rememebr that by default, a SELECT statement will issue SHARED LOCKS on all rows required for the query and the SHARED LOCK is released as soon as the data is read. If another transaction is attemting an INSERT and the query optimizer has placed a shared lock on so many rows possibly the entire table, then you will be getting some blocking issues until the transaction completes.
Somebody correct me if I'm wrong!
November 4, 2004 at 11:48 pm
Thanks.
Will look into how many rows have shared locks.
Will it retain the Shared lock obtained during the select until the commit after the DB2 transaction? If this is the case, it would explain why the shared locks are not being released when the select sp returns.
Also, would placing the select with in it's own transaction release the shared lock?
November 7, 2004 at 4:27 pm
The default Transaction Isolation Level for SQL Server is READ COMITTED. This level issues Shared Locks on rows that will be selected and Exclusive Locks on rows that will modified.
As the data is read the shared lock is released. However, an exclusive lock on a row or rows will be locked until the transaction completes or rolls back!
I'm not sure about DB2.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply