Lock TIme request - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Lock TIme request

Author

Topic

joseerengan
Starting Member

4 Posts

Posted - 2007-02-08 : 05:03:05

We have developed a web application using ASP.NET, VB.NET & SQL Server 2000. We are frequently getting "Lock timeout request" error in the production environment. So that its affect the routine business everyday severely.
Related to this, In our perception is while doing full index scanning on table, its locks the table.

Kindly guide us to rectify the problem at the earliest.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-08 : 05:24:57

Do you have any index on the table?
How do you connect and release the connection from the application?

Peter Larsson
Helsingborg, Sweden

joseerengan
Starting Member

4 Posts

Posted - 2007-02-08 : 05:30:36

1. Yes we are using index on this table 1 clustered index and 2 non clustered index (all used queries are having 3 to 4 tables are linked even update query itself)
2. We are using one separate component for connect and release (our own component)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-08 : 05:38:23

2) Do you connect once and do all processing during user session, and then close connection

Or do you connect and do some processing and close the connection and open/close again for next user action?

Peter Larsson
Helsingborg, Sweden

joseerengan
Starting Member

4 Posts

Posted - 2007-02-08 : 06:47:40

1 . Once we open one connection we finish the all the transactiob works and close the connection immediately.

As per our knowledge connection open & closing is not a problem.Because concurrent access only this problem is occurred.Once row level lock will apply instead of table lock our problem will solved.One sample query i have attached for your reference

Update ABD With (RowLock)
Set
ABD.OSAmt = Case When ILD.TxnType = 'C' Then
ABD.OSAmt + ILD.TxnAmt
When ILD.TxnType = 'D' Then
ABD.OSAmt - ILD.TxnAmt
Else ABD.OSAmt
End,
ABD.AmtDueTo = Case When ILD.TxnType = 'DEM' Then
ABD.AmtDueTo + ILD.TxnAmt
When ILD.TxnType = 'D' Then
Case When (ABD.AmtDueTo - ILD.TxnAmt) >= 0 Then
ABD.AmtDueTo - ILD.TxnAmt
Else 0 End
Else ABD.AmtDueTo
End,
ABD.AmtDueFrm = ABD.AmtDueFrm,
ABD.Advncoln = ABD.Advncoln,
ABD.ValueDt = @p_BusnDt
From
DEP_tAcctBaln AB,
DEP_tAcctBalnDetl ABD,
DEP_tInstLdgrHdr ILH,
DEP_tInstLdgrDet ILD
Where
AB.BrID = @p_BrID
And AB.PrdID = @p_PrdID
And AB.AcctID = @p_AcctID
AND ILH.BusnDt = @p_BusnDt --Where Clause added by SJA on 03-11-2005
And ILH.OprBrID = @p_OprBrID
And ILH.InsGrpID = @p_InsGrpID
And ILD.InsGrpSlNo = @p_InsGrpSlNo
And AB.BrID = ILH.BrID
And AB.PrdID = ILH.PrdID
And AB.AcctID = ILH.AcctID
And AB.PK_ID = ABD.BalnHdr_FK
And ILH.PK_ID = ILD.TxnInsHdr_FKID
And AB.PK_ID = ABD.BalnHdr_FK
And ILD.OptnTypeID = ABD.OptnTypeID

This query is lock the DEP_tAcctBalnDetl table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources