DBIx::Class 'select.... for update' confusion

tomgracey has asked for the wisdom of the Perl Monks concerning the following question:

Hello all

I am confused about the mechanics of 'select.. for update' under DBIx::Class (I'm using MySQL but I suppose that shouldn't matter.) So I have reduced it down to the following test case:

my $guard = $schema->txn_scope_guard;

my $rs = $schema->resultset('TestTable')->search({
    status => 'available'
},
{ 
    rows => 1,
    'for' => 'update'
});

my $id_before_update = $rs->first->id;

my $updated = $rs->update({
    status => 'reserved'
});

$guard->commit;

my $id_at_end = $rs->first->id;

print "id before update: $id_before_update\nid at end: $id_at_end\n";
[download]

The table looks like this initially:

+----+-----------+
| id | status    |
+----+-----------+
|  1 | available |
|  2 | available |
|  3 | available |
+----+-----------+
[download]

and after execution like this:

+----+-----------+
| id | status    |
+----+-----------+
|  1 | reserved  |
|  2 | available |
|  3 | available |
+----+-----------+
[download]

The schema was created by DBIx::Class::Schema::Loader and looks like this:

__PACKAGE__->table("test_table");
__PACKAGE__->add_columns(
  "id",
  { data_type => "bigint", is_nullable => 0 },
  "status",
  { data_type => "varchar", is_nullable => 1, size => 255 },
);
[download]

(with boilerplate removed). When I run the above code I get the following:

id before update: 1
id at end: 2
[download]

To me this seems like odd behaviour, because after $rs is selected with rows=1 it surely should only contain a single row with a single id...?

Any idea what stupid thing have I (not) done this time?

Comment on DBIx::Class 'select.... for update' confusion Select or Download Code

Replies are listed 'Best First'.
Re: DBIx::Class 'select.... for update' confusion by poj (Abbot) on Dec 14, 2017 at 21:43 UTC
From the docs it looks like `->first` resets the resultset (causing a fresh query to storage). poj	[reply] [d/l]
Re^2: DBIx::Class 'select.... for update' confusion by duff (Parson) on Dec 14, 2017 at 21:57 UTC
And more importantly, you didn't specify an `order by` clause, so the exact order of your search results will change over time. duff	[reply] [d/l]
Re^3: DBIx::Class 'select.... for update' confusion by tomgracey (Scribe) on Dec 14, 2017 at 22:05 UTC
Actually in this particular case the order doesn't matter... but thanks for pointing it out!	[reply]
Re^2: DBIx::Class 'select.... for update' confusion by tomgracey (Scribe) on Dec 14, 2017 at 22:02 UTC
Ah yes, silly me. Thanks...!	[reply]


laziness, impatience, and hubris
	PerlMonks