Re: [PHP-DB] Lock Record on Postgresql
Norma, Generally speaking issuing locks on records across the internet is not a good idea. Too many things can happen that could result in the record never being unlocked. Recognize the HTTP is stateless, that's one of its limitations, and work around that. Regards - Miles At 11:30 AM 8/13/2004, Norma Ramirez wrote: I need to lock a record in a postgresql table, how can I send the lock query in a script and after in other script send the unlock instruction? Is this possible? Thanks Norma R -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Lock Record on Postgresql
On Fri, 2004-08-13 at 07:30, Norma Ramirez wrote: I need to lock a record in a postgresql table, how can I send the lock query in a script and after in other script send the unlock instruction? Is this possible? Thanks Norma R What kind of queries are you running. Often times I find people misusing LOCKS. -Robby -- /*** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | [EMAIL PROTECTED] * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting Development / signature.asc Description: This is a digitally signed message part
Re: [PHP-DB] Lock Record on Postgresql
Thanks for answer Miles, I'm aware of what you wrote but have a little hope to find some way, and Robby what I'm trying to do is avoid the user for update or delete a record that other user has been already selected, so I cant tell something like: This record is locked by another user, try later, currently I'm doing this by code but like to do by Postgres directly. Thanks in advance. - Original Message - From: Robby Russell [EMAIL PROTECTED] To: Norma Ramirez [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, August 13, 2004 10:07 AM Subject: Re: [PHP-DB] Lock Record on Postgresql -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Lock Record on Postgresql
Norma Ramirez [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Thanks for answer Miles, I'm aware of what you wrote but have a little hope to find some way, and Robby what I'm trying to do is avoid the user for update or delete a record that other user has been already selected, so I cant tell something like: This record is locked by another user, try later, currently I'm doing this by code but like to do by Postgres directly. Thanks in advance. Hi Norma, you can achieve this in a much more comfortable and elegant way: Create an integer column named oca (stands for Optimistic Concurrency Control Attribute). When you load the data to show them in the editing form store the value in a hidden field or in the session (if you are using sessions). Then when you update the data use the following statement: UPDATE table SET column = '$value' ..., oca = oca + 1 WHERE user_id = $user_id AND oca = $value_from_hidden_field After performing the query check the affected rows. If there is one affected row the update was succesful. If there are no affected rows it means that someone else updated this row in the meantime (thereby incrementing oca) and you can show a message like: Since the start of your editing process someone else updated this record set. Please cancel and edit the record again. This way you will never lock out a record set forever like Miles wrote and the user will at least be able to open the record set and see all data - even if sometimes (should be very seldom) he has to cancel and start again. I hope you get my point - it's a bit difficult to explain. Best regards, Torsten Roehr -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Lock Record on Postgresql
Torsten, Elegant! Miles At 02:03 PM 8/13/2004, Torsten Roehr wrote: Norma Ramirez [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Thanks for answer Miles, I'm aware of what you wrote but have a little hope to find some way, and Robby what I'm trying to do is avoid the user for update or delete a record that other user has been already selected, so I cant tell something like: This record is locked by another user, try later, currently I'm doing this by code but like to do by Postgres directly. Thanks in advance. Hi Norma, you can achieve this in a much more comfortable and elegant way: Create an integer column named oca (stands for Optimistic Concurrency Control Attribute). When you load the data to show them in the editing form store the value in a hidden field or in the session (if you are using sessions). Then when you update the data use the following statement: UPDATE table SET column = '$value' ..., oca = oca + 1 WHERE user_id = $user_id AND oca = $value_from_hidden_field After performing the query check the affected rows. If there is one affected row the update was succesful. If there are no affected rows it means that someone else updated this row in the meantime (thereby incrementing oca) and you can show a message like: Since the start of your editing process someone else updated this record set. Please cancel and edit the record again. This way you will never lock out a record set forever like Miles wrote and the user will at least be able to open the record set and see all data - even if sometimes (should be very seldom) he has to cancel and start again. I hope you get my point - it's a bit difficult to explain. Best regards, Torsten Roehr -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Lock Record on Postgresql
Torsten: Thank's a lot, I think I get it! Hi Norma, you can achieve this in a much more comfortable and elegant way: Create an integer column named oca (stands for Optimistic Concurrency Control Attribute). When you load the data to show them in the editing form store the value in a hidden field or in the session (if you are using sessions). Then when you update the data use the following statement: UPDATE table SET column = '$value' ..., oca = oca + 1 WHERE user_id = $user_id AND oca = $value_from_hidden_field After performing the query check the affected rows. If there is one affected row the update was succesful. If there are no affected rows it means that someone else updated this row in the meantime (thereby incrementing oca) and you can show a message like: Since the start of your editing process someone else updated this record set. Please cancel and edit the record again. This way you will never lock out a record set forever like Miles wrote and the user will at least be able to open the record set and see all data - even if sometimes (should be very seldom) he has to cancel and start again. I hope you get my point - it's a bit difficult to explain. Best regards, Torsten Roehr -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
