How can I lock a table in DBIx:Class?

Question

I am writing a small application with

• Mojolicious
• DBIx::Class
• Hypnotoad which is a pre-forking web server.
• MySQL

In my application I need to do the following;

1. Do some complex processing ( takes a minute of so to complete )
2. insert resulting data from above processing into tables
3. obtain the last auto increment of some tables, do some more processing.
4. use the values from (3) as part of an insert into another table ( a junction table )

Here is some sample code starting at step 2

#step 2
my $device = $device_rs->create(
 {
  devicename      => $deviceName,
  objects         => \@objects
  object_groups   => \@objectgroups,

}
);

#step 3
my $lastogid  = $db->resultset('ObjectGroup')->get_column('objectgroupid')->max;
my $lastobid  = $db->resultset('Object')->get_column('objectid')->max;

my $obgcount = scalar(@objectgroups);
my $objcount = scalar(@objects);

my $ogoffset = $lastogid - $obgcount;
my $oboffset = $lastobid - $objcount;

#now increment the object/group ids by the offset which will be inserted into the many-  many table
foreach my $hash (@childobjects) {
 $hash->{'objectgroup_objectgroupid'} += $ogoffset;
 $hash->{'object_objectid'}           += $oboffset;
}

#step 4  - populate the junction table
$db->resultset('ObjectGroupHasObjects’)->populate(\@childobjects);

Now due to having multiple threads going a once the values obtained from step 3 may not be correct ( for the current ‘device’ ).

I’m trying to find a way around this issue. The only thing I can think of at the moment is putting a lock on the database tables before step 2) and unlocking after step 4).

How can I do this in DBIx::Class and is this likely to resolve my issue?

Thank you.

Answer 1

Something like

$schema->dbh_do("LOCK TABLES names");
...
...
$schema->dbh_do("UNLOCK TABLES");

Source: http://www.perlmonks.org/?node_id=854538

Also see: How to avoid race conditions when using the find_or_create method of DBIx::Class::ResultSet?

and SQLHackers::SELECT#SELECT_..._FOR_UPDATE