|
|
Posted by keyvez on 09/28/98 11:41
Hi,
I have a page where many teachers and school staff members can login
and update student records. For simplicity, let's assume that the page
has three fields, the students name, phone no. and email address.
A Sample Record
Name=Justin
Phone=728-245-3596
Email=timber@lake.com
Problem Scenario
Jane, a teacher, logs in to update the students phone no. which was
entered wrongly. She wants to make it 718-245-3596 instead of
728-245-3596. She opens the page at 10:55 AM and sees that the current
state of the record is as follows
Name=Justin
Phone=728-245-3596
Email=timber@lake.com
Mike, a staff member got Justin's correct email address from his
parents and wants to update that to his record and hence opens the
record update screen at 10:56 AM, a minute after Jane had opened it. He
sees the record just as Jane saw it
Name=Justin
Phone=728-245-3596
Email=timber@lake.com
Jane updates the phone no. and changes it to 718-245-3596 at 10:57 AM
and after the page is loading she sees that Justin's record has been
updated and the new record is
Name=Justin
Phone=718-245-3596
Email=timber@lake.com
(note: the Phone no. was 728-245-3596 and has changed to 718-245-3596)
Mike updates the record and changes the email address to
timberlake@hotmail.com. After he has made his updates the page loads
again and he sees the final record as follows
Name=Justin
Phone=728-245-3596
Email=timberlake@hotmail.com
(note: the updates made by Jane are overwritten)
I know there are many ways of solving this, but I want to solve this by
using locking features provided by MSSQL, that way as long as the locks
exist on the row no one can change that row even by logging onto
Enterprise Manager as system admin and issuing an update command.
How I am trying to Solve the Problem
I am beginning a transaction from PHP and setting a lock on a record
when a user clicks the edit button. Then the user spends some random
time before he or she changes the text box and clicks the update
button. I then commit the transaction I started when the user clicked
edit.
Fact 1:
Transactions in MSSQL are Connection-dependent, if the Connection is
ended before the Transaction is committed, MSSQL automatically
Rolls-back the Transaction.
Fact 2:
There is no way in PHP where I can start a persistent ms sql connection
and ensure that I will get the same connection when I issue the
mssql_pconnect command again in the next page.
(If you have doubts read this:
http://www.php.net/manual/en/features.persistent-connections.php)
Problem:
Once I lock the rows and the script ends without committing the
transactions, the rows remain locked until a commit transaction command
is sent from THE SAME connection or that connection is closed.
Because of Fact 1, I cannot commit transactions in just about any
connection and because of Fact 2 I may not be able to use the same
connection again.
Question:
Is there a way in PHP or SQL to lock and unlock rows without having to
depend on transactions or connections.
Also, I know that I can add a lock column to every table and set it to
true when the record is opened for editing or have a table where
entries for all locked rows are made, however, this allows anyone who
can get alternate access to the database to update rows locked by these
methods.
I've used the COM class to connect to ADO dlls, the new PDO classes,
adodb libraries and odbc drivers to try and achieve this, but with no
avail.
If someone has been successful in doing this through MSSQL locking
rowlock or some other method of locking, I would greatly appreciate any
help.
Gaurav Misra
Navigation:
[Reply to this message]
|