Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

Pessimistic locking and a web interface

Hi,

I'm developing a PHP/MySQL Intranet application. Since it is very data-entry oriented, I'm wondering if it were possible to implement pessimistic locking in such a scenario, where a record is locked with a "select...for update" when it is accessed, then a user can make their changes, then the lock is released once they hit submit.

The problem that I see is since HTTP is a stateless protocol, the PHP connection to the database ends as soon as the execution of the script that gets the record from the database ends. I'm assuming that when that happens, the lock dissolves automatically and the whole scheme is rendered useless. Am I right on this? If so, is there a way to persist the lock even after the loading PHP script ends, so that it is released on a COMMIT issued by the PHP script that handles the saving?

Or is optimistic locking the only way to go?
0
rruvin
Asked:
rruvin
1 Solution
 
splishsplashCommented:
First, if you want to maintain state, you either set a cookie, keep everything tracked in the GET string, or in the GET string pass the sessionID around and store session data on the server.

To do row-level locking (aka pessimistic locking) you need to use InnoDB or BDB tables types for MySQL.

As for pessimmistic versus optimistic locking, that will depend on the application, load, and how much time you want to spend working on the project.



0
 
rruvinAuthor Commented:
Yes, I am using InnoDB tables.

So are you saying that if I were to store the link to the database in a session, the link would not be terminated once the execution of the script ended? And then when the save script is run I could resume from where I left off and the row will remain locked in between?
0
 
rruvinAuthor Commented:
Yes, I am using InnoDB tables.

So are you saying that if I were to store the link to the database in a session, the link would not be terminated once the execution of the script ended? And then when the save script is run I could resume from where I left off and the row will remain locked in between?
0
 
xkubaCommented:
Don't use database locks for this task. They are not intended for purposes like this.

Instead, add extra columns to the tables you want to perform locking on. E.g. lock_userid (who is holding lock to the row) and lock_time (when the lock was aquirred). Of course, you have to check for the locks manually. Locks in this schema can also expire... I think that this approach has many many advantages...
0
 
snoyes_jwCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation in the Cleanup topic area:

Answered by splishsplash

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

snoyes_jw
EE Cleanup Volunteer
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now