deAttach database: temp

Showing posts with label temp. Show all posts

Sunday, March 25, 2012

deadlock victim even using temp table

Hi. I am struggling to understand why I get the following error in
using the stored procedure noted below. I recently starting using a
temp table as a way of providing custom paging in asp.net and this
problem has occured ever since (maybe 10 times per day with an average
of 30 users on all day).
Here is the error: "Transaction (Process ID ##) was deadlocked on lock
resources with another process and has been chosen as the deadlock
victim. Rerun the transaction"
The client code is below. It uses a DataAdapter to fill a dataset that
is used to populate a datagrid. The long store procedure is below
that. It essentialy fills the temp table with records that are chosen
then retrieves all the necessary fields for the datagrid using whatever
page that is selected. There is code in there to support sorting and
hopefully it's not too confusing.
I apologize for the long post, I didn't want to remove parts of the SP
to make it shorter in case I removed an important element. I admit, I
am only an intermediate programmer so I may be missing some
fundamentals. Hopefully this is obvious to someone.
Thanks in advance.
Jeff
-- client code --
' Create Instance of Connection and Command Object
Dim myConnection As New
SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
Dim myCommand As New SqlDataAdapter("tochange",
myConnection)
'Dim myCommand As New SqlDataAdapter
'myCommand.SelectCommand.Connection = myConnection
myCommand.SelectCommand.CommandType =
CommandType.StoredProcedure
myCommand.SelectCommand.CommandText =
"dbo.csp_cGeneral_GetRequests2"
myCommand.SelectCommand.Parameters.Add("@.PortalID",
SqlDbType.Int).Value = iPortalID
myCommand.SelectCommand.Parameters.Add("@.Status",
SqlDbType.VarChar, 10).Value = Status
myCommand.SelectCommand.Parameters.Add("@.RequestSeqID",
SqlDbType.Int).Value = RequestSeqID
myCommand.SelectCommand.Parameters.Add("@.BorrowerLastName",
SqlDbType.VarChar, 30).Value = BorrowerLastName
myCommand.SelectCommand.Parameters.Add("@.LoanOfficerCompany",
SqlDbType.VarChar, 30).Value = LoanOfficerCompany
myCommand.SelectCommand.Parameters.Add("@.BegDate",
SqlDbType.VarChar, 25).Value = BegDate
myCommand.SelectCommand.Parameters.Add("@.EndDate",
SqlDbType.VarChar, 25).Value = EndDate
myCommand.SelectCommand.Parameters.Add("@.ContactName",
SqlDbType.VarChar, 30).Value = ContactName
myCommand.SelectCommand.Parameters.Add("@.AgentID",
SqlDbType.Int).Value = AgentID
myCommand.SelectCommand.Parameters.Add("@.HasDocs",
SqlDbType.Int).Value = HasDocs
myCommand.SelectCommand.Parameters.Add("@.AssignedStaffID",
SqlDbType.Int).Value = iStaffSearchID
myCommand.SelectCommand.Parameters.Add("@.CurrentPage",
SqlDbType.Int).Value = _currentPageNumber
myCommand.SelectCommand.Parameters.Add("@.PageSize",
SqlDbType.Int).Value = iPagesize
myCommand.SelectCommand.Parameters.Add("@.SortField",
SqlDbType.VarChar, 30).Value = strSortColumn
myCommand.SelectCommand.Parameters.Add("@.UserID",
SqlDbType.Int).Value = UserID
myCommand.SelectCommand.Parameters.Add("@.Role",
SqlDbType.VarChar, 20).Value = Role
myCommand.SelectCommand.Parameters.Add("@.Maps",
SqlDbType.VarChar, 20).Value = sMaps
' Create and Fill the DataSet
Dim myDataSet As New DataSet
myCommand.Fill(myDataSet, "Requests")
Dim myTable As DataTable = myDataSet.Tables("Requests")
If Not myTable Is Nothing Then
If myTable.Rows.Count > 0 Then
Dim dr As DataRow = myTable.Rows(0)
_TotalRecords = dr.Item("TotalRecords")
Else
_TotalRecords = 0
End If
End If
' Return the DataSet
-- stored procedure --
ALTER procedure dbo.csp_cGeneral_GetRequests2
@.PortalID int,
@.Status varchar(10) = "-1",
@.RequestSeqID int = -1,
@.BorrowerLastName varchar(30) = "-1",
@.LoanOfficerCompany varchar(30) = "-1",
@.BegDate varchar(25) = "-1",
@.EndDate varchar(25) = "-1",
@.ContactName varchar(30) = "-1",
@.AgentID Int = Null,
@.UserID int = 0,
@.Role varchar(20) = 'None',
@.HasDocs decimal = -1,
@.CurrentPage int,
@.PageSize int,
@.SortField varchar(30),
@.Maps varchar(20),
@.AssignedStaffID Int --(-1 all, -2 not in list)
as
--if @.AssignedStaffID = -2
--begin
--
--end
Declare @.TotalRecords int
Declare @.Status1 int
Declare @.Status2 int
Declare @.AssignedAgentID int
Declare @.CustomerID int
set @.AssignedAgentID = 0
set @.CustomerID = 0
if @.Role = 'NotaryAgent'
Begin
set @.AssignedAgentID = @.UserID
set @.CustomerID = Null
end
if @.Role = 'Customer'
Begin
set @.AssignedAgentID = Null
set @.CustomerID = @.UserID
end
if @.Role = 'ServiceOwner'
Begin
set @.AssignedAgentID = Null
set @.CustomerID = Null
end
if @.Role = 'AgentOwner'
Begin
set @.AssignedAgentID = Null
set @.CustomerID = Null
end
set @.Status1 = -1
set @.Status2 = -1
if len(@.Status) = 1 or (len(@.Status) = 2 and not @.Status = '45')
begin
set @.Status1 = cast(@.Status as int)
set @.Status2 = cast(@.Status as int)
end
else
begin
if @.Status = '123'
Begin
set @.Status1 = 1
set @.Status2 = 3
end
if @.Status = '45'
Begin
set @.Status1 = 4
set @.Status2 = 5
end
if @.Status = '123456'
Begin
set @.Status1 = 1
set @.Status2 = 6
end
if @.Status = '1234569'
Begin
set @.Status1 = 1
set @.Status2 = 10
end
end
Declare @.BegDate2 as smalldatetime
Declare @.EndDate2 as smalldatetime
if @.BegDate = '-1' or @.EndDate = '-1' or isdate(@.BegDate) = 0 or
isdate(@.EndDate) = 0
begin
set @.BegDate2 = Null
Set @.EndDate2 = Null
end
else
begin
set @.BegDate2 = cast(@.BegDate as smalldatetime)
Set @.EndDate2 = cast(@.EndDate as smalldatetime)
end
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
RequestID int,
FileSize int,
DocCount int
)
INSERT INTO #TempTable
(
RequestID,
FileSize,
DocCount
)
SELECT
RequestID,
FileSize,
DocCount
>From (
Select SR.RequestID, isnull(FileSize,0) as FileSize,
isnull(temp1.DocCount,0) as DocCount,
CASE @.SortField
WHEN 'Request' THEN 0
WHEN 'Status' THEN SRS.StatusOrder
WHEN 'Borrower' THEN 0
WHEN 'Date' THEN 0
WHEN 'Location' THEN 0
WHEN 'ContactInfo' THEN 0
WHEN 'Agent' THEN 0
WHEN 'FileSize' THEN 0
WHEN 'HasCust' THEN SR.UserID
WHEN 'StaffOrder' THEN Staff.StaffOrder
ELSE SRS.StatusOrder
END AS sortcol0,
CASE @.SortField
WHEN 'Request' THEN ''
WHEN 'Status' THEN ''
WHEN 'Borrower' THEN SR.BorrowerLastName
WHEN 'Date' THEN convert(varchar(20),SR.SigningDate,112)
WHEN 'Location' THEN isnull(SR.SigningCity,'')
WHEN 'ContactInfo' THEN SR.ContactName
WHEN 'Agent' THEN Users.LastName
WHEN 'FileSize' THEN ''
WHEN 'HasCust' THEN '0'
WHEN 'StaffOrder' THEN Staff.StaffInitials
ELSE ''
END AS sortcol1,
CASE @.SortField
WHEN 'Request' THEN '0'
WHEN 'Status' THEN convert(varchar(20),SR.SigningDate,112)
WHEN 'Borrower' THEN SR.BorrowerFirstName
WHEN 'Date' THEN SR.SigningTime
WHEN 'Location' THEN isnull(SR.SigningState,'')
WHEN 'ContactInfo' THEN SR.LoanOfficerCompany
WHEN 'Agent' THEN Users.FirstName
WHEN 'FileSize' THEN '0'
WHEN 'HasCust' THEN '0'
WHEN 'StaffOrder' THEN '0'
ELSE convert(varchar(20),SR.SigningDate,112)
END AS sortcol2,
CASE @.SortField
WHEN 'Request' THEN '0'
WHEN 'Status' THEN SR.SigningTime
WHEN 'Borrower' THEN '0'
WHEN 'Date' THEN '0'
WHEN 'Location' THEN '0'
WHEN 'ContactInfo' THEN '0'
WHEN 'Agent' THEN '0'
WHEN 'FileSize' THEN '0'
WHEN 'HasCust' THEN '0'
WHEN 'StaffOrder' THEN '0'
ELSE SR.SigningTime
END AS sortcol3,
CASE @.SortField
WHEN 'Request' THEN 0
WHEN 'Status' THEN 0
WHEN 'Borrower' THEN 0
WHEN 'Date' THEN 0
WHEN 'Location' THEN 0
WHEN 'ContactInfo' THEN 0
WHEN 'Agent' THEN 0
WHEN 'FileSize' THEN FileSize
WHEN 'HasCust' THEN 0
WHEN 'StaffOrder' THEN 0
ELSE 0
END AS sortcol4,
SR.RequestSeqID AS sortcol5
>From dbo.ctbl_SigningRequests SR
Left Outer Join dbo.ctbl_SigningRequestStatus SRS
ON SR.SigningStatusID = SRS.SigningStatusID
Left Outer Join dbo.ctbl_UserData UD
ON SR.AssignedAgent = UD.UserID
Left Outer Join dbo.Users Users
ON SR.AssignedAgent = Users.UserID
Left Outer Join ctbl_PortalData
On ctbl_PortalData.PortalID = @.PortalID
Left Outer Join dbo.Users Staff
ON SR.AssignedStaffID = Staff.UserID
Left Outer Join (
Select ctbl_Docs.RequestID,
case when sum(Case when ctbl_Docs.LoanDocs = 0 and
ctbl_Docs.TitleDocs = 0 then 1 else 0 end) > 0 then 1 else 0 end as
DocCount,
cast(sum(ctbl_Docs.filesize) as decimal)/1000000 as filesize from
ctbl_Docs
Where ctbl_Docs.PortalID = @.PortalID
Group by ctbl_Docs.RequestID
) as temp1 ON SR.RequestID = temp1.RequestID
Where
SR.PortalID = @.PortalID
and SR.InActiveDate is null
and SR.BorrowerLastName like
IsNull(nullif('%'+@.BorrowerLastName+'%',
'%-1%'),'%'+SR.BorrowerLastName+'%')
and SR.LoanOfficerCompany like
IsNull(nullif('%'+@.LoanOfficerCompany+'%
','%-1%'),'%'+SR.LoanOfficerCompany+
'%')
and SR.ContactName like
IsNull(nullif('%'+@.ContactName+'%','%-1%'),'%'+SR.ContactName+'%')
and SR.RequestSeqID =
isnull(Nullif(@.RequestSeqID,-1),SR.RequestSeqID)
and IsNull(SR.AssignedAgent,-1) =
isnull(Nullif(@.AgentID,-1),IsNull(SR.AssignedAgent,-1))
and SR.SigningStatusID Between
IsNull(Nullif(@.Status1,-1),SR.SigningStatusID) and
IsNull(NullIf(@.Status2,-1),SR.SigningStatusID)
and SR.SigningDate Between IsNull(@.BegDate2,SR.SigningDate) and
IsNull(@.EndDate2,SR.SigningDate)
and SR.DeleteDate Is Null
and isnull(temp1.FileSize,0) > cast(@.HasDocs as decimal)
and SR.UserID = IsNull(@.CustomerID,SR.UserID)
and IsNull(SR.AssignedAgent,-1) =
isnull(Nullif(@.AssignedAgentID,-1),IsNull(SR.AssignedAgent,-1))
and IsNull(SR.AssignedStaffID,-1) =
isnull(Nullif(@.AssignedStaffID,-1),IsNull(SR.AssignedStaffID,-1))
) as t1
order by sortcol0, sortcol1, sortcol2, sortcol3, sortcol4 DESC,
sortcol5
--Create variable to identify the first and last record that should be
selected
SELECT @.TotalRecords = COUNT(*) FROM #TempTable
if @.CurrentPage > ceiling(cast(@.TotalRecords as float)/cast (@.PageSize
as float))
set @.CurrentPage = isnull(ceiling(@.TotalRecords / @.PageSize),1)
--select ceiling(cast(@.TotalRecords as float)/cast (@.PageSize as
float))
--select ceiling(cast(31/10 as float))
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
--Return the total number of records available as an output parameter
--Select one page of data based on the record numbers above
Select SR.RequestID, SR.RequestSeqID, SRS.StatusNameShort, SR.UserID,
SRS.StatusOrder, SR.SigningDate, SR.SigningTime, SR.LoanNumber,
Case When SR.InvoiceCreated is Null then 0 else 1 end as
InvoiceCreated,
Case When SR.Invoiced is Null then 0 else 1 end as Invoiced,
Case When SR.InvoicePaid is Null then 0 else 1 end as CustomerPaid,
Case When SR.NotaryPaid is Null then 0 else 1 end as NotaryPaid,
Case When SR.Invoiced is Null then '0' else '1' end + Case When
SR.InvoicePaid is Null then '0' else '1' end
+ Case When SR.NotaryPaid is Null then '0' else '1' end as IconSort,
SR.ContactName, Isnull(SR.ContactEmail,'') as ContactEmail,
Isnull(SR.ContactPhone,'') as ContactPhone,
-- SR.ContactName + '' + left(SR.LoanOfficerCompany,10) as
ContactInfo, SR.BorrowerLastName, SR.BorrowerFirstName,
Left(SR.ContactName,15) as ContactInfo, SR.BorrowerLastName,
SR.BorrowerFirstName,
SR.LoanOfficerCompany, left(SR.LoanOfficerCompany,10) as
LoanOfficerCompanyShort,
Case When Users.UserID Is Null then '(Assign Notary)' else
Users.FirstName + ' ' + Users.LastName end as AssignedAgentName,
IsNull(Users.UserID,0) as AssignedAgentID, isnull(SR.SigningCity,'')
+ ', ' + isnull(SR.SigningState,'') as CityState,
isnull(SR.SigningZip,'') as SigningZip,
SR.LastChangedByMobile,
cast(isnull(SR.DocsIn,0) as char(1)) + cast(isnull(SR.HudIn,0) as
char(1)) + cast(#TempTable.DocCount as char(1)) as HudDocsFlag,
Case cast(isnull(SR.DocsIn,0) as char(1)) + cast(isnull(SR.HudIn,0) as
char(1)) + cast(#TempTable.DocCount as char(1))
When '001' then 'Loan Docs and Title are not in. Other docs exist.'
When '011' then 'Loan Docs are not in. Title is in. Other docs
exist.'
When '101' then 'Loan Docs are in. Title is not in. Other docs
exist.'
When '111' then 'Loan Docs and Title are in. Other docs exist.'
When '000' then 'Loan Docs and Title are not in.'
When '010' then 'Loan Docs are not in. Title is in.'
When '100' then 'Loan Docs are in. Title is not in.'
When '110' then 'Loan Docs and Title are in.'
else 'Desc error.'
end as HudDocsFlagDesc,
#TempTable.FileSize,
Case When SR.UserID = 0 then '-' else 'C' end as CustomerFlag,
Case When SR.AssignedAgent = 0 and @.Maps='MSN' then
''
When SR.AssignedAgent <> 0 and @.Maps='MSN' then
replace(replace('http://maps.msn.com/directionsFind.aspx?strt1=' +
isnull(Users.Street,'') + '&city1=' + isnull(Users.City,'') + '&zipc1='
+ isnull(Users.PostalCode,'')
+ '&cnty1=0&strt2=' + isnull(SR.SigningAddress1,'') + '&city2=' +
isnull(SR.SigningCity,'') + '&zipc2=' + isnull(SR.SigningZip,'') +
'&cnty2=0&rtyp=1&unit=0',' ','%20'),'#','')
When SR.AssignedAgent = 0 and @.Maps='QUEST' then
''
When SR.AssignedAgent <> 0 and @.Maps='QUEST' then
replace(replace('http://www.mapquest.com/directions/main.adp?go=1&do=nw&rmm=
1&un=m&cl=EN&ct=NA&rsres=1&1a='
+ isnull(Users.Street,'') + '&1c=' + isnull(Users.City,'') + '&1s=' +
isnull(Users.Region,'') + '&1z=' + isnull(Users.PostalCode,'')
+ '&2a=' + isnull(SR.SigningAddress1,'') + '&2c=' +
isnull(SR.SigningCity,'') + '&2s=' + isnull(SR.SigningState,'') +
'&2z=' + isnull(SR.SigningZip,''),' ','%20'),'#','')
else ''
end as MapLink,
cast(isnull(ctbl_PortalData.FileSSLActivate,1) as varchar(1)) as
FileSSLActivate,
(isnull(SR.PriceQty_Agent1,0) * isnull(SR.PriceAmt_Agent1,0))
+(isnull(SR.PriceQty_Agent2,0) * isnull(SR.PriceAmt_Agent2,0))
+(isnull(SR.PriceQty_Agent3,0) * isnull(SR.PriceAmt_Agent3,0))
+(isnull(SR.PriceQty_Agent4,0) * isnull(SR.PriceAmt_Agent4,0))
+(isnull(SR.PriceQty_Agent5,0) * isnull(SR.PriceAmt_Agent5,0)) as
NotaryInvoiceTotal, @.TotalRecords as TotalRecords,
isnull(Staff.StaffInitials, Isnull(Staff.FirstName,'***')) as Staff,
isnull(Staff.StaffOrder,0) as StaffOrder
>From dbo.ctbl_SigningRequests SR
inner join #TempTable
on #TempTable.RequestID = SR.RequestID
Left Outer Join dbo.ctbl_SigningRequestStatus SRS
ON SR.SigningStatusID = SRS.SigningStatusID
Left Outer Join dbo.ctbl_UserData UD
ON SR.AssignedAgent = UD.UserID
Left Outer Join dbo.Users Users
ON SR.AssignedAgent = Users.UserID
Left Outer Join dbo.Users Staff
ON SR.AssignedStaffID = Staff.UserID
Left Outer Join ctbl_PortalData
On ctbl_PortalData.PortalID = @.PortalID
WHERE
ID > @.FirstRec
AND
ID < @.LastRec
order by #TempTable.[ID]Check the transaction isolation level: you can probably live with READ
COMMITTED. Also make sure that the procedure isn't running in the context o
f
a transaction. If that isn't the problem, then make sure that indexes exist
on the columns joined and that the execution plan uses them. You may have t
o
coerce the optimizer with a hint or two.
"jhonz@.etsmail.com" wrote:

> Hi. I am struggling to understand why I get the following error in
> using the stored procedure noted below. I recently starting using a
> temp table as a way of providing custom paging in asp.net and this
> problem has occured ever since (maybe 10 times per day with an average
> of 30 users on all day).
> Here is the error: "Transaction (Process ID ##) was deadlocked on lock
> resources with another process and has been chosen as the deadlock
> victim. Rerun the transaction"
> The client code is below. It uses a DataAdapter to fill a dataset that
> is used to populate a datagrid. The long store procedure is below
> that. It essentialy fills the temp table with records that are chosen
> then retrieves all the necessary fields for the datagrid using whatever
> page that is selected. There is code in there to support sorting and
> hopefully it's not too confusing.
> I apologize for the long post, I didn't want to remove parts of the SP
> to make it shorter in case I removed an important element. I admit, I
> am only an intermediate programmer so I may be missing some
> fundamentals. Hopefully this is obvious to someone.
> Thanks in advance.
> Jeff
>
> -- client code --
> ' Create Instance of Connection and Command Object
> Dim myConnection As New
> SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
> Dim myCommand As New SqlDataAdapter("tochange",
> myConnection)
> 'Dim myCommand As New SqlDataAdapter
> 'myCommand.SelectCommand.Connection = myConnection
> myCommand.SelectCommand.CommandType =
> CommandType.StoredProcedure
> myCommand.SelectCommand.CommandText =
> "dbo.csp_cGeneral_GetRequests2"
> myCommand.SelectCommand.Parameters.Add("@.PortalID",
> SqlDbType.Int).Value = iPortalID
> myCommand.SelectCommand.Parameters.Add("@.Status",
> SqlDbType.VarChar, 10).Value = Status
> myCommand.SelectCommand.Parameters.Add("@.RequestSeqID",
> SqlDbType.Int).Value = RequestSeqID
> myCommand.SelectCommand.Parameters.Add("@.BorrowerLastName",
> SqlDbType.VarChar, 30).Value = BorrowerLastName
> myCommand.SelectCommand.Parameters.Add("@.LoanOfficerCompany",
> SqlDbType.VarChar, 30).Value = LoanOfficerCompany
> myCommand.SelectCommand.Parameters.Add("@.BegDate",
> SqlDbType.VarChar, 25).Value = BegDate
> myCommand.SelectCommand.Parameters.Add("@.EndDate",
> SqlDbType.VarChar, 25).Value = EndDate
> myCommand.SelectCommand.Parameters.Add("@.ContactName",
> SqlDbType.VarChar, 30).Value = ContactName
> myCommand.SelectCommand.Parameters.Add("@.AgentID",
> SqlDbType.Int).Value = AgentID
> myCommand.SelectCommand.Parameters.Add("@.HasDocs",
> SqlDbType.Int).Value = HasDocs
> myCommand.SelectCommand.Parameters.Add("@.AssignedStaffID",
> SqlDbType.Int).Value = iStaffSearchID
> myCommand.SelectCommand.Parameters.Add("@.CurrentPage",
> SqlDbType.Int).Value = _currentPageNumber
> myCommand.SelectCommand.Parameters.Add("@.PageSize",
> SqlDbType.Int).Value = iPagesize
> myCommand.SelectCommand.Parameters.Add("@.SortField",
> SqlDbType.VarChar, 30).Value = strSortColumn
> myCommand.SelectCommand.Parameters.Add("@.UserID",
> SqlDbType.Int).Value = UserID
> myCommand.SelectCommand.Parameters.Add("@.Role",
> SqlDbType.VarChar, 20).Value = Role
> myCommand.SelectCommand.Parameters.Add("@.Maps",
> SqlDbType.VarChar, 20).Value = sMaps
> ' Create and Fill the DataSet
> Dim myDataSet As New DataSet
> myCommand.Fill(myDataSet, "Requests")
> Dim myTable As DataTable = myDataSet.Tables("Requests")
> If Not myTable Is Nothing Then
> If myTable.Rows.Count > 0 Then
> Dim dr As DataRow = myTable.Rows(0)
> _TotalRecords = dr.Item("TotalRecords")
> Else
> _TotalRecords = 0
> End If
> End If
> ' Return the DataSet
>
> -- stored procedure --
> ALTER procedure dbo.csp_cGeneral_GetRequests2
> @.PortalID int,
> @.Status varchar(10) = "-1",
> @.RequestSeqID int = -1,
> @.BorrowerLastName varchar(30) = "-1",
> @.LoanOfficerCompany varchar(30) = "-1",
> @.BegDate varchar(25) = "-1",
> @.EndDate varchar(25) = "-1",
> @.ContactName varchar(30) = "-1",
> @.AgentID Int = Null,
> @.UserID int = 0,
> @.Role varchar(20) = 'None',
> @.HasDocs decimal = -1,
> @.CurrentPage int,
> @.PageSize int,
> @.SortField varchar(30),
> @.Maps varchar(20),
> @.AssignedStaffID Int --(-1 all, -2 not in list)
> as
> --if @.AssignedStaffID = -2
> --begin
> --
> --end
> Declare @.TotalRecords int
> Declare @.Status1 int
> Declare @.Status2 int
> Declare @.AssignedAgentID int
> Declare @.CustomerID int
> set @.AssignedAgentID = 0
> set @.CustomerID = 0
> if @.Role = 'NotaryAgent'
> Begin
> set @.AssignedAgentID = @.UserID
> set @.CustomerID = Null
> end
> if @.Role = 'Customer'
> Begin
> set @.AssignedAgentID = Null
> set @.CustomerID = @.UserID
> end
> if @.Role = 'ServiceOwner'
> Begin
> set @.AssignedAgentID = Null
> set @.CustomerID = Null
> end
> if @.Role = 'AgentOwner'
> Begin
> set @.AssignedAgentID = Null
> set @.CustomerID = Null
> end
> set @.Status1 = -1
> set @.Status2 = -1
> if len(@.Status) = 1 or (len(@.Status) = 2 and not @.Status = '45')
> begin
> set @.Status1 = cast(@.Status as int)
> set @.Status2 = cast(@.Status as int)
> end
> else
> begin
> if @.Status = '123'
> Begin
> set @.Status1 = 1
> set @.Status2 = 3
> end
> if @.Status = '45'
> Begin
> set @.Status1 = 4
> set @.Status2 = 5
> end
> if @.Status = '123456'
> Begin
> set @.Status1 = 1
> set @.Status2 = 6
> end
> if @.Status = '1234569'
> Begin
> set @.Status1 = 1
> set @.Status2 = 10
> end
> end
> Declare @.BegDate2 as smalldatetime
> Declare @.EndDate2 as smalldatetime
> if @.BegDate = '-1' or @.EndDate = '-1' or isdate(@.BegDate) = 0 or
> isdate(@.EndDate) = 0
> begin
> set @.BegDate2 = Null
> Set @.EndDate2 = Null
> end
> else
> begin
> set @.BegDate2 = cast(@.BegDate as smalldatetime)
> Set @.EndDate2 = cast(@.EndDate as smalldatetime)
> end
> CREATE TABLE #TempTable
> (
> ID int IDENTITY PRIMARY KEY,
> RequestID int,
> FileSize int,
> DocCount int
> )
> INSERT INTO #TempTable
> (
> RequestID,
> FileSize,
> DocCount
> )
> SELECT
> RequestID,
> FileSize,
> DocCount
> Select SR.RequestID, isnull(FileSize,0) as FileSize,
> isnull(temp1.DocCount,0) as DocCount,
> CASE @.SortField
> WHEN 'Request' THEN 0
> WHEN 'Status' THEN SRS.StatusOrder
> WHEN 'Borrower' THEN 0
> WHEN 'Date' THEN 0
> WHEN 'Location' THEN 0
> WHEN 'ContactInfo' THEN 0
> WHEN 'Agent' THEN 0
> WHEN 'FileSize' THEN 0
> WHEN 'HasCust' THEN SR.UserID
> WHEN 'StaffOrder' THEN Staff.StaffOrder
> ELSE SRS.StatusOrder
> END AS sortcol0,
> CASE @.SortField
> WHEN 'Request' THEN ''
> WHEN 'Status' THEN ''
> WHEN 'Borrower' THEN SR.BorrowerLastName
> WHEN 'Date' THEN convert(varchar(20),SR.SigningDate,112)
> WHEN 'Location' THEN isnull(SR.SigningCity,'')
> WHEN 'ContactInfo' THEN SR.ContactName
> WHEN 'Agent' THEN Users.LastName
> WHEN 'FileSize' THEN ''
> WHEN 'HasCust' THEN '0'
> WHEN 'StaffOrder' THEN Staff.StaffInitials
> ELSE ''
> END AS sortcol1,
> CASE @.SortField
> WHEN 'Request' THEN '0'
> WHEN 'Status' THEN convert(varchar(20),SR.SigningDate,112)
> WHEN 'Borrower' THEN SR.BorrowerFirstName
> WHEN 'Date' THEN SR.SigningTime
> WHEN 'Location' THEN isnull(SR.SigningState,'')
> WHEN 'ContactInfo' THEN SR.LoanOfficerCompany
> WHEN 'Agent' THEN Users.FirstName
> WHEN 'FileSize' THEN '0'
> WHEN 'HasCust' THEN '0'
> WHEN 'StaffOrder' THEN '0'
> ELSE convert(varchar(20),SR.SigningDate,112)
> END AS sortcol2,
> CASE @.SortField
> WHEN 'Request' THEN '0'
> WHEN 'Status' THEN SR.SigningTime
> WHEN 'Borrower' THEN '0'
> WHEN 'Date' THEN '0'
> WHEN 'Location' THEN '0'
> WHEN 'ContactInfo' THEN '0'
> WHEN 'Agent' THEN '0'
> WHEN 'FileSize' THEN '0'
> WHEN 'HasCust' THEN '0'
> WHEN 'StaffOrder' THEN '0'
> ELSE SR.SigningTime
> END AS sortcol3,
> CASE @.SortField
> WHEN 'Request' THEN 0
> WHEN 'Status' THEN 0
> WHEN 'Borrower' THEN 0
> WHEN 'Date' THEN 0
> WHEN 'Location' THEN 0
> WHEN 'ContactInfo' THEN 0
> WHEN 'Agent' THEN 0
> WHEN 'FileSize' THEN FileSize
> WHEN 'HasCust' THEN 0
> WHEN 'StaffOrder' THEN 0
> ELSE 0
> END AS sortcol4,
> SR.RequestSeqID AS sortcol5
> Left Outer Join dbo.ctbl_SigningRequestStatus SRS
> ON SR.SigningStatusID = SRS.SigningStatusID
> Left Outer Join dbo.ctbl_UserData UD
> ON SR.AssignedAgent = UD.UserID
> Left Outer Join dbo.Users Users
> ON SR.AssignedAgent = Users.UserID
> Left Outer Join ctbl_PortalData
> On ctbl_PortalData.PortalID = @.PortalID
> Left Outer Join dbo.Users Staff
> ON SR.AssignedStaffID = Staff.UserID
> Left Outer Join (
> Select ctbl_Docs.RequestID,
> case when sum(Case when ctbl_Docs.LoanDocs = 0 and
> ctbl_Docs.TitleDocs = 0 then 1 else 0 end) > 0 then 1 else 0 end as|||Thanks, Brian. Couldn't I use WITH (NOLOCK) on the select that fills
the temptable and then later selects from it? READ COMMITTED seems to
be SQL 2000 default and is probably arleady set. I image the locks or
on the select a temp table should not be shared amongts users. In
ASP.Net's connection pooling, do you think things could get crossed
there.
I am not running a transaction so I think I am safe there.
I ran the execution plan and I don't see any table scans. Is that
sufficient indication that things are OK there?
Jeff

Wednesday, March 21, 2012

Deadlock on Update using temp table

I sometimes get the following error from an update statement in a
stored procedure:

Transaction (Process ID 62) was deadlocked on thread | communication
buffer resources with another process and has been chosen as the
deadlock victim. Rerun the transaction.

The isolation level is READ UNCOMMITTED and there are no explicit
transactions in the stored procedure. The update statement is as
follows:

UPDATE PL
SET PL.PL_SI_LAST_YEAR_AMOUNT = #tmpWorkPLPrior.PRIOR_AMOUNT
FROM #tmpWorkPLPrior
WHERE PL.COMPANY = @.comp
AND PL.PLAN_YEAR = @.year
AND PL.FORECAST_QUARTER = @.qtr
AND PL.VERSION_ID = @.ver
AND PL.BUSINESS_UNIT_CODE = #tmpWorkPLPrior.BUSINESS_UNIT
AND PL.PROJECT_ID = #tmpWorkPLPrior.PROJECT_ID
AND PL.BUDGET_CODE = #tmpWorkPLPrior.BUDGET_CODE
AND PL.BUSINESS_UNIT_CODE <> 'G7'

PL rows: 24,342,553
PL rows - Filtered: 230,088
#tmpWorkPLPrior rows: 3,641
Updated rows: 43,692

The temp table (#tmpWorkPLPrior) is created by a SELECT INTO statement.
It has the values that need to be set in the PL table. The PL table
has a clustered index on 8 columns. The filters (@.comp, @.year, ...)
select 230,088 rows. When the update succeeds it updates 43,692 rows
in about 15 seconds. Why does this sometimes deadlock and other times
succeed? There is nothing else running, so the process is deadlocking
on itself.

Thanks,
FrankHi Frank

I don't have the skills and time to analyse your specific issue, but I
hope this general link could help you:
SQL Server technical bulletin - How to resolve a deadlock:
http://support.microsoft.com/kb/832524/en-us

Cheers
SMF|||Hi

You don't give the version of SQL Server you are running?

In general it is better to create the temporary table separately.

Heavy use of tempdb may be helped by moving it to it's own drive(s) and
using multiple files see
http://support.microsoft.com/defaul...kb;en-us;328551

Also check out http://support.microsoft.com/kb/271509/EN-US/ and
http://support.microsoft.com/kb/224453/EN-US/ on how to identify blocking.

John

<fmatamoros@.yahoo.com> wrote in message
news:1135823370.065365.186970@.g44g2000cwa.googlegr oups.com...
>I sometimes get the following error from an update statement in a
> stored procedure:
> Transaction (Process ID 62) was deadlocked on thread | communication
> buffer resources with another process and has been chosen as the
> deadlock victim. Rerun the transaction.
> The isolation level is READ UNCOMMITTED and there are no explicit
> transactions in the stored procedure. The update statement is as
> follows:
> UPDATE PL
> SET PL.PL_SI_LAST_YEAR_AMOUNT = #tmpWorkPLPrior.PRIOR_AMOUNT
> FROM #tmpWorkPLPrior
> WHERE PL.COMPANY = @.comp
> AND PL.PLAN_YEAR = @.year
> AND PL.FORECAST_QUARTER = @.qtr
> AND PL.VERSION_ID = @.ver
> AND PL.BUSINESS_UNIT_CODE = #tmpWorkPLPrior.BUSINESS_UNIT
> AND PL.PROJECT_ID = #tmpWorkPLPrior.PROJECT_ID
> AND PL.BUDGET_CODE = #tmpWorkPLPrior.BUDGET_CODE
> AND PL.BUSINESS_UNIT_CODE <> 'G7'
> PL rows: 24,342,553
> PL rows - Filtered: 230,088
> #tmpWorkPLPrior rows: 3,641
> Updated rows: 43,692
> The temp table (#tmpWorkPLPrior) is created by a SELECT INTO statement.
> It has the values that need to be set in the PL table. The PL table
> has a clustered index on 8 columns. The filters (@.comp, @.year, ...)
> select 230,088 rows. When the update succeeds it updates 43,692 rows
> in about 15 seconds. Why does this sometimes deadlock and other times
> succeed? There is nothing else running, so the process is deadlocking
> on itself.
> Thanks,
> Frank|||Hi,

Don't get distracted, the temporary table is not causing your deadlock,
rather the update on PL is.

Its feasible that your connection is locking index pages, data pages that
other connections also have locked before you grab them - you are updating
quite a lot of rows in one go so the transaction will be quite large.

Have you checked the query plan for this UPDATE? Index on some of the
columns in your WHERE clause will help reduce the IO.

READ UNCOMMITTED is redundant on the UPDATE - the locks will be placed
because you are updating the data, you could use READ UNCOMMITTED on ALL
your readers and that would help.

I don't tend to use such large composite keys like this, I would use a
surrogate - 'ID' integer column instead and update by joining using that (if
thats possible in your case).

Reading your end bit, if you are absolutely sure that nothing else is
accessing that table then the plan is probably deadlocking itself because of
parallelism which can happen, you can stop parallelism by using MAXDOP 1 on
the OPTIONS clause of the UPDATE statement.

Hope that helps.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

Thanks,
Frank

Tuesday, February 14, 2012

Dblocks with temporary table

Hi,

I am using temp table in my stored procedure. The temp table is first created and rows are inserted. Then I am selecting data from same temp table. Everything is inside the stored procedure. This stored procedure is called from a Java (EJB) program with at least 100 threads at the same time.

But when the Java program runs and calls this stored procedure, it is resulting in many dblocks. Can any one explain why this is happening ? I appreciate any help on this.

Does each thread create its own copy of temp table ?
Is temdb locked in this process ?
Do I need to drop the tamp table at the end ? ( I am not dropping now)
What are the other alternatives ?

I am on SQL Server 7, windows 2000/NT.

Thanx..
-BheemsenThe temp table is first created and rows are inserted...

Which approach do you use?

1. select into
2. create table + insert

Select into locks system tables in MSSQL7.|||Thanx ispaleny. I am using the 2 option.
i.e. create table, then insert, then select.

-Bheemsen|||I use MSSQL2k.

#tables are created unique for each thread
#tables created in scope of SP exist only in scope of SP|||RE:
Thanx ispaleny. I am using option 2. (create table, then insert). -Bheemsen

Question I
Since it isn't a simple select into locking issue, what are the server's wait states like when the issue presents itself? Also, what kinds of locks are being issued and in what proportion, and is the Java app possibly spawning multiple connections (rather than reusing when possible) and / or not closing out connections when done with them?

wait states data gathering example:

Select
Spid,
Waittime,
Lastwaittype,
Waitresource
From
Master..Sysprocesses
Where
Waittime > 300

Sunday, March 25, 2012

deadlock victim even using temp table

Wednesday, March 21, 2012

Deadlock on Update using temp table

Tuesday, February 14, 2012

Dblocks with temporary table

deAttach database

Blog Archive

About Me