Thanks for all the replies. I see that I was clearly to vague in my original post, so I shall try to give some more details.

This is a web-application and I have not found any reliable way to unlock rows when a users leaves a page, closes the web-browser etc. So to prevent a user from holding a lock that is no longer used, I want the following behaviour: A lock is only valid for a short time (for instance 5 minutes). After that the lock is not counted as a lock anymore; it is invalid. If a user wants to keep the lock longer, she must ask for it explictly, in which case the lock is held for another 5 minutes (or some other time).

Regarding what herveus said:
I agree that there generally is no reason to reinvent the wheel, but initially I could not find anyway to get SQL Server to lock rows for me explicitly (changing the database is not an option at this time). All I found where descriptions of how SQL Server locked rows when performing INSERTS/UPDATES etc. and how to optimize SQL Servers behaviour. I have now found a way for SQL Server to lock rows, but I don't know if this way is database independent. Database independece is not critical, but desired. Do anyone know if it is possible to lock rows in a database independent way?

Regarding what davidrw said:
This is what I have implemented already. Each row has a version number and if the version number is lower than what is stored in the database when the user tries to update a row, the update is rejected and a error message is issued. This is not acceptable to my users since they can use a long time filling out a form before submitting. If they lose the data they used a long time entering, they will not be happy users.

Regarding what SheridanCat said:
Letting the users fix any conflicts can be a good solution, but I am uncertain if it is the right solution here. Conflicts are mostly likely to happen at days when my users have a lot to do, and letting them fix conflicts at this time might be frustrating for them. I should clearly have checked this more thoroughly though.

BTW I think I have a race condition free implementation in Perl now thanks to one of the people that helped my at the chatterbox. The person suggested letting the column that identifies a locked row have a UNIQUE constraint. When performing and insert the RDBMS will reject an insert that tries to lock a row (by inserting into the ROWLOCK table) that is already locked and DBI will cast an exception. By placing the DBI execute call in an eval{} it is easy to see if the user acquired the lock or not, and two users can not have the lock at the same time.

I will now look more close at your suggestion and see if I should do something differently. Thanks again for all replies.

In reply to Re: Implementing rowlocking by oyse
in thread Implementing rowlocking by oyse

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.