SQL Server - is using @@ROWCOUNT safe in multithre

问题:

I am using SQL Server 2008.

I have a table A which accepts many insert/update in one seconds. After insert, update I want to get the number of rows affected.

INSERT INTO A (ID) VALUES (1)
IF @@ROWCOUNT = 0
    PRINT 'NO ROWS AFFECTED'

While query is being executed, the same query may be called again by application. So what happens if the current execution is after INSERT but before IF block at that moment.

Do you think @@ROWCOUNT may give wrong result for that reason?

Or is it always safe in its context?

回答1:

Yes - its safe. It always refers the previous operation in current query

BUT

if you want to know the number of rows affected, save it to variable first, because after IF statement the count @@ROWCOUNT resets

INSERT INTO A (ID) VALUES (1)
DECLARE @rc INT = @@ROWCOUNT
IF @rc = 0
    PRINT 'NO ROWS AFFECTED'
ELSE
  SELECT @rc AS RowsAffected

回答2:

@@ROWCOUNT is both scope and connection safe.

In fact, it reads only the last statement row count for that connection and scope. The full rules are here on MSDN (cursors, DML, EXECUTE etc)

To use it in subsequent statements, you need to store it in a local variable.

回答3:

You must preserve the @@ROWCOUNT value in a local variable, otherwise after the IF statement its value will reset to zero:

SET @rowCount = @@ROWCOUNT

IF @rowCount = 0 
   PRINT 'NO ROWS AFFECTED' 

Other than that, yes, it is safe.

回答4:

Short answer: Yes.

However it worth to see the question in a perspective, for the deeper understanding why the answer yes is so natural without doubt.

SQL Server is prepared to handle concurrent access correctly by its nature, regardless if the client application is multithreaded or not. Unless this attribute SQL Server would be useless in any multiuser scenario. From point of view of the server it does not matter if the concurrent access caused by one multithreaded application, or two applications which are currently using the server concurrently by multiple users.

Regarding this point the @@rowcount is only the top of the iceberg, there are much more and deeper functionality what must be handled correctly when concurrent access is in the picture.

The most practical part of this area is transaction management and transaction isolation.