SQL Server insert locking with concurrent transactions
Assume I have procedure A wrapped in a transaction that is using read uncommitted isolation level with an insert statement into table A that has an identity column followed by a series of select statements. The insert statement does not request a TABLOCKX.
If multiple calls are issued to this procedure in parallel will the calls be serialized because of the insert statement in the transaction or will they be allowed to execute concurrently? I know if this was an update statement changing the exact same row for each call that they would be serialized but what about an insert statement with identity column? Does this cause the transaction to hold an exclusive lock on the table for the duration of the transaction?
Assume SQL server version is 2008 R2 if this makes a difference.
I don't have SQL 2008 handy, but in SQL 2012 it will allow any of the transactions to complete first (I assume it's the same in 2008).
It's pretty easy to test, in SSMS. First, create a table in your database:
CREATE TABLE [dbo].[Test1](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Value] [varchar](500) NULL,
) ON [PRIMARY]
Then in 2 different query windows run the following query:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
begin tran
insert into test1 values (1)
select * from test1
Note: the query is not rolled back or committed.
If you run the queries without the 1st line then the 1st query will run and wait then the 2nd will be sequential. If you run with transaction isolation level set to Read Uncommitted then the 2nd query will finish. You can commit or rollback the 1st query and the results of the 2nd will be unaffected.
use WITH (NOLOCK) with select query. it's give output evenif that table or row is in updation. it's give output last committed data.
for ex.
select * form tablename with(nolock)
下一篇: SQL Server插入锁定与并发事务