Re: Proposal for new $h->{ReadOnly} attribute

Doing "set transaction read only" on Oracle has additional side-effects
besides making the connection unable to write: it switches the read
consistency level from per-statement (the default) to per-transaction.

This effectively freezing the connection in time, allowing multiple
queries to be executed against changing data to "match up".

It might surprise someone who thought they were just getting a read-only
connection, especially if they're polling the database for changes and
wanted to be lighter weight. It consumes more resources, not less,
since Oracle must reverse out all changes since your "set trans.."
statement with each query, and might result in a "snapshot too old" error.

I don't know if/how other databases implement this feature, and give it
such a possibly misleading name. Does anyone else?

Scott Smith

Tim Bunce wrote:
> I've just added this to the DBI docs:
>
> =item C (boolean, inherited)
>
> An application can set the C attribute of a handle to a true value to
> indicate that it will not be attempting to make any changes (insert, delete,
> update etc) using that handle or any children of it.
>
> If the driver can make the handle truely read-only (by issing a statement like
> "C" as needed, for example) then it should.
> Otherwise the attribute is simply advisory.
>
> A driver can set the C attribute itself to indicate that the data it
> is connected to cannot be changed for some reason.
>
> Library modules and proxy drivers can use the attribute to influence their behavior.
> For example, the DBD::Gofer driver considers the C attribute when
> making a decison about whether to retry an operation that failed.
>
> =cut
>
> Any thoughts?
>
> Tim.
>

Re: Proposal for new $h->{ReadOnly} attribute

On Fri, May 04, 2007 at 08:15:53AM -0500, Scott Smith wrote:
> Doing "set transaction read only" on Oracle has additional side-effects
> besides making the connection unable to write: it switches the read
> consistency level from per-statement (the default) to per-transaction.
>
> This effectively freezing the connection in time, allowing multiple
> queries to be executed against changing data to "match up".

s/freezing the connection in time/freezing the transaction in time/ ?

So effectively no change if AutoCommit is on?

If AutoCommit is off, then the effect lasts until the next commit/rollback,
but an app that's "read only" might not be doing any commits.

I agree that's an issue.

> It might surprise someone who thought they were just getting a read-only
> connection, especially if they're polling the database for changes and
> wanted to be lighter weight. It consumes more resources, not less,
> since Oracle must reverse out all changes since your "set trans.."
> statement with each query, and might result in a "snapshot too old" error.

That's certainly a good argument for the ReadOnly attribute not doing
a "set transaction read only" on Oracle. I'll tweak the docs.

It's also an argument in favor of ReadOnly not being a simple boolean.

Thanks.

Tim.

p.s. You're *just* in time for 1.55.

> I don't know if/how other databases implement this feature, and give it
> such a possibly misleading name. Does anyone else?
>
> Scott Smith
>
> Tim Bunce wrote:
> >I've just added this to the DBI docs:
> >
> >=item C (boolean, inherited)
> >
> >An application can set the C attribute of a handle to a true
> >value to
> >indicate that it will not be attempting to make any changes (insert,
> >delete,
> >update etc) using that handle or any children of it.
> >
> >If the driver can make the handle truely read-only (by issing a statement
> >like
> >"C" as needed, for example) then it should.
> >Otherwise the attribute is simply advisory.
> >
> >A driver can set the C attribute itself to indicate that the
> >data it
> >is connected to cannot be changed for some reason.
> >
> >Library modules and proxy drivers can use the attribute to influence their
> >behavior.
> >For example, the DBD::Gofer driver considers the C attribute when
> >making a decison about whether to retry an operation that failed.
> >
> >=cut
> >
> >Any thoughts?
> >
> >Tim.
> >

Re: Proposal for new $h->{ReadOnly} attribute

Tim Bunce wrote:
> On Fri, May 04, 2007 at 08:15:53AM -0500, Scott Smith wrote:
>> Doing "set transaction read only" on Oracle has additional side-effects
>> besides making the connection unable to write: it switches the read
>> consistency level from per-statement (the default) to per-transaction.
>>
>> This effectively freezing the connection in time, allowing multiple
>> queries to be executed against changing data to "match up".
>
> s/freezing the connection in time/freezing the transaction in time/ ?
>

Good point, speaking of small differences which make a difference.

> So effectively no change if AutoCommit is on?
>
> If AutoCommit is off, then the effect lasts until the next commit/rollback,
> but an app that's "read only" might not be doing any commits.

Wow. I hadn't thought of using commit/rollback to intentionally select
when to bump the time point forward.

>
> I agree that's an issue.
>
>> It might surprise someone who thought they were just getting a read-only
>> connection, especially if they're polling the database for changes and
>> wanted to be lighter weight. It consumes more resources, not less,
>> since Oracle must reverse out all changes since your "set trans.."
>> statement with each query, and might result in a "snapshot too old" error.
>
> That's certainly a good argument for the ReadOnly attribute not doing
> a "set transaction read only" on Oracle. I'll tweak the docs.
>
> It's also an argument in favor of ReadOnly not being a simple boolean.
>
> Thanks.
>
> Tim.
>
> p.s. You're *just* in time for 1.55.

Yeah. Sorry to reply four days late...

Scott

>
>> I don't know if/how other databases implement this feature, and give it
>> such a possibly misleading name. Does anyone else?
>>
>> Scott Smith
>>
>> Tim Bunce wrote:
>>> I've just added this to the DBI docs:
>>>
>>> =item C (boolean, inherited)
>>>
>>> An application can set the C attribute of a handle to a true
>>> value to
>>> indicate that it will not be attempting to make any changes (insert,
>>> delete,
>>> update etc) using that handle or any children of it.
>>>
>>> If the driver can make the handle truely read-only (by issing a statement
>>> like
>>> "C" as needed, for example) then it should.
>>> Otherwise the attribute is simply advisory.
>>>
>>> A driver can set the C attribute itself to indicate that the
>>> data it
>>> is connected to cannot be changed for some reason.
>>>
>>> Library modules and proxy drivers can use the attribute to influence their
>>> behavior.
>>> For example, the DBD::Gofer driver considers the C attribute when
>>> making a decison about whether to retry an operation that failed.
>>>
>>> =cut
>>>
>>> Any thoughts?
>>>
>>> Tim.
>>>
>