DatabaseFeatures and supports_transactions

[maxi]

Hi,

DatabseFeatures class has a supports_transactions property for test if the db engine support transactions.
supports_transactions implementation makes  some metadata alters to check this behaivor.

Now, is really needed to do this to check transaction support?
I don't know exactly how many times this supports_transactions is called but I think what it produce unnecessary alterations in database structure, or I'm misinterpreting how it supposed works.

---
Maxi

[Karen Tracey]

First, this method is used during testing, generally not during normal operation. It's implemented as a cached property so regardless of how many times the value is tested the underlying code which creates and drops a table is only actually called once per database. Is it needed? We need to know if the database actually supports transactions or if commit/rollback/etc are simply no-ops. If you know of an alternative way of determining this, that works across all databases, please share. I added that code and I've never liked it but I don't know of another way to accomplish what it needs to do.

Karen

[maxi]

El martes, 25 de septiembre de 2012 09:05:47 UTC-3, Karen Tracey escribió:

On Mon, Sep 24, 2012 at 10:15 PM, maxi <maxir...@gmail.com> wrote:

Hi,

DatabseFeatures class has a supports_transactions property for test if the db engine support transactions.
supports_transactions implementation makes  some metadata alters to check this behaivor.

Now, is really needed to do this to check transaction support?
I don't know exactly how many times this supports_transactions is called but I think what it produce unnecessary alterations in database structure, or I'm misinterpreting how it supposed works.

Hi Karen,
 

First, this method is used during testing, generally not during normal operation. It's implemented as a cached property so regardless of how many times the value is tested the underlying code which creates and drops a table is only actually called once per database. Is it needed? We need to know if the database actually supports transactions or if commit/rollback/etc are simply no-ops. If you know of an alternative way of determining this, that works across all databases, please share. I added that code and I've never liked it but I don't know of another way to accomplish what it needs to do.

No, I just answer because it caught my attention. Why not just trust in a True/False property ?
BTW, What do you mean with "commit/rollback/etc are simply no-ops" ?

I'm working on django-firebird backend implementation (now using the new firebird python-driver "fdb") and I need reimplement supports_transactions, because I need to commit any work before drop the table, otherwise, I fall into "Object in use" error.

Regards.
---
Maxi







 

[Russell Keith-Magee]

On Tue, Sep 25, 2012 at 11:24 PM, maxi <maxir...@gmail.com> wrote:
>
>
> El martes, 25 de septiembre de 2012 09:05:47 UTC-3, Karen Tracey escribió:
>>
>> On Mon, Sep 24, 2012 at 10:15 PM, maxi <maxir...@gmail.com> wrote:
>>>
>>> Hi,
>>>
>>> DatabseFeatures class has a supports_transactions property for test if
>>> the db engine support transactions.
>>> supports_transactions implementation makes some metadata alters to check
>>> this behaivor.
>>>
>>> Now, is really needed to do this to check transaction support?
>>> I don't know exactly how many times this supports_transactions is called
>>> but I think what it produce unnecessary alterations in database structure,
>>> or I'm misinterpreting how it supposed works.
>>>
>>
>
> Hi Karen,
>
>>
>> First, this method is used during testing, generally not during normal
>> operation. It's implemented as a cached property so regardless of how many
>> times the value is tested the underlying code which creates and drops a
>> table is only actually called once per database. Is it needed? We need to
>> know if the database actually supports transactions or if
>> commit/rollback/etc are simply no-ops. If you know of an alternative way of
>> determining this, that works across all databases, please share. I added
>> that code and I've never liked it but I don't know of another way to
>> accomplish what it needs to do.
>>
>
> No, I just answer because it caught my attention. Why not just trust in a
> True/False property ?

As I recall, the reason we can't just use a property is MySQL. There's
no reliable way to tell whether the database has been set up as
InnoDB, which supports transactions, or MyISAM, which doesn't. An
experimental approach allows us to be certain.

> BTW, What do you mean with "commit/rollback/etc are simply no-ops" ?

Exactly that. On databases that don't support transactions,
commit/rollback etc are no-ops.

> I'm working on django-firebird backend implementation (now using the new
> firebird python-driver "fdb") and I need reimplement supports_transactions,
> because I need to commit any work before drop the table, otherwise, I fall
> into "Object in use" error.

Are you saying that the current 'supports_transaction' test is leaving
data in an uncommitted state? From a quick eyeball, I don't see how --
which bit is tripping up Firebird? Where do you need to add a commit?

Yours,
Russ Magee %-)

[Karen Tracey]

On Tue, Sep 25, 2012 at 11:24 AM, maxi <maxir...@gmail.com> wrote:

El martes, 25 de septiembre de 2012 09:05:47 UTC-3, Karen Tracey escribió:

First, this method is used during testing, generally not during normal operation. It's implemented as a cached property so regardless of how many times the value is tested the underlying code which creates and drops a table is only actually called once per database. Is it needed? We need to know if the database actually supports transactions or if commit/rollback/etc are simply no-ops. If you know of an alternative way of determining this, that works across all databases, please share. I added that code and I've never liked it but I don't know of another way to accomplish what it needs to do.

No, I just answer because it caught my attention. Why not just trust in a True/False property ?
BTW, What do you mean with "commit/rollback/etc are simply no-ops" ?

We have no single value we can set for this property for MySQL; whether that DB supports transactions is dependent on the configuration of the DB server. If the MySQL server is configured to use the MyISAM storage engine, then all transaction methods such as commit and rollback simply do nothing (are no-ops).
 

I'm working on django-firebird backend implementation (now using the new firebird python-driver "fdb") and I need reimplement supports_transactions, because I need to commit any work before drop the table, otherwise, I fall into "Object in use" error.

That seems odd. What does "object in use" mean? The routine is currently coded to:

1 - create table
2 - commit #1
3 - insert row into table
4 - rollback #3
5 - select count of rows in table
6 - read result of #5
7 - drop the table
 
You need to commit before #7? Doesn't seem like that should be necessary. Can you elaborate on why Firebird thinks it is necessary?

Karen

[maxi]

No, I mean if I do select * from TABLE and inmediately I try to drop the table whitout make a commit, the table object is still in use and then I gen an "Object in use" exception.

Then, I need todo
  - select * from TABLE  <- fetch
  - commit work
  - drop TABLE

And the current implementation is:

            cursor.execute('CREATE TABLE ROLLBACK_TEST (X INT)')
            self.connection._commit()
            cursor.execute('INSERT INTO ROLLBACK_TEST (X) VALUES (8)')
            self.connection._rollback()
            cursor.execute('SELECT COUNT(X) FROM ROLLBACK_TEST')
            count, = cursor.fetchone()
            # Commit is needed here
            cursor.execute('DROP TABLE ROLLBACK_TEST')
            self.connection._commit()


BTW, I was talking about it with the fdb main developer and he notes what there is a possible bug here about how Transaction must work.

So, if  support_transaction is the natural feature from my db engine I can just return True. Is Ok?

Best regards.
---
Maxi

[maxi]

Yes, I need to commit before 7 because the transaction is still active in that point.

cursor.execute('SELECT COUNT(X) FROM ROLLBACK_TEST')
count, = cursor.fetchone()

self.connection.commit()  # <-- I need add this.

cursor.execute('DROP TABLE ROLLBACK_TEST')
self.connection._commit()

cursor.execute start a new transaction, but cursor.fetchone() does not close it. Then I need a explicit commit, otherwise, drop table will fail because the table is still in use for the current active transaction.

[Andy Dustman]

On Tue, Sep 25, 2012 at 7:57 PM, Russell Keith-Magee
<rus...@keith-magee.com> wrote:
> On Tue, Sep 25, 2012 at 11:24 PM, maxi <maxir...@gmail.com> wrote:
>> No, I just answer because it caught my attention. Why not just trust in a
>> True/False property ?
>
> As I recall, the reason we can't just use a property is MySQL. There's
> no reliable way to tell whether the database has been set up as
> InnoDB, which supports transactions, or MyISAM, which doesn't. An
> experimental approach allows us to be certain.

Actually, there are several database engines in MySQL that support
transactions, not just InnoDB, though that's the most popular.

>> BTW, What do you mean with "commit/rollback/etc are simply no-ops" ?
>
> Exactly that. On databases that don't support transactions,
> commit/rollback etc are no-ops.

Not exactly. MySQLdb starts in transactional mode (required by
PEP-249), but if for some reason it's switched to autocommit mode,
rollback throws an exception. There's probably nothing in Django that
would set the connection to autocommit mode, though.

Additionally, MySQL (at least as of 5.0) will throw a warning ("Some
non-transactional changed tables couldn't be rolled back") if you do
try rollback on non-transactional tables If you run your MySQL session
in ANSI mode, this becomes an error and will produce an exception in
MySQLdb.

http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html

I've kicked around the idea of making a mysql_ansi database driver for
Django but there hasn't been a compelling reason to do it.
--
Question the answers

[Russell Keith-Magee]

On Sat, Sep 29, 2012 at 5:27 AM, Andy Dustman <farc...@gmail.com> wrote:
> On Tue, Sep 25, 2012 at 7:57 PM, Russell Keith-Magee
> <rus...@keith-magee.com> wrote:
>> On Tue, Sep 25, 2012 at 11:24 PM, maxi <maxir...@gmail.com> wrote:
>>> No, I just answer because it caught my attention. Why not just trust in a
>>> True/False property ?
>>
>> As I recall, the reason we can't just use a property is MySQL. There's
>> no reliable way to tell whether the database has been set up as
>> InnoDB, which supports transactions, or MyISAM, which doesn't. An
>> experimental approach allows us to be certain.
>
> Actually, there are several database engines in MySQL that support
> transactions, not just InnoDB, though that's the most popular.

For sure. My point is that it isn't as simple as "MySQL supports (or
doesn't support) transactions", so it can't be a simple boolean flag.

Yours,
Russ Magee %-)