insert query now causing other queries to no longer work

Question

I ran this query on my SQL Server database:

begin transaction;
insert into [db].[a].[Table1] ( [IsDeleted], [itemId], [regionId], [deskId], [LastUpdated], [LastUpdatedby])
select  [IsDeleted], [itemId], [regionId], 11, [LastUpdated], [LastUpdatedby]
from [db].[a].[Table1]
where deskId = 12;

update [db].[a].[Table1]
set deskId = 3
where deskId = 12;
commit transaction;

there are records that should have changed but it said (O rows affected).

i now went to do a:

Select * from [db].[a].[Table1]

and its just spinning with "Executing Query" for minutes now with no end in site.

1. Does anyone see anything wrong with my first query that would cause some type of lock issue.
2. Also, now that i am in this state, how do i get out of it?

Answer 1

Is the first script still actually executing the Update Statement? If so this is to be expected as you are selecting rows that are not yet committed.

If not and it is apparently idle then I don't think you can have run the whole of the first script. At any rate it seems the transaction is still open and needs to be rolled back or committed.

You can confirm this by running select * from sys.dm_os_waiting_tasks and looking at the blocking_session_id or if you are on SQL2000 you would need to use exec sp_who2

Is this in management studio? If so go to the window with the insert statement and (in the case that the Update isn't still in progress) run either rollback or commit as desired.

If you select some text in SSMS and then run it will just execute the selected text. Which might explain how only part of the script could be run.

Answer 2

If you are updating the first table still, then the read will not work; if you stop the query (first one) it will roll back, but that might take some time.

Change the select to this:

Select * from [db].[a].[Table1] WITH(NOLOCK)

And it should return right away.

Hope that helps