SQL Server locks up on INSERT/SELECT when columns don't match...but only in SQL Server Manageme-CodePudding

I recently managed to lock up our SQL Server database with a query like this (simplified for clarity):

SELECT *, NULL AS extraColmn
INTO #tempTable
FROM sourceTable

IF @@ROWCOUNT > 0
BEGIN
    BEGIN TRANSACTION;

    DELETE FROM sourceTable

    INSERT INTO sourceTable
    SELECT * FROM #tempTable;

    COMMIT TRANSACTION;

    DROP TABLE #tempTable
END

There are two obvious problems with this. First, the INSERT/SELECT statement should have the columns explicitly listed. Second, when doing that INSERT, the source and destination tables had a different number of columns. As expected, this caused an error:

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

And the query simply failed on our production server.

But strangely, when I ran this same query in SQL Server Management Studio, it gave that error, but then locked up the entire database, to the point where I had to restart the database server.

Why? Is it the transaction? I'm a newbie at those, but I understood that if an error is encountered in a transaction, it's simply rolled back—that's the point. (Note that sourceTable is fairly heavily used on this site, so there would probably have been a few concurrent attempts to access it while this was running.)

Why would this only occur in SQL Server Management Studio?

CodePudding user response：

IIRC, the default settings in SSMS mean that an error will cause the rest of the batch (until the next GO or the end of the commands) to be skipped. So the transaction would still be open and bound to your SSMS window's session. So if you don't either close the session window or else explicitly rollback the transaction in that window, it will stay open until you do (or you restart the server).

This is what I assume happened to you. The simple fix is normally to just disconnect or close the SSMS session window where the error was thrown. Alternatively you can manually execute a ROLLBACK command in that session so that you don't lose the window or have to re-connect.

A client program receiving this error will usually end up closing the connection (either through error-handling or because it crashes), thus ending the session and it's transaction.