[GENERAL] AccessShareLock question

[Clayton Graf]

I get an AccessShareLock in a simple select command and I am not using the
FOR SHARE clause.
The select is just "select * from controle". The connection is JDBC and the
driver is postgresql-8.4-701.jar.
What am I doing wrong?

This is the code:

Class.forName(jdbc).newInstance();
Connection connection = DriverManager.getConnection(url, login,
password);
connection.setCatalog(database);
connection.setAutoCommit(false);
Statement st = connection.createStatement();
ResultSet rs = st.executeQuery("select * from controle");
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
st.close();

Thanks,

Clayton

Re: [GENERAL] AccessShareLock question

[Clayton Graf]

Ok, but this is really my problem: I cannot perform an ALTER TABLE with the
system in production mode, because the ALTER TABLE hangs due an
AccessShareLock.

We use two-tier mode, so is it necessary to shutdown all users before
perform an ALTER TABLE? Is it this true?

Thanks,

Clayton

2009/12/19 Jaime Casanova 

> On Sat, Dec 19, 2009 at 9:04 AM, Clayton Graf 
> wrote:
> > I get an AccessShareLock in a simple select command and I am not using
> the
> > FOR SHARE clause.
>
> http://www.postgresql.org/docs/current/static/explicit-locking.html says:
> """
> ACCESS SHARE
>
>Conflicts with the ACCESS EXCLUSIVE lock mode only.
>
>The SELECT command acquires a lock of this mode on referenced
> tables. In general, any query that only reads a table and does not
> modify it will acquire this lock mode.
> """
> in other words, everything is ok, AccessShareLock doesn't block
> anything but with anyone trying to change the structure of the table
> (ALTER, DROP) and with commands TRUNCATE, REINDEX, CLUSTER, and VACUUM
> FULL, and every select take it
>
> --
> Atentamente,
> Jaime Casanova
> Soporte y capacitación de PostgreSQL
> Asesoría y desarrollo de sistemas
> Guayaquil - Ecuador
> Cel. +59387171157
>



-- 
Clayton Graf

Re: [GENERAL] AccessShareLock question

[Clayton Graf]

I think I got it...

I was just using

select * from table1;
select * from table2;
select * from tablen;

instead of

begin;
select * from table1;
select * from table2;
select * from tablen;
commit;

Using MS-SQLSERVER the begin trans is "implicit" at first update or delete
command. It is not necessary to "worry" about selects before the first
update or delete command. I got confused but I understand now. I guess :-)

Thank you,

Clayton


2009/12/19 Jaime Casanova 

> On Sat, Dec 19, 2009 at 10:58 AM, Clayton Graf 
> wrote:
> > Ok, but this is really my problem: I cannot perform an ALTER TABLE with
> the
> > system in production mode, because the ALTER TABLE hangs due an
> > AccessShareLock.
>
> until the lock is released, are your selects all that long?
> besides, why are you ALTERing the table in production... i guess
> clients will suffer if the expect less or more columns than the ones
> they receive from the ALTERed table
>
> > We use two-tier mode,
>
> don't understand this
>
> > so is it necessary to shutdown all users before
> > perform an ALTER TABLE?
>
> no
>
> --
> Atentamente,
> Jaime Casanova
> Soporte y capacitación de PostgreSQL
> Asesoría y desarrollo de sistemas
> Guayaquil - Ecuador
> Cel. +59387171157
>



-- 
Clayton Graf