Results 1 to 8 of 8

Thread: record lock

  1. #1
    Join Date
    Sep 2003
    Posts
    29

    Unanswered: record lock

    i have an app that insert multiple rows into a table in sql using insert statments. i use a begin trans and commit trans.

    here is the setup i have a table that contains 100 rows. i insert another 25 rows. in another app i try to edit a record in that table (a record from the first block of 100) i can't edit the record because my first app has not done its commit trans yet. i don't understand why i can't edit a record that was already in the table. i'm not trying to edit one of my newly inserted records.

    please advise,
    Thanks
    Thomas

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    SQL Server has to allow for the possibility that your open transaction might ROLLBACK, making the rows it inserted vanish. To do this, everything you do within a transaction is done in a "sandbox" that other users can't see until you commit it.

    -PatP

  3. #3
    Join Date
    Sep 2003
    Posts
    29
    i understand that point when dealing with the newly inserted records. but i'm trying to edit a record that was inserted into the table days ago. why are these old records locked.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I must have misunderstood you, I thought you meant that the app that inserted the rows was still running and had the transaction open (uncommitted).

    Without seeing your DDL and your code I'm having to guess about a lot of things. My guess is that you have an open read lock somewhere (possibly due to a cursor) that is preventing you from getting an exclusive lock needed to do the update. If that is the case, I'd suggest that you switch to optimistic locking to drastically reduce your general concurrancy problems.

    You don't say what language your apps are written in, or what access method(s) they use, but I'm pretty comfortable that optimistic locking has to be in there somewhere!

    -PatP

  5. #5
    Join Date
    Sep 2003
    Posts
    29
    i'm using vb6 and ado as my connection.

    the app that is inserting the new rows is still running (it has not run the cnn.commit at this point).

    in another app i try updating a record in the same table but the record is not part of the new transactions. the record i whish to update is over a week old.

    why would my inserts lock the whole table. shouldn't they only lock the newly created rows. allowing other apps to view and edit old data in the same table.

    Thomas

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I vote for a cursor that was opened, but never closed or deallocated...

    Is there a system table that tracks the cursors?

    must be...
    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
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Found this in BOL...blew my mind...

    Code:
    USE Northwind
    GO
    -- Declare and open a keyset-driven cursor.
    DECLARE abc CURSOR KEYSET FOR
    SELECT LastName
    FROM Employees
    WHERE LastName LIKE 'S%'
    OPEN abc
    
    -- Declare a cursor variable to hold the cursor output variable
    -- from sp_cursor_list.
    DECLARE @Report CURSOR
    
    -- Execute sp_cursor_list into the cursor variable.
    EXEC master.dbo.sp_cursor_list @cursor_return = @Report OUTPUT,
          @cursor_scope = 3
    -- Fetch all the rows from the sp_cursor_list output cursor.
    FETCH NEXT from @Report
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
       FETCH NEXT from @Report
    END
    
    -- Close and deallocate the cursor from sp_cursor_list.
    CLOSE @Report
    DEALLOCATE @Report
    GO
    
    -- Close and deallocate the original cursor.
    CLOSE abc
    DEALLOCATE abc
    GO
    You can declare a cursor as an output variable?

    What the hell is that about...still trying to wrap my head around that...
    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.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by Brett Kaiser
    You can declare a cursor as an output variable?

    What the hell is that about...still trying to wrap my head around that...
    Yes, some of the digi-vermin left over from Sybase, trying to do things "the Oracle way". Don't think about it, it will only make your head hurt for no good reason!

    -PatP

Posting Permissions

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