Multi user MS Access database

[Jeanh]

Hi,

I have an access database with a single simple table.

Jobid Status
1 busy
2 busy
3 free
4 free
5 free

When a user wants the next job, I SELECT the next lowest free number and UPDATE the status to 'busy'. A job can take a random amount of time to complete before that user requests another job.

Problems happen when multiple users access the database. Everything goes out of sync.

I tried ADO and DBI, both have the same problem. The connections don't seem to update the changes made by the other connections until it's too late. Some jobs get processed multiple times! The connections are always open. If I reconnect each time to get a job the problem goes away because the data is refreshed in each connection.

SELECT:
my $SQL = "SELECT JOBID, FILE FROM MYTABLE WHERE JOBID=(SELECT MIN (JOBID) FROM FILESET WHERE STATUS=?)";

UPDATE:
my $SQL = "UPDATE FILESET SET STATUS=? WHERE JOBID=?";
my $obj = $dbh->prepare($SQL);
$obj->execute($new_status,$record->{JOBID});
$obj->finish;

}

Thanking you in advance. I'm losing hours of sleep!

[hspc]

try to do both select and update within a transaction to avoid dirty reads. (in this case reading a free id that is taken by another user before calling the update.
use an appropriate isolation level (in this case I think adXactReadCommitted is good).

[Jeanh]

Thanks for your quick and helpful response.

I've checked that the default isolationlevel for my connection is 4096 = adXactReadCommitted. Next, I wrapped the SELECT statement and UPDATE in a transaction. It seems there is still a problem somewhere because some jobs are getting processed more than once. This is an example in ADO and perl. The actual output is shown below.


sub get_next_job{

my ($conn, $old_status, $new_status) = @_; # old status = free, new_status = busy

my $record;

# get the next lowest free jobid number

my $SQL = "SELECT * FROM FILESET WHERE JOBID=(SELECT MIN JOBID) FROM FILESET WHERE STATUS='$old_status')";

$conn->BeginTrans();

my $RS = Win32::OLE->new("ADODB.Recordset");

$RS->open ($SQL, $conn, adOpenDynamic, adLockPessimistic);

eval {
unless($RS->EOF)
{
$record->{ 'JOBID' } = $RS->Fields("JOBID")->value;
$record->{ 'DRVNAME' } = $RS->Fields("DRVNAME")->value;
$record->{ 'SETTING' } = $RS->Fields("SETTING")->value;
$record->{ 'APPLICATION' } = $RS->Fields("APPLICATION")->value;
$record->{ 'FILE' } = $RS->Fields("FILE")->value;
$RS->update("STATUS", $new_status);
}
};

if ( $@ ) #did any errors occur
{

$record = undef;
$conn->RollbackTrans();
}
else
{
$conn->CommitTrans();
}

$RS->close;

return $record || undef;
}


job 1 finished from thread 1
job 2 finished from thread 3
job 1 finished from thread 2
job 2 finished from thread 1
job 4 finished from thread 2
job 3 finished from thread 3
job 7 finished from thread 3
job 6 finished from thread 2
job 5 finished from thread 1
job 9 finished from thread 2
job 8 finished from thread 3
job 10 finished from thread 1

Note: Three threads were used and each thread has its own connection.

Any help would be greatly appreciated!

[cjard]

Why would you be using a designed-for-single-user-simple-databases system like access, for a task like that?

[Jeanh]

Well what I want to is really quite simple. All I want to do is synchronize a small number of processes (roughly 3) so jobs are not repeated! I'm not asking for the world. Access should be able to cope with this.

Additionally, I can give the mdb file to others who can easily update the contents without any knowledge of databases.