Thread: SELECT Generating Row Exclusive Locks?
I'm monitoring locks using this query: SELECT pgsa.procpid, pgsa.current_query, pgsa.query_start, pgc.relname, pgl.mode, pgl.granted FROM pg_catalog.pg_class pgc, pg_locks AS pgl, pg_stat_activity AS pgsa WHERE pgl.pid = pgsa.procpid AND current_query <> '<IDLE>' AND pgl.relation = pgc.oid ORDER BY pgsa.query_start DESC; which was built as an extension of this information: http://archives.postgresql.org/pgsql-novice/2004-08/msg00291.php Interestingly, I'm seeing evidence that SELECTs are occasionally taking Row Exclusive locks. Should this surprise me? PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4 -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 (cell) 615-469-5150 (office) 615-469-5151 (fax)
On Nov 30, 2005, at 9:22 PM, Thomas F. O'Connell wrote: > I'm monitoring locks using this query: > > SELECT pgsa.procpid, pgsa.current_query, pgsa.query_start, > pgc.relname, pgl.mode, pgl.granted > FROM pg_catalog.pg_class pgc, pg_locks AS pgl, pg_stat_activity AS > pgsa > WHERE pgl.pid = pgsa.procpid > AND current_query <> '<IDLE>' > AND pgl.relation = pgc.oid > ORDER BY pgsa.query_start DESC; > > which was built as an extension of this information: > > http://archives.postgresql.org/pgsql-novice/2004-08/msg00291.php > > Interestingly, I'm seeing evidence that SELECTs are occasionally > taking Row Exclusive locks. Should this surprise me? > > PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4 Actually, let me clarify/rephrase my question. This query as written doesn't necessarily seem to help me distinguish clearly between queries that are waiting for a lock and those that are holding a lock. What I would expect to see during contentious periods in a given database would be a core of rows in pg_locks with granted = true and then a stable of additional rows with granted = false. For instance, if a long SELECT were running against table_foo and an UPDATE arrived wanting to update table_foo, I would expect to see in pg_locks an entry corresponding to the SELECT with granted = true and an entry corresponding to the UPDATE with granted = false. In reality, I often see hundreds of rows in pg_locks and am lucky ever to see granted = false among them. And in the rows that I do see, I occasionally see a SELECT corresponding to pg_stat_activity.current_query with the same pid as a granted Row Exclusive lock in pg_locks. I feel like I must be missing something in my interpretation, but I'm not sure what. -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 (cell) 615-469-5150 (office) 615-469-5151 (fax)
"Thomas F. O'Connell" <tfo@sitening.com> writes:
> For instance, if a long SELECT were running against table_foo and an
> UPDATE arrived wanting to update table_foo, I would expect to see in
> pg_locks an entry corresponding to the SELECT with granted = true and
> an entry corresponding to the UPDATE with granted = false.
Why would you expect to see that exactly? SELECTs don't block UPDATEs.
regards, tom lane
On Nov 30, 2005, at 10:52 PM, Tom Lane wrote: > "Thomas F. O'Connell" <tfo@sitening.com> writes: >> For instance, if a long SELECT were running against table_foo and an >> UPDATE arrived wanting to update table_foo, I would expect to see in >> pg_locks an entry corresponding to the SELECT with granted = true and >> an entry corresponding to the UPDATE with granted = false. > > Why would you expect to see that exactly? SELECTs don't block > UPDATEs. Mm. I must've been projecting my notion of a problem onto one that wasn't there, reading (and not thinking) Row Exclusive instead of Access Exclusive for conflicts. Duh. I guess I'm still somewhat puzzled by the original statement of the question, then. Why does that particular view of locks occasionally tie a SELECT to a granted Row Exclusive lock? I recognize that the pid in pg_locks can be the pid of the server process holding or awaiting the lock, but I'm seeing granted = true on these, which implies that the server process corresponding to the SELECT is holding a Row Exclusive, doesn't it? -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 (cell) 615-469-5150 (office) 615-469-5151 (fax)
"Thomas F. O'Connell" <tfo@sitening.com> writes:
> I guess I'm still somewhat puzzled by the original statement of the
> question, then. Why does that particular view of locks occasionally
> tie a SELECT to a granted Row Exclusive lock?
You sure it's not left over from an update command earlier in the
same transaction?
regards, tom lane
On Nov 30, 2005, at 11:24 PM, Tom Lane wrote: > "Thomas F. O'Connell" <tfo@sitening.com> writes: >> I guess I'm still somewhat puzzled by the original statement of the >> question, then. Why does that particular view of locks occasionally >> tie a SELECT to a granted Row Exclusive lock? > > You sure it's not left over from an update command earlier in the > same transaction? Pretty sure, unless the query I posted earlier can cause the display of leftover commands... -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 (cell) 615-469-5150 (office) 615-469-5151 (fax)