BEGIN / END / COMMIT TRAN (SQL 2000)

[mutley1]

Hi guys,

Sorry if I am sounding thick here, but the brain has died at the end of the day.

I know even a select statement will put a shared lock on a table, but I have a procedure that is causing all sorts of issues. It has a

BEGIN
select stuff
union
select other stuff
union
select more stuff
END

but occasionally sits there doing nothing. There is always data to be retrieved, but I have had occasions where it sits there as runnable but locks everything else out of the AG table, even though it is a select (and not vast quantities of data). This is a problem because the AG table is replicated and this blocks updates coming from production to the table in the DB that web users get the data from (transactional replication). Looking at ALL locks and blocks, this is the lead blocker and it causes a huge backlog on other reports.

Question is, is there a difference between BEGIN / END and BEGIN / COMMIT TRAN , and any ideas what may cause the select to sit there locking and doing naff all when there is nothing blocking it? I can see the OTHER processes are trying to get LCK_M_S (or IX depending) but the waitresource is on KEY x:xxxxxxxxxxx:x which I have checked in sysobjects and it is the table being selected from in the DB as found in sysobjects. I am loathe to put a NO LOCK hint in as it may stop the other items being blocked but I would have no indication of that report hanging and rather fix the problem.

If I kill the process, things run through, but someone starts it again and it locks everything up. Failing over to the other node in the cluster and back solves the whole issue for another 3 months.

DBCC CHECKDB clear
DBCC CHECKALLOC clear
DBCC CHECKTABLE clear.......

Any help appreciated.

TIA,

M

 

[markros]

What is the transaction isolation level set to?

What if you add WITH (NOLOCK) hint to all your selects?

 

[mutley1]

Someone has remmed out the READ UNCOMMITTED level. I assume it is because it is a detailed financial report for split second detail. I'll see if I can find out which developer remmed it out!

The other thing is I am loathe to put a NO LOCK hint in as it may stop the other items being blocked but I would have no indication of that report hanging and rather fix the problem.

Also, even with a READ UNCOMMITTED, this report may still hang. It is the lead block whenever it goes boo boo, so other stuff will then run through but this may still sit there hanging. As mentioned, we failed over and back and the report runs fine again for a few months.

Ta,

M.

 

[Crowley16]

have you checked that the statistics are up to date and the indexes are not too fragmented?

--------------------
Procrastinate Now!

 

[mutley1]

Yes - there are a couple of jobs that run overnight to update stats and indexdefrag. A showcontig near the end of the day shows good results on the tables too. Really confused.

 

[gmmastros]

Do you really need to UNION the results. UNION will filter out duplicates. If your data is already unique, you will get better performance using UNION ALL.

I won't begin to suggest that this will resolve all your problems, but it may help a bit. This is especially true if this query is returning a lot of data. Determining of the data is distinct may be the reason it is slow, which would lock your records for a longer period of time, etc....



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom