Want to keep Database connection open to lock record

General discussion on PHP
Post Reply
suma2283
Posts: 5
Joined: Tue Jun 24, 2014 9:19 am

Want to keep Database connection open to lock record

Post by suma2283 » Thu Jun 26, 2014 12:00 pm

Need some help in this. I am using Zend server 6.1 and PHP 5.3. I need to keep a record lock when I am fetching data from database and send clients to view or update, without using any java pluggins. I need to lock till user navigate to next record or previous record. What I found that record is remain locked only during execution of PHP script. As soon script execution completed lock got released. I am using 0ci8 as I am using oracle in backend. My DB is in dedicated server mode. I set oci8.max_persistent to -1 and oci8.persistent_timeout to -1 but still that connection not holding lock state. As soon as script execution ends I found record lock released.

Am I missing something or is it not possible to keep a record lock? My requirement is to keep a record lock when one user is viewing that record and others will get be able view only that record. This has to be done in DB level, from application level I can do this but client wants to keep that record lock even in database level so that no one can make any changes even from back end.

Any help much appreciated. I can't use any Java plugins.

Regards and thanks

Suman

User avatar
zvika
Zend Global Support
Posts: 997
Joined: Sun Dec 14, 2008 9:48 am
Contact:

Re: Want to keep Database connection open to lock record

Post by zvika » Mon Jun 30, 2014 8:12 am

Hello Suman

I'm not sure how you wish to enforce exclusive lock for single client and release it on DB level.
There are OCI pin/unpin which might help in such scenario, but I don't see those implemented in PHP-OCI.

It might be better practice to implement on PHP level and / or use extra locking column for queries, with some auto-unlock mechanism in case the locking user is "lost".

Hope this helps.
I would suggest to further research at Oracle community resources and Stack Overflow, if the answer is not already there.
Zvika Dror
Zend Support Team

suma2283
Posts: 5
Joined: Tue Jun 24, 2014 9:19 am

Re: Want to keep Database connection open to lock record

Post by suma2283 » Mon Jun 30, 2014 12:28 pm

Hi Zvika Dror,
Thanks for your reply.
Actually my requirement is to lock a table record when any user is view that record, say if one user is viewing one PO by no say 26062014/1002, then any other user when they will view it they will view it in view only mode, none except the first one can update that record. I am able to do this programmatically by marking which user is accessing this one, but my client want it to be locked even in back end, so that if anyone tries to update that record from backend they should be blocked. This is where I tried to lock record from PHP using PCONNECT and running my oracle database in DEDICATED server mode. But even on this what I found after executing PHP script where I am locking that row of the table is get released. I need this to continue. As I found on using PCONNECT I am always allocated same oracle session,, hence releasing that lock will not be an issue, but that lock doesn't persists after execution. I will try to look into pin and unpin option as you mentioned, but what this pin / unpin option is?
It will be great if you can just highlight it little bit.

With regards and thanks,

Suman

User avatar
zvika
Zend Global Support
Posts: 997
Joined: Sun Dec 14, 2008 9:48 am
Contact:

Re: Want to keep Database connection open to lock record

Post by zvika » Mon Jun 30, 2014 2:52 pm

Hi

I think this calls for client side locking, you lock tables only within transactional activity and on maintenance (same, but initiated by DBA and not application code).
A locking table which can hold the record ID, User ID and timestamp can help, with override of course (using the timestamp and user ID "alive" to determine if the record can be free to all).

Hope this helps
Zvika Dror
Zend Support Team

suma2283
Posts: 5
Joined: Tue Jun 24, 2014 9:19 am

Re: Want to keep Database connection open to lock record

Post by suma2283 » Mon Jun 30, 2014 3:35 pm

My intention was to lock that record. When user will view one record I will just fire an update without commit which should lock the row not the table, this lock will be done by oracle Session ID allocated to the specified request send from application server. For this I was using PCONNECT so that every tie I use same login credential I will get same SID. On moving to other record without making any changes it will fire rollback from front end which will release record lock for that record and move on.

This is what I intend to do.

Thanks

Suman

User avatar
zvika
Zend Global Support
Posts: 997
Joined: Sun Dec 14, 2008 9:48 am
Contact:

Re: Want to keep Database connection open to lock record

Post by zvika » Mon Jun 30, 2014 3:56 pm

Sounds reasonable,
I would still take this to Oracle community or Stackoverflow, since it is a proprietary driver question.

Sorry I cannot help you further. Good luck and hope someone will get here with the right Oracle - PHP experience.
Zvika Dror
Zend Support Team

viki4520
Posts: 1
Joined: Sun Jul 06, 2014 2:18 pm
Contact:

Re: Want to keep Database connection open to lock record

Post by viki4520 » Sun Jul 06, 2014 2:22 pm

you simply used the orical data base and more information go to w3 schools web site.

swat9909
Posts: 1
Joined: Mon Oct 26, 2015 12:57 pm

Re: Want to keep Database connection open to lock record

Post by swat9909 » Mon Oct 26, 2015 12:58 pm

Row-level locks provide a major purpose to check multiple communications from transforming the similar row. Whenever a business requires transforming a row, a row lock is obtained by Oracle. There is no firm boundary on the precise numeral of row locks detained by a declaration or contract.

Post Reply