Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Re: Implementing rowlocking

by oyse (Monk)
on Jan 09, 2007 at 15:05 UTC ( [id://593737]=note: print w/replies, xml ) Need Help??


in reply to Implementing rowlocking

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.

Replies are listed 'Best First'.
Re^2: Implementing rowlocking
by SheridanCat (Pilgrim) on Jan 09, 2007 at 15:41 UTC
    The unique constraint idea is clever. But using side effects like the constraint throwing an error to do significant stuff makes me uncomfortable. Document it well so the maintenance programmer who looks at the app in four years will understand how you're using that.
Re^2: Implementing rowlocking
by pajout (Curate) on Jan 09, 2007 at 15:43 UTC
    Just 2 notes:

    1. I think that rowlocking (whatever it means) is not (probably) necessary during whole user session in described situation. I don't know real requirements, but consider the scenario when user sends both old (which he has received) and new values (which he has probably changed). The application logic can decide, if it is necessary to lock some rows for a while, check if the data in rows are equal to old values and change it into new values. And unlock, of course. (I suppose transaction isolation level = read commited and the transaction is started on the begin of every user request and ended on the every end of user request)

    2. If you really don't have a mechanism for locking rows, you can mimize it using special column in the locked table, which is for pid of locking process. Consequently, you should have "WHERE pid = $$" in all updating/deleting commands. Just idea, probably leading to messy code, better way is to have PostgreSQL :>)

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://593737]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others studying the Monastery: (5)
As of 2024-03-29 07:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found