Обсуждение: ExclusiveLock and Python

Hi all,

doing the following query

select * from pg_locks where  mode = 'ExclusiveLock';

I obtain:


 relation | database | transaction |  pid  |     mode      | granted
----------+----------+-------------+-------+---------------+---------
          |          |     2560899 | 20404 | ExclusiveLock | t


ExclusiveLock on what ?

BTW I obtain this ExclusiveLock just doing a connection
with a Python program using the pgdb interface:

db_connection = pgdb.connect( )

and I obtain:

$> ps -eafwww  | grep post

postgres 10408 21357  0 12:02 ?        00:00:00 postgres: kalman kalman
[local] idle in transaction

I seen inside the pgdb.py code and I found that in the constructor of
pgdbCnx is performed:
    src.execute("BEGIN")

and the methods commit and rollback:


 def commit(self):
        try:
            src = self.__cnx.source()
            src.execute("COMMIT")
            src.execute("BEGIN")
        except:
            raise OperationalError, "can't commit."

    def rollback(self):
        try:
            src = self.__cnx.source()
            src.execute("ROLLBACK")
            src.execute("BEGIN")
        except:
            raise OperationalError, "can't rollback."


so it seem that is no an "autocommit" behaviour,
How can avoid to open a transaction if is not needed ?
Exist a way to emulate an autocommit behaviour or should I modify the
pgdb.py ?



Regards Gaetano

"Gaetano Mendola" <mendola@bigfoot.com> writes:
> select * from pg_locks where  mode = 'ExclusiveLock';
>  relation | database | transaction |  pid  |     mode      | granted
> ----------+----------+-------------+-------+---------------+---------
>           |          |     2560899 | 20404 | ExclusiveLock | t

> ExclusiveLock on what ?

Your own transaction, that's what.

If one transaction needs to wait for a specific other transaction, it
does so by attempting to grab ShareLock on the transaction ID.  When
the other transaction completes (and releases its ExclusiveLock on its
ID), the waiter is released.

Offhand I think this is only used to implement waits associated with
SELECT FOR UPDATE row locking --- all other locks are on tables or
table-like objects.

            regards, tom lane

"Michael A. Schulte" <michael.schulte@sun.com> writes:
> Tom Lane wrote:
>> Offhand I think this is only used to implement waits associated with
>> SELECT FOR UPDATE row locking --- all other locks are on tables or
>> table-like objects.

> What about two processes updating the same row?

Right, that case is also a row lock.

> I thougth
> PostGres locks the row in this case and this would also
> be reflected as an entry in pg_locks with mode
> ExclusiveLock.

There's no pg_locks entry for a row lock.

            regards, tom lane

"Michael A. Schulte" <michael.schulte@sun.com> writes:
> Tom Lane wrote:
>> Offhand I think this is only used to implement waits associated with
>> SELECT FOR UPDATE row locking --- all other locks are on tables or
>> table-like objects.

> What about two processes updating the same row?

Right, that case is also a row lock.

> I thougth
> PostGres locks the row in this case and this would also
> be reflected as an entry in pg_locks with mode
> ExclusiveLock.

There's no pg_locks entry for a row lock.

            regards, tom lane

Tom Lane wrote:
> "Gaetano Mendola" <mendola@bigfoot.com> writes:
>
>>select * from pg_locks where  mode = 'ExclusiveLock';
>> relation | database | transaction |  pid  |     mode      | granted
>>----------+----------+-------------+-------+---------------+---------
>>          |          |     2560899 | 20404 | ExclusiveLock | t
>
>
>>ExclusiveLock on what ?
>
>
> Your own transaction, that's what.
>
> If one transaction needs to wait for a specific other transaction, it
> does so by attempting to grab ShareLock on the transaction ID.  When
> the other transaction completes (and releases its ExclusiveLock on its
> ID), the waiter is released.
>
> Offhand I think this is only used to implement waits associated with
> SELECT FOR UPDATE row locking --- all other locks are on tables or
> table-like objects.

What about two processes updating the same row? I thougth
PostGres locks the row in this case and this would also
be reflected as an entry in pg_locks with mode
ExclusiveLock.

michael

--
Michael Schulte, Solaris Kernel Development, Sun Microsystems