Thread: Hanging locks?
I have a situation regarding locks that nobody seems to own: using psql: Chipoteka=3D> select pg_class.relname,pg_locks.* from pg_class,pg_locks=20 where pg_class.relfilenode=3Dpg_locks.relation; relname | locktype | database | relation | page | tuple |=20 transactionid | classid | objid | objsubid | transaction | pid |=20=20=20= =20=20=20 mode | granted --------------+----------+----------+----------+------+-------+------------= ---+---------+-------+----------+-------------+------+-----------------+---= ------ valuta | relation | 366513 | 366657 | |=20=20=20=20=20=20= =20 | | | | | 4518231 | |=20 AccessShareLock | t jezik | relation | 366513 | 366567 | |=20=20=20=20=20=20= =20 | | | | | 4202257 | |=20 AccessShareLock | t jezik | relation | 366513 | 366567 | |=20=20=20=20=20=20= =20 | | | | | 4518231 | |=20 AccessShareLock | t serverconfig | relation | 366513 | 375491 | |=20=20=20=20=20=20= =20 | | | | | 4518231 | |=20 AccessShareLock | t mjesto | relation | 366513 | 366584 | |=20=20=20=20=20=20= =20 | | | | | 4518231 | |=20 AccessShareLock | t drzava | relation | 366513 | 366550 | |=20=20=20=20=20=20= =20 | | | | | 4518231 | |=20 AccessShareLock | t orgjed | relation | 366513 | 366596 | |=20=20=20=20=20=20= =20 | | | | | 4202257 | |=20 AccessShareLock | t serverconfig | relation | 366513 | 375491 | |=20=20=20=20=20=20= =20 | | | | | 4202257 | |=20 AccessShareLock | t serverconfig | relation | 366513 | 375491 | |=20=20=20=20=20=20= =20 | | | | | 4518229 | |=20 AccessShareLock | t drzava | relation | 366513 | 366550 | |=20=20=20=20=20=20= =20 | | | | | 4202257 | |=20 AccessShareLock | t valuta | relation | 366513 | 366657 | |=20=20=20=20=20=20= =20 | | | | | 4518229 | |=20 AccessShareLock | t jezik | relation | 366513 | 366567 | |=20=20=20=20=20=20= =20 | | | | | 4518229 | |=20 AccessShareLock | t valuta | relation | 366513 | 366657 | |=20=20=20=20=20=20= =20 | | | | | 4202257 | |=20 AccessShareLock | t pg_class | relation | 366513 | 1259 | |=20=20=20=20=20=20= =20 | | | | | 4658945 | 5709 |=20 AccessShareLock | t orgjed | relation | 366513 | 366596 | |=20=20=20=20=20=20= =20 | | | | | 4518231 | |=20 AccessShareLock | t pg_locks | relation | 366513 | 10342 | |=20=20=20=20=20=20= =20 | | | | | 4658945 | 5709 |=20 AccessShareLock | t drzava | relation | 366513 | 366550 | |=20=20=20=20=20=20= =20 | | | | | 4518229 | |=20 AccessShareLock | t mjesto | relation | 366513 | 366584 | |=20=20=20=20=20=20= =20 | | | | | 4202257 | |=20 AccessShareLock | t orgjed | relation | 366513 | 366596 | |=20=20=20=20=20=20= =20 | | | | | 4518229 | |=20 AccessShareLock | t mjesto | relation | 366513 | 366584 | |=20=20=20=20=20=20= =20 | | | | | 4518229 | |=20 AccessShareLock | t (20 rows) from the shell: kresot@kreso:~$ ps auxw|grep post postgres 1388 0.0 0.4 151980 6304 ? S 09:16 0:00=20 /usr/lib/postgresql/8.1/bin/postmaster -D /var/lib/postgresql/8.1/main=20 -c unix_socket_directory=3D/var/run/postgresql -c=20 config_file=3D/etc/postgresql/8.1/main/postgresql.conf -c=20 hba_file=3D/etc/postgresql/8.1/main/pg_hba.conf -c=20 ident_file=3D/etc/postgresql/8.1/main/pg_ident.conf -c=20 external_pid_file=3D/var/run/postgresql/8.1-main.pid postgres 1390 0.0 5.7 152128 89944 ? S 09:16 0:00=20 postgres: writer process=20=20 postgres 1391 0.0 0.1 10728 1884 ? S 09:16 0:00=20 postgres: stats buffer process=20=20 postgres 1392 0.0 0.0 10016 1424 ? S 09:16 0:00=20 postgres: stats collector process=20=20 kresot 5699 0.0 0.0 3224 636 pts/0 S+ 11:11 0:00 grep post The situation is the same after I restart postgres. My environment: postgres 8.1.4 on debian. Is this a bug, or am I doing something wrong? --=20 Kre=C5=A1imir Tonkovi=C4=87 Z-el d.o.o. Industrijska cesta 28, 10360 Sesvete, Croatia Tel: +385 1 2022 758 Fax: +385 1 2022 741 Web: www.chipoteka.hr e-mail: z-el.tonkovic@chipoteka.hr
Kresimir Tonkovic wrote: > I have a situation regarding locks that nobody seems to own: > > using psql: > > Chipoteka=3D> select pg_class.relname,pg_locks.* from pg_class,pg_locks= =20 > where pg_class.relfilenode=3Dpg_locks.relation; > relname | locktype | database | relation | page | tuple |=20 > transactionid | classid | objid | objsubid | transaction | pid |=20=20= =20=20=20=20 > mode | granted > --------------+----------+----------+----------+------+-------+----------= -----+---------+-------+----------+-------------+------+-----------------+-= --------=20 > > valuta | relation | 366513 | 366657 | |=20=20=20=20=20=20= =20 > | | | | | 4518231 | |=20 > AccessShareLock | t > jezik | relation | 366513 | 366567 | |=20=20=20=20=20=20= =20 > | | | | | 4202257 | |=20 > AccessShareLock | t > jezik | relation | 366513 | 366567 | |=20=20=20=20=20=20= =20 > | | | | | 4518231 | |=20 > AccessShareLock | t > serverconfig | relation | 366513 | 375491 | |=20=20=20=20=20=20= =20 > | | | | | 4518231 | |=20 > AccessShareLock | t > mjesto | relation | 366513 | 366584 | |=20=20=20=20=20=20= =20 > | | | | | 4518231 | |=20 > AccessShareLock | t > drzava | relation | 366513 | 366550 | |=20=20=20=20=20=20= =20 > | | | | | 4518231 | |=20 > AccessShareLock | t > orgjed | relation | 366513 | 366596 | |=20=20=20=20=20=20= =20 > | | | | | 4202257 | |=20 > AccessShareLock | t > serverconfig | relation | 366513 | 375491 | |=20=20=20=20=20=20= =20 > | | | | | 4202257 | |=20 > AccessShareLock | t > serverconfig | relation | 366513 | 375491 | |=20=20=20=20=20=20= =20 > | | | | | 4518229 | |=20 > AccessShareLock | t > drzava | relation | 366513 | 366550 | |=20=20=20=20=20=20= =20 > | | | | | 4202257 | |=20 > AccessShareLock | t > valuta | relation | 366513 | 366657 | |=20=20=20=20=20=20= =20 > | | | | | 4518229 | |=20 > AccessShareLock | t > jezik | relation | 366513 | 366567 | |=20=20=20=20=20=20= =20 > | | | | | 4518229 | |=20 > AccessShareLock | t > valuta | relation | 366513 | 366657 | |=20=20=20=20=20=20= =20 > | | | | | 4202257 | |=20 > AccessShareLock | t > pg_class | relation | 366513 | 1259 | |=20=20=20=20=20=20= =20 > | | | | | 4658945 | 5709 |=20 > AccessShareLock | t > orgjed | relation | 366513 | 366596 | |=20=20=20=20=20=20= =20 > | | | | | 4518231 | |=20 > AccessShareLock | t > pg_locks | relation | 366513 | 10342 | |=20=20=20=20=20=20= =20 > | | | | | 4658945 | 5709 |=20 > AccessShareLock | t > drzava | relation | 366513 | 366550 | |=20=20=20=20=20=20= =20 > | | | | | 4518229 | |=20 > AccessShareLock | t > mjesto | relation | 366513 | 366584 | |=20=20=20=20=20=20= =20 > | | | | | 4202257 | |=20 > AccessShareLock | t > orgjed | relation | 366513 | 366596 | |=20=20=20=20=20=20= =20 > | | | | | 4518229 | |=20 > AccessShareLock | t > mjesto | relation | 366513 | 366584 | |=20=20=20=20=20=20= =20 > | | | | | 4518229 | |=20 > AccessShareLock | t > (20 rows) > > from the shell: > > kresot@kreso:~$ ps auxw|grep post > postgres 1388 0.0 0.4 151980 6304 ? S 09:16 0:00=20 > /usr/lib/postgresql/8.1/bin/postmaster -D /var/lib/postgresql/8.1/main=20 > -c unix_socket_directory=3D/var/run/postgresql -c=20 > config_file=3D/etc/postgresql/8.1/main/postgresql.conf -c=20 > hba_file=3D/etc/postgresql/8.1/main/pg_hba.conf -c=20 > ident_file=3D/etc/postgresql/8.1/main/pg_ident.conf -c=20 > external_pid_file=3D/var/run/postgresql/8.1-main.pid > postgres 1390 0.0 5.7 152128 89944 ? S 09:16 0:00=20 > postgres: writer process postgres 1391 0.0 0.1 10728 1884=20 > ? S 09:16 0:00 postgres: stats buffer process postgres=20=20 > 1392 0.0 0.0 10016 1424 ? S 09:16 0:00 postgres: stats=20 > collector process kresot 5699 0.0 0.0 3224 636 pts/0 S+=20= =20=20 > 11:11 0:00 grep post > > The situation is the same after I restart postgres. > > My environment: postgres 8.1.4 on debian. > > Is this a bug, or am I doing something wrong? To clarify, This is a problem for me because I'm trying to drop this database, but=20 dropdb complains about other users using it. I suppose these locks are=20 what prevent dropdb from doing it's work. Best regards, --=20 Kre=C5=A1imir Tonkovi=C4=87 Z-el d.o.o. Industrijska cesta 28, 10360 Sesvete, Croatia Tel: +385 1 2022 758 Fax: +385 1 2022 741 Web: www.chipoteka.hr e-mail: z-el.tonkovic@chipoteka.hr
Kresimir Tonkovic <z-el.tonkovic@chipoteka.hr> writes:
> I have a situation regarding locks that nobody seems to own:
The only way pid can be null in a pg_locks entry is if the lock is held
by a prepared transaction. See pg_prepared_xacts view.
regards, tom lane
Tom Lane wrote:
> Kresimir Tonkovic <z-el.tonkovic@chipoteka.hr> writes:
>=20=20=20
>> I have a situation regarding locks that nobody seems to own:
>>=20=20=20=20=20
>
> The only way pid can be null in a pg_locks entry is if the lock is held
> by a prepared transaction. See pg_prepared_xacts view.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>
>=20=20=20
So i did
Chipoteka=3D> select * from pg_prepared_xacts;
transaction | gid | prepared=20=20=20=20=
=20=20=20=20=20=20=20=20
| owner | database
-------------+---------------------------+-------------------------------+-=
------+-----------
4202257 | 257_a3Jlc28vMTg4NDQ4_Mg=3D=3D | 2006-10-20 12:22:08.72175+02=
=20=20
| jboss | Chipoteka
4518229 | 257_a3Jlc28vNTY3MzE3_Mg=3D=3D | 2006-10-20 16:53:32.002687+0=
2=20
| jboss | Chipoteka
4518231 | 257_a3Jlc28vNTY3MzI2_Mg=3D=3D | 2006-10-20 16:53:32.036318+0=
2=20
| jboss | Chipoteka
and then:
Chipoteka=3D> commit prepared '257_a3Jlc28vMTg4NDQ4_Mg=3D=3D';
COMMIT PREPARED
Chipoteka=3D> commit prepared '257_a3Jlc28vNTY3MzE3_Mg=3D=3D';
COMMIT PREPARED
Chipoteka=3D> commit prepared '257_a3Jlc28vNTY3MzI2_Mg=3D=3D';
COMMIT PREPARED
and I'm free! :-)
Thanks!
--=20
Kre=C5=A1imir Tonkovi=C4=87
Z-el d.o.o.
Industrijska cesta 28, 10360 Sesvete, Croatia
Tel: +385 1 2022 758
Fax: +385 1 2022 741
Web: www.chipoteka.hr
e-mail: z-el.tonkovic@chipoteka.hr