Thread: Python client + select = locked resources???

Hi!

I wanna ask something. I came from IB/FB world.
In this world I was everytime in transaction, because of reads are also working under transactions.
In the FB world the transactions without any writes/updates are not locking the database, so another clients can makes a transactions on any records.
And also can add new fields to the tables.

Now I used Pylons webserver (Python) with PyGRESQL, and DBUtils for cached database connections/cursors.

Today I saw a locking situation in many times.

0.) I started Pylons web server, and in the browser I request for a simple view (without modify anything).
1.) I opened PGAdmin.
2.) I move the focus to the table "X".
3.) I opened an SQL editor and try to make two column adds:
alter table X add test_a date;
alter table X add test_b date;
4.) After the the PGAdmin's Query Execution (F5) nothing happened. I see this menu is disabled, and PGAdmin is locked for new operations.
5.) When I simply close "Pylons web server", the PGAdmin quickly finished with this table restructure without problems...

The problem can repeatable in any times.

This is very hateable thing, because in this view I don't modify anything, I use only "selects", nothing other things.

And I wanna solve this problem, because if I must do some modifications in the online database (for example: add a new field), I don't want to shut down the webserver with all online clients...

I simplified this "bug" to see this without web server, dbutils, and other layers.

I wrote this python code:

import os, sys, pgdb

fmtstring = '%s:%s:%s:%s'
fmtstring = fmtstring % ('127.0.0.1',
    'anydb',
    'anyuser', 'what?')
db = pgdb.connect (fmtstring)
print "ok"
cur = db.cursor()
cur.execute('select * from testtable')
rek = cur.fetchone()
cur.close()
while 1:
    pass
db.close()

After start this I tried to add a new field to the testtable from PGAdmin's Query GUI:
alter table testtable add test_001 date;

With the cur.execute("select * from testtable") I got lock "error", the PGAdmin query is running and running and running... :-(

Without this cur.execute the alter table finished without locks.

When I put a "db.rollback()" before while the lock vanished...

So pg is hold all records I fetched? They are not useable freely in a simple, readonly select?

Please help me SOS if possible, I must find a way to get out from these locks!

And please help me: how to check that I'm in transaction or not?

Thanks for your help:
    dd

On Mon, 2009-06-29 at 13:36 +0200, durumdara wrote:

> I wanna ask something. I came from IB/FB world.

InterBase / FireBird ?

> In this world I was everytime in transaction, because of reads are
> also working under transactions.

Just like PostgreSQL. You can't run a query without a transaction in
PostgreSQL; if you don't issue an explicit BEGIN, it'll do an implicit
BEGIN/COMMIT around the statement.

> In the FB world the transactions without any writes/updates are not
> locking the database, so another clients can makes a transactions on
> any records.

PostgreSQL doesn't "lock the database" for reads or writes. Transactions
do take out various levels of lock on tables when you do things with
those tables. See the locking documentation:

http://www.postgresql.org/docs/8.3/static/explicit-locking.html

Additionally, PostgreSQL can take out share and update locks against
rows, as the documentation mentions.

> 0.) I started Pylons web server, and in the browser I request for a
> simple view (without modify anything).
> 1.) I opened PGAdmin.
> 2.) I move the focus to the table "X".
> 3.) I opened an SQL editor and try to make two column adds:
> alter table X add test_a date;
> alter table X add test_b date;

ALTER TABLE does take out an exclusive lock on the table. See the
manual:

http://www.postgresql.org/docs/8.3/static/explicit-locking.html

If there's other work in progress, it can't get the exclusive lock until
that work completes.

> And I wanna solve this problem, because if I must do some
> modifications in the online database (for example: add a new field), I
> don't want to shut down the webserver with all online clients...

You should not have to. If you can't get a lock on the table, then most
likely the web app is holding transactions open instead of opening them,
doing work, and promptly committing / rolling back.

Try connecting to the database with psql and running
  "select * from pg_stat_activity"
while the web app is running. You should see only "IDLE" or working
connections, never idle in transaction. If you have anything idle in a
transaction for more than a few moments you WILL have problems, because
if those transactions have SELECTed from the table you're trying to
alter they'll hold share locks that will prevent ALTER TABLE from
grabbing an exclusive lock on the table.


>         cur = db.cursor()
>         cur.execute('select * from testtable')
>         rek = cur.fetchone()
>         cur.close()
>         while 1:
>             pass

Here you're holding a transaction open and idle. Wrong move. Close the
transaction (dispose the cursor) and then open a new transaction to do
more work.

--
Craig Ringer

Hi!

2009.06.29. 18:26 keltezéssel, Craig Ringer írta:

On Mon, 2009-06-29 at 13:36 +0200, durumdara wrote:
 

I wanna ask something. I came from IB/FB world.   

InterBase / FireBird ? 

Yes, sorry for short descriptions.

In this world I was everytime in transaction, because of reads are
also working under transactions.   

Just like PostgreSQL. You can't run a query without a transaction in
PostgreSQL; if you don't issue an explicit BEGIN, it'll do an implicit
BEGIN/COMMIT around the statement. 

Aha... So if I'm getting out from normal transactions I moved into implicit autocommit way.
Hmmm... Then that is meaning that every statement is in new transaction context which can makes inconsistency in the views...
For example (pseudo):
select item_head, count(items)
select items

Possible: count(items) <> len(fetchall(items)) if someone committed a new record into "items" table...

Am I thinking good?

So I need:

begin;
select item_head, count(items)
select items
rollback;

to get full consistent data-sets?

In the FB world the transactions without any writes/updates are not
locking the database, so another clients can makes a transactions on
any records.   

PostgreSQL doesn't "lock the database" for reads or writes. Transactions
do take out various levels of lock on tables when you do things with
those tables. See the locking documentation:

http://www.postgresql.org/docs/8.3/static/explicit-locking.html 

The locks meaning in my "dictionary" that DB will prevent some functions on the table to avoid the consistency and other errors.

For example DBISAM is working in that way. We can make record modifications, etc, but for "altering table" I need to shut down all of the clients!
Because DBISAM put a "file lock" to this table while it altered.

But in FireBird is allowed to add a new field to table when it is used. Because FB is makes a new record version, and this version used for the next queries.
And I can add a new field without shutting down all of the clients.

Additionally, PostgreSQL can take out share and update locks against
rows, as the documentation mentions. 

Ok.

0.) I started Pylons web server, and in the browser I request for a
simple view (without modify anything).
1.) I opened PGAdmin.
2.) I move the focus to the table "X".
3.) I opened an SQL editor and try to make two column adds:
alter table X add test_a date;
alter table X add test_b date;   

ALTER TABLE does take out an exclusive lock on the table. See the
manual:

http://www.postgresql.org/docs/8.3/static/explicit-locking.html

If there's other work in progress, it can't get the exclusive lock until
that work completes. 

Thanks. So this was I ask from this mailing list before I started to use PGDB.

But they are said to me that PGDB is working in same mode like FB.

And now I know from your mail that isn't true - it will be easier if I shut down the webserver, make the modifications on PGDB and after that I restart them all.

And I wanna solve this problem, because if I must do some
modifications in the online database (for example: add a new field), I
don't want to shut down the webserver with all online clients...   

You should not have to. If you can't get a lock on the table, then most
likely the web app is holding transactions open instead of opening them,
doing work, and promptly committing / rolling back.

Try connecting to the database with psql and running  "select * from pg_stat_activity"
while the web app is running. You should see only "IDLE" or working
connections, never idle in transaction. If you have anything idle in a
transaction for more than a few moments you WILL have problems, because
if those transactions have SELECTed from the table you're trying to
alter they'll hold share locks that will prevent ALTER TABLE from
grabbing an exclusive lock on the table. 

Hmmm... Thanks for your info!!!

        cur.close()       while 1:           pass   

Here you're holding a transaction open and idle. Wrong move. Close the
transaction (dispose the cursor) and then open a new transaction to do
more work. 

Yes. If I can make a rollback on it, all of resources released.
Now I search for a way to "force dbutils to it must make a rollback before it re-move the connection into it's pool", or a way to I can do this easily from the webserver...

Thanks for your help and please suggest me a transaction mode to consistent views (see above).

    dd

Hey.  You should turn on all statement logging and tail the postgresql
logs to see what your python app is really doing.

On Monday 29 June 2009 09:26:24 am Craig Ringer wrote:
> Try connecting to the database with psql and running
>   "select * from pg_stat_activity"
> while the web app is running. You should see only "IDLE" or working
> connections, never idle in transaction. If you have anything idle in a
> transaction for more than a few moments you WILL have problems, because
> if those transactions have SELECTed from the table you're trying to
> alter they'll hold share locks that will prevent ALTER TABLE from
> grabbing an exclusive lock on the table.

I used psql to run the query "select * from pg_stat_activity;" and it shows
three (3) lines , the select statement, <IDLE>, and the last one is <IDLE> in
transaction.  No one else is using the database because it's a on my machine.
So can you explain why I have an  "<IDLE> in transaction" listed and does it
mean I can't alter the table from some other program like pgAdmin3???

Also "commit" or "rollback" gives a warning "there is no transaction in
progress".  So what gives?


--
John Fabiani

On Jul 1, 2009, at 9:21 AM, johnf wrote:

> On Monday 29 June 2009 09:26:24 am Craig Ringer wrote:
>> Try connecting to the database with psql and running
>>   "select * from pg_stat_activity"
>> while the web app is running. You should see only "IDLE" or working
>> connections, never idle in transaction. If you have anything idle
>> in a
>> transaction for more than a few moments you WILL have problems,
>> because
>> if those transactions have SELECTed from the table you're trying to
>> alter they'll hold share locks that will prevent ALTER TABLE from
>> grabbing an exclusive lock on the table.
>
> I used psql to run the query "select * from pg_stat_activity;" and
> it shows
> three (3) lines , the select statement, <IDLE>, and the last one is
> <IDLE> in
> transaction.  No one else is using the database because it's a on my
> machine.
> So can you explain why I have an  "<IDLE> in transaction" listed and
> does it
> mean I can't alter the table from some other program like pgAdmin3???
>
> Also "commit" or "rollback" gives a warning "there is no transaction
> in
> progress".  So what gives?

Using the values in the client_addr and client_port columns of the
pg_stat_activity output you can track that idle transaction back to
it's originating client process.  First, since you say that all
connections should be coming from the same host, verify that the ip
listed under client_addr is the same as the host the db is on.  If
not, determine what host the ip actually does refer to.  Then on
whatever host the idle transaction is coming from run:

lsof -itcp:<port>

where <port> is the value from the client_port column in the earlier
pg_stat_activity output.  In the lsof output there should be a pid
column take the pid value and run:

ps aux | grep <pid>

substituting the actual pid value for <pid>.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k