Re: Unable to achieve safe record contention.
Dear Boxes,
I had the same problem with the 5.2 version, and I’m migrating and testing this solution to 6.00.
This is the “trick” that I use in order to prevent two user to update the same set of record.
It is a kind of “row” lock, not a single record lock, but I hope that this can help you.
the trick is to lock the screen from where you or the user update the data.
It is NOT an easy solution, but it work for me, and I can manage easily more than 40 user at the same time.
1 - design a table that hold session data. This is my schema for postgresql, but you can adapt it for yor db. Sorry for tablename that are in Italian:
CREATE TABLE adm_utenti_collegati
(
id_sessione character varying(32) NOT NULL,
login_utente character varying(32),
dataora_login timestamp without time zone,
dataora_ultima_azione timestamp without time zone,
lock_tabella character varying(64),
identificativo_alog character varying(20),
lock_tipo character(1) NOT NULL,
CONSTRAINT adm_utenti_collegati_pkey PRIMARY KEY (id_sessione , lock_tipo ),
CONSTRAINT adm_utenti_collegati_identificativo_alog_fkey FOREIGN KEY (identificativo_alog)
REFERENCES all_dati_iniziali (identificativo_alog) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE,
autovacuum_enabled=true
);
What this table do:
record the session id (1st row) with the username (2nd row), the login time (3rd), last action done (4rd), screen in use (5), your UNIQUE id for locking the data that you need (mine is identificativo_alog), kind of lock (read or edit or what you want).
2 - create two (2) screen for the record that you want to protect, one is read_only, the second is update.
3 - when the user go from the read_only to the live (update) screen, this is my script in order to check if another user use the same screen and the same data: I put the comments in eglish too, but if something is not ok, use Google to traslate from italian.
You need to put this script in the Event part of the module. I put them in the OnLoad Event.
Code is below:
//prendo il nome della schermata per dopo
// read the script name to use later
$file = $_SERVER["SCRIPT_NAME"];
//break the pah
$break = Explode('/', $file);
// restituisce il solo nome senza l'estensione .php
// comodo sia per la tabella lock, sia per la redirect
//take only the name without the .php extension
// for lock table and redirect
$pfile = str_replace(".php", "", $break[count($break) - 1]) ;
/* those are useful for me only, not for thee script.
*/
$issOrAcq = substr($pfile, 0, 1);
$redirForm = substr($pfile, 0, 3);
//qui ? il path dove ? salvata la sessione
// session save path
$sessPath = ini_get('session.save_path');
//session id, save in the table
$sessId = session_id();
//time and date
$dataOra = date("Y-m-d H:i:s");
$dataOggi = date("Y-m-d");
//query value
$valore_query = "";
//login name
$login_utente = "";
//last action
$ultima_azione = "";
//username logged in
$nome_utente=[sm_global_login];
/**
* Check for lock
*/
//if the lock is in effect where to return
$ritornoMaschera='issUsr_datiInizialiRead';
/**
* this is the query that check if someone is connected to the data that you need
*/
$check_sql = 'SELECT id_sessione, login_utente, dataora_ultima_azione '
. ' FROM adm_utenti_collegati '
. " WHERE "
. " lock_tabella ='" . $pfile . "' AND "
. " identificativo_alog = '" . [identificativo_alog] . "';";
sc_lookup(sessione_id, $check_sql);
/* if no one own the lock */
if (empty({sessione_id})){
$valore_query="";
} else {
// someone own the lock
$valore_query = {sessione_id[0][0]};
$login_utente = {sessione_id[0][1]};
$ultima_azione = {sessione_id[0][2]};
}
// if the query is null
if ($valore_query === "") {
//imposto il lock - lock set
$update_sql = 'UPDATE adm_utenti_collegati '
. " SET login_utente='" . [sm_global_login] . "', "
. " dataora_ultima_azione='" . $dataOra . "', "
. " lock_tabella='" . $pfile . "', "
. " identificativo_alog='" . [identificativo_alog] . "' "
. " WHERE id_sessione='" . $sessId . "'"
. " AND lock_tipo ='t';";
sc_exec_sql($update_sql);}
else
{
/*****
* test se stesso ID sessione:
* se per qualche motivo strano ? lo stesso
* tipo refresh con f5
* reset e continuo lock scaduto.
*******/
if ($sessId == $valore_query )
{
$update_sql = 'UPDATE adm_utenti_collegati '
. " SET login_utente='" . [sm_global_login] . "', "
. " dataora_ultima_azione='" . $dataOra . "', "
. " lock_tabella='" . $pfile . "', "
. " identificativo_alog='" . [identificativo_alog] . "' "
. " WHERE id_sessione='" . $sessId . "'"
. " AND lock_tipo ='t';";
sc_exec_sql($update_sql);
} else
{
/******
* non ? lo stesso ID.
* quindi verifico se la sessione esiste o no.
* se non esiste, reset del lock e ricarico il form.
* se esiste, messaggio di errore e vado alla form principale.
****/
$sessione_trackdb = $sessPath . "/sess_" . $valore_query;
if (file_exists($sessione_trackdb) && $login_utente != $nome_utente)
{
// interrompo php e scrivo il messaggio
?>
<script type="text/javascript">
alert("Sorry, this table is locked by user <?php echo $login_utente; ?>
since <?php echo $ultima_azione; ?> ");
</script>
<?php
// riapro php
//reindirizzo alla read
// Redirection
sc_redir($ritornoMaschera, identificativo_alog = [identificativo_alog]; sm_global_login = [sm_global_login], '_self');
} elseif (file_exists($sessione_trackdb) && $login_utente == $nome_utente)
{
?>
<script type="text/javascript">
alert("Sorry, but you lock this table with another session
since <?php echo $ultima_azione; ?>.
Please logout from the other session.");
</script>
<?php
sc_redir($ritornoMaschera, identificativo_alog = [identificativo_alog]; sm_global_login = [sm_global_login], '_self');
}
else
{
/***
** non esiste il file di lock
** reset tabella per quel lock
** e ricarico sessione
***/
// Delete record
$delete_sql = 'DELETE FROM adm_utenti_collegati '
. "WHERE id_sessione='" . $valore_query . "';";
sc_exec_sql($delete_sql);
// Redirection
sc_redir($pfile, identificativo_alog = [identificativo_alog]; sm_global_login = [sm_global_login], '_self');
}
}
}
Note that i breack from PHP and put some javascript in order to inform the user that the “table” (but is the screen) is locked, when it was locked and who.
So, when the scrupt load, it check if someone is in the screen AND this is the record that you whis to protect; if the screen and the record are the same, inform the user and redirect, else go on.
Please, add your code in order to manage what to do when the user inser data and free the lock. I do not put mine here because my user prefer to have the screen in front of them after inserting data; I update the table adm_utenti_collegati only when the user change screen.
And no, YOU CAN’T update the table when the user close the screen with the X on the browser. The Onclose event does not fire any script on the modern browser (annoyng popup, I suppose), so you must design your mask in order to want in order to update the session and you must explain/force/whatewer the user to do it.
If you note, I force my user to cleanly exit from the application (and reset the lock on exit, code not here), or they will be locked out until the PHP timeout expire, or you delete them from the lock table.
Even if you force the user to cleanly exit, you need to mantain the lock table, because the users prefer to exit with the [X] button, so you will find lot of old data if you do not use some maintenance script, but this will came lather if you use my solution.
I hope the code above can help you.
Best regards,
Giuseppe Giardina.
