locked
TRANSACTION ISOLATION LEVEL SNAPSHOT RRS feed

All replies

  • Hi,

    Yes, you need clustered index on that table.

    Tuesday, June 23, 2015 2:32 PM
  • Hi, is that possible to update concurrently two different records  in same table same time.(with row lock)? I'm not mistaken every update SQL server set table lock(IX) and Page lock(X)

    Thanks & regards,
    Tharindu Dhaneenja (www.databaseusergroup.com)

    Hi,

    When you just update a single row only that row will be locked and not the page and yes table would have IX lock (the intent exclusive). Why would you use rowlock you can do without it.

    You must read Lock Granularity and Hierarchy


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Tuesday, June 23, 2015 2:40 PM
  • Hi All, yes i have "clustered index"... but i have noticed table lock and page lock while update.   

    (Ex: CREATE TABLE [dbo].[tblChild]([id] [int] IDENTITY(1,1) NOT NULL,[Parentid] [int] NULL,[Childname] [varchar](100) NULL);" "Parentid" is clustered index and update using where id)

    Any idea of above method.

    Thanks


    Thanks & regards,
    Tharindu Dhaneenja (www.databaseusergroup.com)

    Wednesday, June 24, 2015 6:00 AM
  • Hi,

    Why CLI on ParentID ? Create NCI on Parent if needed and CLI on ID.

    Wednesday, June 24, 2015 6:07 AM
  • Hello - Indeed, that is what is expected from a RDBMS, you just need to ensure that your table has Clustered Index in it. Without clustered index, the update statement may cause entire table to be locked (Due to Full Scan) 

    Hope this helps !


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Wednesday, June 24, 2015 6:12 AM
  • Hi, but my most of search sql's using with "ParentID" if we have CIX then much more faster than ID. the issue is unexpected locking.

    Thanks & regards,
    Tharindu Dhaneenja (www.databaseusergroup.com)

    Wednesday, June 24, 2015 6:20 AM
  • Hi All, yes i have "clustered index"... but i have noticed table lock and page lock while update.   

    (Ex: CREATE TABLE [dbo].[tblChild]([id] [int] IDENTITY(1,1) NOT NULL,[Parentid] [int] NULL,[Childname] [varchar](100) NULL);" "Parentid" is clustered index and update using where id)

    Any idea of above method.

    Thanks


    Thanks & regards,
    Tharindu Dhaneenja (www.databaseusergroup.com)

    Please can you show me the locks , I guess its IX (intent exclusive) lock on table which is actually not a lock but indicator.

    How many rows are beig updated ? if number of rows being updated is large sql server can escalate lock to table level unless you show us locks it would be difficult to predict


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Wednesday, June 24, 2015 6:42 AM
  • If you build non cluster index on ParentID instead of Clustered, your sql's will use it and performance will be the same. Then create Clustered on ID and you solve update blocking issue.
    Wednesday, June 24, 2015 6:43 AM
  • I ran your scripts, but it didn't repro.

    First of all, there are two problems in the script. It doesn't create the database and it doesn't set the database option to allow snapshot isolation. A repro should ideally be 100%, just to make sure we don't do anything that you do.

    Second. When I adjusted for the two things above, the second query was not blocked.

    Since I don't want to guess what to expect etc, I decided to stop here. I don't want to guess what parts to re-execute, what to uncomment, etc I decided to ask for an update on your repro, so it is cleaner and more predictable...


    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, June 24, 2015 7:22 AM
  • I test it, no blocks.

    Does you table allow rowlock and page lock ?

    Wednesday, June 24, 2015 7:25 AM
  • I forget to mention that it's already done,

    my DB = IS Read Committed Snapshot ON = true,

    ALTER DATABASE [DATABASEUSERGROUP]
    SET READ_COMMITTED_SNAPSHOT ON;
     
    ALTER DATABASE [DATABASEUSERGROUP]
    SET ALLOW_SNAPSHOT_ISOLATION ON;
     

    above commands excited after DB is created.

    Note default fillfactor is 100%


    Thanks & regards,
    Tharindu Dhaneenja (www.databaseusergroup.com)

    Wednesday, June 24, 2015 7:37 AM
  • You retrofit commands into a script, which doesn't repro what you want to show in the first place. Since we cannot reproduce your behaviour with the script you posted, I doubt that you will get anything that will lead you forward. 

    I suggest you re-do that blog post and post code that we can execute, *as is*, without modifications that includes *everyting* in order to show what you are seeing. This will include creation of database, database options etc. And, of you want to show with some different indexes, post separate scripts for that, not comments in the first script.

    Remember, if we cannot reproduce what you are seeing, it is very difficult for us to commend. Again, the script you posted did *not* block for me.


    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, June 24, 2015 1:46 PM