Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unanswered: Inserts resulting in Exclusive Key Lock

    I'm relatively new to SQL and I've come across something that doesn't seem quite right. When an insert becomes part of an transaction I notice an exclusive KEY lock in Enterprise Manager. The table in question was using a Clustered index but I changed that, dropped the table and brought it back in but I still get the lock which keeps all others out of the table. Is this the expected behavior or is there something I am missing? Could the size of the tabe affect things? This is a very small table currently. I'm using MSSQL 7 sp3.

    Thanks,
    John

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well put the clustered index back...

    It's locking the row and/or page..

    Did 7 have row? And was it a 2k or 8k page?

    I can't remeber any more,

    BUT, you need to keep your transactions brief....like lightning breif....

    What's the code do?

    Is this a sproc?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2004
    Posts
    3
    Yes, 7 has row locking, not sure about the page size. I am converting and old Btrieve app and trying to keep modifications to the code to a minimum. This is acutally happening with only one user in. Within a tranasction its reading a table, inserting a row, then cycyling again - multiple times. After the first insert its not able to read the table again. Before I went in and checked if the code can be changed I wanted to make sure there isn't something else I could do - aside from changing the isolation level.

    Thanks

    Originally posted by Brett Kaiser
    Well put the clustered index back...

    It's locking the row and/or page..

    Did 7 have row? And was it a 2k or 8k page?

    I can't remeber any more,

    BUT, you need to keep your transactions brief....like lightning breif....

    What's the code do?

    Is this a sproc?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You mean looping?

    You're probably blocking yourself...

    Are you doing INSERT INTO the table you're reading from?

    Why not write to a temp table, then if all the executions are fine, insert the temp to the final destination table...

    can you post the code?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2004
    Posts
    3
    Yes, I am blocking myself...

    Would that be a user temp table? Will give that a try. Can't really post the code as its being generated from an application called Magic.

    Even with the temp, when inserting the temp to the final dest, won't that still result in excl key locks? - although in another transaction.

    Is this just the way insert locks work - has behavior changed in 2000?

    Originally posted by Brett Kaiser
    You mean looping?

    You're probably blocking yourself...

    Are you doing INSERT INTO the table you're reading from?

    Why not write to a temp table, then if all the executions are fine, insert the temp to the final destination table...

    can you post the code?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Magic...that's funny...

    Let's make some Magic

    Code:
    CREATE TABLE #myTable99(Col list)
    
    DECLARE @x int
    
    SELECT @x = 0
    
    WHILE @x < 10
      BEGIN
        SELECT @loclavariable1 = Col1, ect FROM TABLE WHERE Predicate = guarentee 1 row
        INSERT INTO #myTable99(Col1, ect) SELECT @loclavariable1, ect
        SELECT @x = @x + 1
    INSERT INTO FinalDetaTable (Collist)
    SELECT Col list FROM #myTable99
    Like that...or are you using a cursor?

    If it is a cursor, you can probably do without it...


    Hey, I know, I'll create an app and call it Miracle

    Good Luck
    Last edited by Brett Kaiser; 04-01-04 at 17:45.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Oh for Pete's sake!...

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's wrong, my advice or the miracle thing?

    Some say it would be a miracle if I ever get this third party extract done...

    They keep chenging their mind...

    I'm on revision 12 now....purely painful...for a simple (ok, well not so simple) extract....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey Brett, you rock! I just don't understand why people are trying to write a script to send a rocket to Mars while doing an insert?!

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sounds like a 4GL that generates the code...

    Ever see SQ-R

    Painful...

    I guess you can you it and write good code, but it leads down the cursor path..

    I once saw a "program" that ran 7 hours..

    8 levels of nested curors...

    Wrote in pure sql....

    5 minutes....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    LOL

    So in actuallity, it really was my advice

    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •