[Dbix-class] SELECT ROWS FOR UPDATE/SHARE

[Jess Robinson]

On Sun, 18 Mar 2007, Oleg Pronin wrote:

> Hi. I use PostgreSQL database and want to use it's row locking system.
> That's why i need to execute statements like this:
> SELECT * FROM table WHERE condition FOR UPDATE (FOR SHARE);
>
> I didn't find a nice solution for that.
> I would like to use row-locking in some way like that:
> $rs->search(
>  {column => $value},
>  {
>    rows => 1,
>    locking => 'update',
> }
> );
>
> The only solution i found without copy-pasting code of  '_select' and
> '_execute' functions of storage is
>
> in my schema:
> __PACKAGE__->storage_type('My::Storage');
>
> in my::storage
>
> package My::Storage;
> use base qw/DBIx::Class::Storage::DBI::Pg/;
> use strict;
>
> our $_LOCKING = 0;
>
> sub _select {
>    my $self = shift;
>    $_LOCKING = $_[3]->{locking} eq 'update' ? 1 : 2 if defined
> $_[3]->{locking};
>   $self->SUPER::_select(@_);
> }
>
> sub sth {
>    my ($self, $sql) = @_;
>    # 3 is the if_active parameter which avoids active sth re-use
>    if ($_LOCKING) {
>        $sql .= $_LOCKING == 1 ? ' FOR UPDATE' : ' FOR SHARE';
>        $_LOCKING = 0;
>    }
>    return $self->dbh->prepare_cached($sql, {}, 3);
> }
> I understand this is ugly.
> Is there a better solution?
>
>

Seems fairly sane to me. Would you like to work that into a patch for the 
Postgres Storage class and add some tests?

Jess