How do locks work in a insert...select statement?

Two transactions in two sessions are operating on same item as:

In session 1:

begin tran T1

   insert into Invoice with (item,OrderNumber) 

    select 'ItemA', max(OrderNumber)+1 

    from Orders

    where item='ItemA'

    waitfor delay '00:00:05'

commit T1

In session 2:

begin tran T2

   insert into Invoice with (item,OrderNumber) 

    select 'ItemA', max(OrderNumber)+1 

    from Orders

    where item='ItemA'

commit T2

If just like this, two identical rows will be insert into the table Orders. But I want to transaction in either session is done first and then another transaction can read new max(OrderNumber) and then insert next value. I add holdlock to T1 as:

begin tran T1

   insert into Invoice with (item,OrderNumber) 

    select 'ItemA', max(OrderNumber)+1 

    from Orders with (holdlock)

    where item='ItemA'

    waitfor delay '00:00:05'

commit T1

Does SQl SERVER assign shared lock to select first since it parse select statement first then assign exclusive lock to insert statement? How does exactly locks works to each other in two session? Thanks for any hints

---

You can use serializable isolation level for your transaction.

Ex:

 set transaction isolation level serializable
 begin tran
    insert into Invoice with (item,OrderNumber) 
    select 'ItemA', max(OrderNumber)+1 
    from Orders
    where item='ItemA'

    waitfor delay '00:00:05'

 commit tran 

Serializable option will provide following transaction features:

Statements cannot read data that has been modified but not yet committed by other transactions

No other transactions can modify data that has been read by the current transaction until the current transaction completes

Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Above will work for your problem but I would suggest using an identity column instead of max ordernumber + 1 logic. So change the OrderNumber to be identity in your table and when you read data use row_number number to compute order number by Item in runtime, here is an example query:

  select Item, Row_Number() over(partition by Item order by OrderNumber) as OrderNumber
  from Invoice

So the above query will give the result you need.

---

What is your ultimate goal. I don't think you cannot stop an insert with a lock on select, it will only lock the selected rows from any update.

---

Database locking schemes are integral part of any database management application. For integrity of data stored in databases there are different locking schemes provided by different database vendors. You should check the following links First Link

Second Link. If these doesn't help then please let me know so that I can help you further.

链接地址: http://www.djcxy.com/p/5616.html

上一篇: 使用（updlock，rowlock）对SQL Server中的查询了解效果

下一篇: 锁如何在insert ... select语句中工作？