Thread: AccessShareLock question
I get an AccessShareLock in a simple select command and I am not using the FOR SHARE clause.
Thanks,
Clayton
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
On Sat, Dec 19, 2009 at 9:04 AM, Clayton Graf <clayton.graf@gmail.com> 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
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.
--
Clayton Graf
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 <jcasanov@systemguards.com.ec>
On Sat, Dec 19, 2009 at 9:04 AM, Clayton Graf <clayton.graf@gmail.com> wrote:http://www.postgresql.org/docs/current/static/explicit-locking.html says:
> I get an AccessShareLock in a simple select command and I am not using the
> FOR SHARE clause.
"""
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
On Sat, Dec 19, 2009 at 10:58 AM, Clayton Graf <clayton.graf@gmail.com> 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
I think I got it...
--
Clayton Graf
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 <jcasanov@systemguards.com.ec>
On Sat, Dec 19, 2009 at 10:58 AM, Clayton Graf <clayton.graf@gmail.com> wrote:until the lock is released, are your selects all that long?
> 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.
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 tabledon't understand this
> We use two-tier mode,no
> so is it necessary to shutdown all users before
> perform an ALTER TABLE?
--Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
--
Clayton Graf
On Saturday 19 December 2009 12:45:15 pm Clayton Graf wrote: > 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 > > Now I am the one that is confused. In the first example the three SELECT statements are each a transaction. In the second case you have wrapped them in one transaction. In either case they are still acquiring an AccessShareLock. I thought the issue was with an ALTER TABLE statement not UPDATES/DELETES. -- Adrian Klaver aklaver@comcast.net