July 15, 2009 at 12:52 pm
Hello All,
I have a procedure that runs every 1 minute in my production database. The beginning of the proc has a "Begin Tran", inside that, there are many statements that execute but my question is this:
There is this statement inside the tran:
"select * from parentTable into #temp order by parentTableID desc"
after that select executes and fills the #temp table, does the tran keep a lock on the "parentTable" or will the tran allow new records to get inserted into that table even while the "tran" is still working?
Thanks,
July 15, 2009 at 1:30 pm
Alot of this depends on what exactly is happening in your transaction, but in it's simplest form no.
It you open 2 query windows both with connections to Tempdb you can see what I mean...
In the first window paste the following
--Do this someplace safe
USE tempdb
GO
--create out test table
CREATE TABLE test (col1 VARCHAR(10))
GO
INSERT INTO test
SELECT 'blah' UNION ALL
SELECT 'blah' UNION ALL
SELECT 'blah' UNION ALL
SELECT 'blah' UNION ALL
SELECT 'blah' UNION ALL
SELECT 'blah' UNION ALL
SELECT 'blah' UNION ALL
SELECT 'blah'
--Start the test
Begin TRANSACTION
SELECT *
INTO #temp
FROM test
WAITFOR DELAY '00:00:15'
COMMIT TRANSACTION
--Cleanup
DROP TABLE test
DROP TABLE #temp
In the second one enter this...
SELECT *
FROM test
INSERT INTO test
SELECT 'blah2' UNION ALL
SELECT 'blah2' UNION ALL
SELECT 'blah2' UNION ALL
SELECT 'blah2' UNION ALL
SELECT 'blah2' UNION ALL
SELECT 'blah2'
SELECT *
FROM test
Run window 1 and while it's waiting and "doing the rest of the work of the transaction" switch to window 2 and run it. You'll see it does not cause a block on inserting new records... Granted this is a VERY simplistic example and other things going on with your db/app could impact these results...
-Luke.
July 15, 2009 at 1:47 pm
I would not think it would continue to hold a lock from that statement, but as Luke mentioned, it depends on what else is happening in the procedure.
July 15, 2009 at 2:10 pm
Thanks Luke.
That sums up what I was getting at (inserting more records into "parentTable" while working with the original list that got inserted into #temp that is still inside the tran).
July 15, 2009 at 2:20 pm
July 16, 2009 at 10:50 am
Hi Luke/Steve,
As a follow-up to the original question, if there are 4 Select Into statements all inside the Tran, does it lock all 4 tables at the beginning of the Tran, or does it wait to do the lock until it gets to the Select Into for that particular table?
Thanks again.
July 16, 2009 at 11:11 am
I would imagine that it wouldn't lock those tables, except during the read to keep acid principles intact. That said it all depends on what you're doing with the data and what else the procedure does...
An easy way to test all of this - in a Dev/QA/Test environment of course - is to modify the procedure and add a waitfor in various places throughout your procedure. Then you can run the procedure, and switch to another window and run sp_who2 and check the DMVs to check for blocking and see what you can see. By moving the waitfor around you'll be able to see what gets locked when if you're trying to troubleshoot some blocking issues within your app.
-Luke.
July 16, 2009 at 12:33 pm
I agree with Luke. If you start a tran, the optimizer doesn't lock everything referenced.
Begin tran
waitfor('1:00:00')
update sales
set price = price * 1.5
commit tran
The sales table will not be locked for an hour after this starts to run. The locks occur as statements are executed.
Depending on your isolation level, a SELECT .. INTO may or may not lock the tables, and it depending on how large things are, it might not lock more than a few pages at a time. The locks for a SELECT should be shared locks, so they might prevent updates or deletes, possibly inserts, but they should not prevent other reads.
July 16, 2009 at 12:50 pm
Thanks again Luke and Steve!
To give you guys more background information on my process/problem that I have (if you care)...
On a day-to-day basis, there are approximately 2000 inserts into table A.
Table A has a trigger that will do an insert/update into Table B for each insert into table A.
Meanwhile, there is a batch job that runs every 1 minute during production hours, the batch job does 8 different (but only writing about 1) "Select * From Table B Into #temp" (so table B can continue to receive new rows but don't process them during the batch job) all inside a Tran.
Quest Performance Tools says the batch job is doing all of the "blocking" against Table B (and somehow inserts into table A are timing out also).
The trigger on Table A shows alot of "LockWait" which is waiting for the batch job that contains the Tran.
Then, all of a sudden, for no rhyme or reason (that we've found yet), it will run perfectly for the rest of the day...no blocking...batch job runs in 3 seconds as opposed to a max run time of 13 minutes when the problem is occurring.
Quest tools does not show any other sql activity that could be the culprit.
We are in the process of trying to find out what is causing the batch job to run so long, then all of a sudden, it's perfect.
Anyway, in case you were curious...
July 16, 2009 at 1:07 pm
Without some more details on your situation it would be hard for us to guess where/why your issues are occurring. You might be better off taking a look at the 2 articles listed in my signature and get together some sample data and DDL/DML and post your problem in the 2005 Performance Tuning Forum. You'll be more likely to get more eyes on it than you would as an off shoot of this topic.
Make sure you post a complete wrap-up of your problem and what you're seeing from your tuning tools when the locking is occurring. Is there any chance that there is another process also hitting one of those tables which get a lock and then the lock chains start to cascade as your job that run every minute keeps re-queueing itself? See if you can identify the SPID and trace that back to a particular statement product or workstation.
Cheers
-Luke.
July 23, 2009 at 8:21 am
Luke and Steve, just to let you guys know. This issue was resolved for me.
We found out that security patches went onto our SQL Server two nights before the issue started happening, thus requiring a reboot of the box. Last night, we failed over, and then rebooted our primary sql server, then rebuilt the indexes for the table that the job hits, and the problem has completely disappeared.
I do not know which of the 2 actually solved the problem though...but at least it is fixed.
Thanks for your help guys, I gained alot of knowledge from that one question.
July 23, 2009 at 8:29 am
Glad you got it sorted, though I would imagine that the security patches probably wouldn't have been the issue at least if it was the patches from this month, nothing major for SQL Server, more likely it was the index maintenance you did.
-Luke.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply