Is IDENTITY_INSERT OFF safe?

Say I have a sproc that is occasionally used to restore deleted records to a
table.
--If I use
SET IDENTITY_INSERT myTable ON
--Then insert the records...
INSERT INTO myTable(ID, AnyField)
VALUES (2,'hello);
--Then I return table to original state
SET IDENTITY_INSERT myTable OFF
During that process, what would happen if another user was attempting to
insert records into the same table?
Even if my transaction only takes .2 milliseconds, is it possible another
user will get an error, or is SQL Server smart enough to delay their
transaction(s) [lock the table] until mine is complete?
Thanks,
ChrisOther updaters are not blocked by SET IDENTITY_INSERT ON. But this is not a
problem since the IDENTITY_INSERT ON applies only to the connection that
runs it. The identity column is handled normally in all other connections
(unless, of course, that connection has also set IDENTITY_INSERT ON.
So, yes, it is safe to use.
Tom
"Chris" <rooster575@.hotmail.com> wrote in message
news:OsTb2VdiGHA.4512@.TK2MSFTNGP02.phx.gbl...
> Say I have a sproc that is occasionally used to restore deleted records to
> a table.
> --If I use
> SET IDENTITY_INSERT myTable ON
> --Then insert the records...
> INSERT INTO myTable(ID, AnyField)
> VALUES (2,'hello);
> --Then I return table to original state
> SET IDENTITY_INSERT myTable OFF
> During that process, what would happen if another user was attempting to
> insert records into the same table?
> Even if my transaction only takes .2 milliseconds, is it possible another
> user will get an error, or is SQL Server smart enough to delay their
> transaction(s) [lock the table] until mine is complete?
> Thanks,
> Chris
>|||Thanks Tom!
"Tom Cooper" <tom.no.spam.please.cooper@.comcast.net> wrote in message
news:WsmdnSkiXZ4uqBvZnZ2dnUVZ_vqdnZ2d@.co
mcast.com...
> Other updaters are not blocked by SET IDENTITY_INSERT ON. But this is not
> a problem since the IDENTITY_INSERT ON applies only to the connection that
> runs it. The identity column is handled normally in all other connections
> (unless, of course, that connection has also set IDENTITY_INSERT ON.
> So, yes, it is safe to use.
> Tom
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:OsTb2VdiGHA.4512@.TK2MSFTNGP02.phx.gbl...
>|||ACID
Isolation