Thread: help with locked table(s)/transactions(s)
Recently we discovered that a stored procedure that we run locks some table(s) and prevents some SQL from running. We discovered this because doing a simple grep against postgres processes revealed several processes "WAITING": -- snip -- 00:04:31 postgres: dataman our_db 10.0.0.103 INSERT waiting 01:10:30 postgres: dataman our_db 10.0.0.103 UPDATE waiting 00:07:04 postgres: dataman our_db 10.0.0.103 UPDATE waiting 00:03:52 postgres: dataman our_db 10.0.0.103 INSERT waiting 00:04:30 postgres: dataman our_db 10.0.0.103 INSERT waiting 01:31:37 postgres: dataman our_db 10.0.0.103 SELECT 00:02:21 postgres: dataman our_db 10.0.0.103 INSERT waiting 00:02:58 postgres: dataman our_db 10.0.0.103 UPDATE waiting -- snip The SELECT statement listed is our stored procedure -- it takes about an hour and a half to run. I'm trying to figure out how to avoid the locking. I ran a query against the pg_locks, but am having some trouble dissecting it. Query I ran: select pg_stat_activity.datname, pg_class.relname, pg_locks.transaction, pg_locks.mode, pg_locks.granted, pg_stat_activity.usename, substr(pg_stat_activity.current_query,1,30), pg_stat_activity.procpid from pg_stat_activity,pg_locks left outer join pg_class on (pg_locks.relation = pg_class.oid) where pg_locks.pid=pg_stat_activity.procpid; One thing I noticed is it seems like for every transaction lock there's an "ExclusiveLock" (to be expected, a lock on the transaction num) as well as a "ShareLock" on the same transaction which has not been granted the lock -- does this mean that two processes are trying to share the same transaction? Could this be the case? I also see a couple "RowExclusiveLock"s related to the stored procedure that we're running. Am I on the right track here? Any suggestions would be very much appreciated as I'm still a little lost on what exactly is holding the lock(s). Thanks - Mott
Mott Leroy <mott@acadaca.com> writes:
> One thing I noticed is it seems like for every transaction lock there's
> an "ExclusiveLock" (to be expected, a lock on the transaction num) as
> well as a "ShareLock" on the same transaction which has not been granted
> the lock -- does this mean that two processes are trying to share the
> same transaction? Could this be the case?
No, the ShareLock is the means used when transaction A needs to wait for
transaction B to complete --- it tries to take a share lock on xact B's
XID, which of course is blocked as along as B is holding its
ExclusiveLock. This is used when there is a row-update conflict, ie,
A wants to lock or update a table row that B's already locked/updated.
Unfortunately, in 7.4 there isn't any easy way to find out exactly which
row transaction A is waiting on.
regards, tom lane
Tom Lane wrote: > No, the ShareLock is the means used when transaction A needs to wait for > transaction B to complete --- it tries to take a share lock on xact B's > XID, which of course is blocked as along as B is holding its > ExclusiveLock. This is used when there is a row-update conflict, ie, > A wants to lock or update a table row that B's already locked/updated. > Unfortunately, in 7.4 there isn't any easy way to find out exactly which > row transaction A is waiting on. My function has a loop in it defined as: FOR price_record IN select pd.price, sp.sku_id from sku_price as sp, price_data as pd where sp.price_data_id = pd.price_data_id AND pd.domain_id = LIVE LOOP -- some selects and inserts END LOOP; Will postgres lock on all rows as it goes through this loop? Or can you give me a better idea of what I can expect to be locked in my example? Thanks a lot - Mott
Mott Leroy <mott@acadaca.com> writes:
> Will postgres lock on all rows as it goes through this loop? Or can you
> give me a better idea of what I can expect to be locked in my example?
SELECTs don't lock any rows. INSERTs don't create any lockable rows
in themselves (other backends can't even see the rows yet). If that's
all that your transaction is doing, then I think the only explanation
is that the INSERTs are in a table that has foreign keys (correct?)
and that the row conflicts are on the referenced table. Before 8.1,
we take a row lock on the referenced row to ensure that it won't be
deleted before we can commit the referencing row. If you've got
other transactions that are actively modifying the referenced table,
then that's the source of the problem. Worse, you can get conflicts
just from inserting other referencing rows with the same key values.
The latter case is fixed in 8.1 by using a share instead of exclusive
row lock, but that mechanism isn't available in 7.4 ...
regards, tom lane
Tom Lane wrote: > SELECTs don't lock any rows. INSERTs don't create any lockable rows > in themselves (other backends can't even see the rows yet). If that's > all that your transaction is doing, then I think the only explanation > is that the INSERTs are in a table that has foreign keys (correct?) > and that the row conflicts are on the referenced table. Before 8.1, > we take a row lock on the referenced row to ensure that it won't be > deleted before we can commit the referencing row. If you've got > other transactions that are actively modifying the referenced table, > then that's the source of the problem. Worse, you can get conflicts > just from inserting other referencing rows with the same key values. > The latter case is fixed in 8.1 by using a share instead of exclusive > row lock, but that mechanism isn't available in 7.4 ... Actually, the transaction has UPDATEs as well, but yes, the INSERTS are on tables with foreign keys. I'm still confused by the behavior however. The locking behaves as if its some kind of *table* level lock, because while the function is executing (a long time), a dozen updates and inserts build up waiting for some lock to be released. If the loop just occassionally puts a lock on a few different rows, I don't see how that could cause the experienced behavior -- presumably the lock on those particular rows is released after its iteration through the loop. Unless, of course, the lock isn't given up ... ps - thanks for noting or recognizing that I'm using 7.4. forgot to mention that.
Mott Leroy <mott@acadaca.com> writes:
> I'm still confused by the behavior however. The locking behaves as if
> its some kind of *table* level lock, because while the function is
> executing (a long time), a dozen updates and inserts build up waiting
> for some lock to be released. If the loop just occassionally puts a lock
> on a few different rows, I don't see how that could cause the
> experienced behavior -- presumably the lock on those particular rows is
> released after its iteration through the loop. Unless, of course, the
> lock isn't given up ...
No, the locks would be held till end of transaction. It is a little odd
that you have so many conflicts, though, unless the referenced table is
pretty small and/or this loop manages to touch a large fraction of the
possible keys.
regards, tom lane
Tom Lane wrote: > No, the locks would be held till end of transaction. It is a little odd > that you have so many conflicts, though, unless the referenced table is > pretty small and/or this loop manages to touch a large fraction of the > possible keys. Ah, well, if the locks are held to the end of the transaction that would explain it I think. I mean, the loop touches *every* row (UPDATEs) in one of the tables. It sounds like I need to break up this function into a smaller function (by placing the loop outside the transaction).