SQL Server Performance

How to lock a specific row in a table?

Discussion in 'General Developer Questions' started by pcsql, May 13, 2004.

  1. pcsql New Member

    I want to insert a record into a permanent table but I don't want anyone (any other session) to have access to that record until I allow it. I cannot use transaction for this case. Will sp_getapplock work? If yes, how to code this?


    Thank you,

    Peter
  2. derrickleggett New Member

    Will the lock need to last longer than the procedure that creates it? How long are we talking about hiding this record?

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  3. pcsql New Member

    Hi derrickleggett,

    The logic is something like this:

    Insert a record into a permanent table
    Lock the new record so only this session can see it.

    Start a loop

    begin transaction
    .
    .
    .
    committ transaction


    end the loop

    Unlock the new record

    All the codings in bold are passed to SQL Server through ODBC. The loop can be seconds or hours depends on # of rows which will be inserted into another permanent table inside the loop.


  4. derrickleggett New Member

    Can you make this into a stored procedure? You could then wrap it in a transaction and it would effectively do what you are saying. I have some import logic that does this. There's a wrapper transaction for the entire process and a transaction around the insert inside of the wrapper transaction. You commit the transaction that inserts the record so the rest of your process can see it. When you are finished with the import, the entire process is commited which makes it available to the world.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  5. pcsql New Member

    Hi derrickleggett,

    This is exactly what I have currently but it is getting locking problem:

    begin transaction
    Insert a record into a permanent table

    Start a loop
    .
    .
    .
    end the loop

    commit transaction

    Since the transaction will not be committed until the entire loop is processed and the loop contains update statements (each update one row of a table), many tables will be locked with different lock type and mode since many rows will be updated. So, in order to get around this locking issue, I decide to change the process to:

    Insert a record into a permanent table
    Lock the new record so only this session can see it.


    Start a loop

    begin transaction
    .
    .
    .
    committ transaction


    end the loop

    Unlock the new record








  6. bambola New Member

    you could add a column to indicate if row can be read or not
    Otherwise, is it possible to pass all the info needed for the loop into a main sproc? In this case you only need to parse it insid the sproc, and probably insert it all in one shot.
  7. pcsql New Member

    bambola,

    Adding a column is easy but changing all the places which will query the table is a tremendous job.

    The begin transaction coding is not in SQL Server but in an application.
  8. gaurav_bindlish New Member

    Read the row to be locked using UPDLOCK statement and then carry on processing on other table....

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  9. Raulie New Member


    It is alway a good idea to let SQL Server dynamically handle locking. But if you need to use Rowlevel locking you use the WITH (Rowlock, HOLDLOCK) query hint. First of all how many records is this operaiton handling? Second what is your current Transaction Isolation Scheme? Third what is happening with this loop your talking about, are you using cursors of anykind?
  10. Raulie New Member

    I'd have to see the complete transaction to give you any meaningful advise.
  11. derrickleggett New Member

    You do need to post some code to help us out here. It sounds like you need to put the entire transaction in a procedure at the SQL Server level though. I wouldn't want to be doing the lock/transaction management you're doing only at the application level.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  12. Raulie New Member

    Very true, like Derrick suggested I would move my transactional code into an SP, I come from the school of letting the server handle everything.
  13. bambola New Member

    Joining Derrick and Lazy_DBA (great name btw). I think you should try moving the transaction into the sp if possible.
    Carefull with HOLDLOCK. It is like setting transaction isolation level to serializable, and you said you already experience lock problems.
  14. Raulie New Member

    Bambola is correct SERIALIZABLE is the highest of the bunch. If you aren't sure I would recommend you do not use any locking hints as they could crop up other unforseen problems, however locking hints could help when administered correctly in certain situations.
  15. pcsql New Member

    I'm using the default isolation level (Read Committed). I'm confused about the suggestion of putting the transaction into a stored procedure. I don't understand why moving the coding to stored procedure will help in this case. I have included both the original coding and its problem and the new coding and its problem in this post so you can see them easier.

    Original Coding
    Causes locking problem in table1, table2, table3 since the loop is about few thousands time and may take over an hour to run. Each table contains few millions rows.

    begin transaction
    Insert a record into table4

    Start a loop

    Update table1
    Update table2
    Update table3

    end the loop

    commit transaction

    New Coding
    Won't cause locking problem anymore but the new record in table4 can be accessed by other users since it is committed. I'm trying to figure how to block other users from accessing that new record before the loop is completed.

    Insert a record into table4

    Start a loop

    begin transaction

    Update table1
    Update table2
    Update table3

    committ transaction

    end the loop

  16. Chappy New Member

    Going back to the idea of adding a column, you could perhaps minimise code changes by creating a view which filters out 'hidden' rows. Then just change the source table in all your stored procs to use the view instead<br /><br />Alternatively, rename the table and create the view with the same name as the original table, but Id avoid this route if possible because it can be confusing<br /><br />Id say it will be difficult to achieve what you want just by playing with locking hints, especially without degrading concurrency (1 hour is a long time to be blocked <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  17. bambola New Member

    I don't think that changing isolation level or lock type will help you solve your problem. I think you would try and get rid of the loop from the application.
    You said there are a few thousands of rows you need to insert each time. From where do you get the data? If it is not on
    your SQL Server, can you upload it there?
    In this case you would try the following

    import your data into a table0. Add an identity column to this table.

    set x and y

    set transaction isolation level read committed (never hurts)

    start loop
    begin transaction

    insert records into table4 from table0 where table0.id between x and y

    if @@rowcount = 0 exit loop

    update table1 join table0 where ... and table0.id between x and y
    update table2 join table0 where ... and table0.id between x and y
    update table3 join table0 where ... and table0.id between x and y

    increment x and y

    commit transaction
    end loop

    This will keep transactions short, users would not be able to access rows that are not committed and if you index tables well, you should not experience lock problems.




  18. pcsql New Member

    bambola,

    Only 1 row need to be inserted into table4 for the entire process.

    The loop is actually a cursor with few thousands rows. Update for table1, table2, table3 only affect 1 row.


    Original Coding
    begin transaction
    insert a row into table4

    while
    begin
    update table1
    update table2
    update table3
    fetch next
    end

    commit transaction

    New Coding

    insert a row into table4

    while
    begin
    begin transaction
    update table1
    update table2
    update table3
    commit transaction
    fetch next
    end


  19. bambola New Member

    Any chance we can see some code? There might be a way to avoid that cursor...
  20. Raulie New Member

    PCSQL, we are just taking shots in the dark here, like mentioned before if it's not too much to ask we need to see what exactly is happening in the code otherwise we are just going in circles. Im sure we can come up with a solution for you.
  21. pcsql New Member

    Hi bambola, Lazy_DBA, and others who have replied to this thread,

    1. Thank you for all your suggestions.
    2. I understand that without actually looking at the codings, it will be very hard to come up with a good solution.

    The codings inside the loop are quite long and involves multiple stored procedures and firing of triggers in different tables.

    I have no doubt that those codings can be tuned. However, in order to tune that part of codings is quite time-consuming since some of the stored procedures and triggers are sharable codings for other functions.

    The new coding that I try to implement is just a quick way to resolve the urgent issue (the lockings). By using the new coding, the time is reduced to couple of minutes instead of over an hour. Of course, this leave me with the potential issue of someone may access the new record in table4 before the loop finishes.
    And that's why I want to figure how to hide that record from others during that couple of minutes.

    Peter
  22. Raulie New Member

    Well if you incorporate the INSERT INTO table4 as part of the transasction you say you get locking problems. Incorporating the INSERT INTO Table4 as part of the transaction will disallow users from accessing the row since it needs to be commited first preventing a Dirty Read. Well here is another shot in the dark...


    begin transaction
    Insert a record into table4

    Start a loop
    ----In this loop if you are doing other Selects from these tables you can use query hint (READPAST)
    ----which will skip past rows level locks again just a shot in the dark.
    Update table1
    Update table2
    Update table3

    end the loop

    commit transaction


    READPAST from BOL
    Skip locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.
  23. Chappy New Member

    No response to the view suggestion? Youre looking for a quick solution.. serious concurrency issues are rarely resolved quickly in my experience
  24. Raulie New Member

    Yep, Im done. One last side note like I mentioned previously Locking Hints can seriously affect performance by bringing up worser problems, but they can also relieve contention in certain circumstances. Keep us posted on your progress.
  25. pcsql New Member

    Hi Lazy_DBA,

    Thank you for trying so much. Unfortunately, the lockings are blocking others processes not the one inside the loop. I have reviewed your posts in this thread and you mention using WITH(ROWLOCK, HOLDLOCK). If I do this:

    insert a row into table4

    while
    begin
    begin transaction
    select * from table4 WITH(ROWLOCK, HOLDLOCK) ---- locking only the new record
    update table1
    update table2
    update table3
    update table4 ****** Can this be done since the record is locked?
    commit transaction
    fetch next
    end

    If I can still update the new record of table4 inside the loop, then it is a workable solution.



    Hi Chappy,

    Sorry about not responding to yours view suggestion. Unfortunately, changing other stored procedures and other codings not related to the process in question is not a workable solution to me. Time and resources are limited.


    Peter
  26. bambola New Member

    and what if another process will place a lock after inserting to table4 and before entering the transaction?
  27. pcsql New Member

    Hi bambola,

    Your concern is definitely valid and that's why I want the coding to be:

    Insert a record into a permanent table
    Lock the new record so only this session can see it.

    Start a loop

    begin transaction
    .
    .
    .
    committ transaction

    end the loop

    Unlock the new record

    Unfortunately, there seems to be no way to just lock the record and then release the lock at will. It seems to me that all the locking hint will either last for the lifetime of a statement or the lifetime of a transaction.


    Peter

Share This Page