locked
self locked by one end-user operation RRS feed

  • Question

  • Hi

    I'm using SQL SERVER 2008 along with EJB.

    2 transactions created by EJB container while end user start one operation on the browser, 

    The sequence is like follwoing:

    -------------------------------------------------

        1st EJB  Transaction starts       

         .....

        insert into tablex(id, id2, comment) values(1, 1, 'xxxx');

         .....

       2nd EJB Transaction starts  

       update tablex set comment='yyyy' where id2 = 1;   ----stop executing

       2nd EJB Transaction ends

        ......

       1st EJB Transaction ends

    -------------------------------------------------

    1)tablx have about 800 rows, and 2 indexes, one is pk for column 'id', one is unique for column 'id2'

    2)by check the management studio, 2nd transaction is wait for the 'key' type index resource(the unique index for column 'id2') which is held by 1st transaction

    3)2 transactions are created in 2 sessions, and isolation level set to 'read-committed'

    my question is:

    why 1st transaction lock the index, and 2nd transaction not isolated, in Oracle, the 2nd transaction would execute successfully with no rows affected

    Thursday, October 17, 2013 9:59 AM

Answers

All replies

  •  Is this the correct behavior for SQLServer? if so, what's the different between the transaction in SQLserver and the transaction in Oracle?
    Friday, October 18, 2013 3:39 AM
  • Hello Bond, Please refer the below article for the difference between ORacle and Microsoft SQL Server.

    Please look at Table 2-21 to 24 Locking in Oracle and Microsoft SQL Server for your specific question.

    http://docs.oracle.com/cd/E10405_01/doc/appdev.120/e10379/ss_oracle_compared.htm


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Elvis Long Thursday, October 24, 2013 11:50 AM
    • Marked as answer by Elvis Long Monday, October 28, 2013 1:33 PM
    Friday, October 18, 2013 4:35 AM