?
Solved

Pessimistic locking and a web interface

Posted on 2003-03-04
6
Medium Priority
?
297 Views
Last Modified: 2013-12-12
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
Comment
Question by:rruvin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 1

Accepted Solution

by:
splishsplash earned 80 total points
ID: 8076868
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
 

Author Comment

by:rruvin
ID: 8079727
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
 

Author Comment

by:rruvin
ID: 8082287
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
 

Expert Comment

by:xkuba
ID: 8089846
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
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 9682554
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question