Обсуждение: Trigger transaction isolation
Hello, I'd like to understand the visibility of data changes made by other transactions when executing SQL commands in a trigger function in READ COMMITTED isolation level. I could not find this covered in the trigger documentation (which already has some good sections about SQL command visibility for several trigger types), and I don't think it is practically possible to infer this from observations of the behaviour. So, if during an SQL command that triggers a trigger, another transaction commits, do the SQL commands in the trigger that start after that commit see the changes of the other transaction or do they see the state as it was when the triggering command was started? If they do see the changes, then I could implement a constraint check without race condition based on the contents of some other table using a lock on that table (say, to check for relation cycles, or, in the same table, to limit the number of records). If they don't see the changes, then I fear the race condition free check can only be implemented using the SERIALIZABLE isolation level, which I cannot really use in my situation for performance reasons and the retry overhead. I know that using a lock might lead to a deadlock, but I'd want to give it a try. Thank you very much. Dirk Lattermann
Hello! Since unfortunately nobody has yet replied to my question, I'd like to know if this is the right list to ask this question on or if I should try another mailing list. Maybe the answer is too obvious, but in that case I'd appreciate a short hint to help me finding it. Maybe it's a hard question, then the answer will be even more interesting...? Thanks again, Dirk Lattermann On Tue, 25 Aug 2020 11:12:35 +0200 Dirk Lattermann <dlatt@alqualonde.de> wrote: > Hello, > > I'd like to understand the visibility of data changes made by other > transactions when executing SQL commands in a trigger function in READ > COMMITTED isolation level. > I could not find this covered in the trigger documentation (which > already has some good sections about SQL command visibility for > several trigger types), and I don't think it is practically possible > to infer this from observations of the behaviour. > > So, if during an SQL command that triggers a trigger, another > transaction commits, do the SQL commands in the trigger that start > after that commit see the changes of the other transaction or do they > see the state as it was when the triggering command was > started? > > If they do see the changes, then I could implement a > constraint check without race condition based on the contents of some > other table using a lock on that table (say, to check for relation > cycles, or, in the same table, to limit the number of records). > If they don't see the changes, then I fear the race condition free > check can only be implemented using the SERIALIZABLE isolation level, > which I cannot really use in my situation for performance reasons and > the retry overhead. I know that using a lock might lead to a > deadlock, but I'd want to give it a try. > > Thank you very much. > Dirk Lattermann > >
On 9/1/20 7:07 AM, Dirk Lattermann wrote: > Hello! > > Since unfortunately nobody has yet replied to my question, I'd like to > know if this is the right list to ask this question on or if I should > try another mailing list. > Maybe the answer is too obvious, but in that case I'd appreciate a > short hint to help me finding it. > Maybe it's a hard question, then the answer will be even more > interesting...? As I understand it a trigger function runs in its own transaction so the rules from below apply: https://www.postgresql.org/docs/12/transaction-iso.html 13.2.1. Read Committed Isolation Level > > Thanks again, > Dirk Lattermann > > On Tue, 25 Aug 2020 11:12:35 +0200 > Dirk Lattermann <dlatt@alqualonde.de> wrote: > >> Hello, >> >> I'd like to understand the visibility of data changes made by other >> transactions when executing SQL commands in a trigger function in READ >> COMMITTED isolation level. >> I could not find this covered in the trigger documentation (which >> already has some good sections about SQL command visibility for >> several trigger types), and I don't think it is practically possible >> to infer this from observations of the behaviour. >> >> So, if during an SQL command that triggers a trigger, another >> transaction commits, do the SQL commands in the trigger that start >> after that commit see the changes of the other transaction or do they >> see the state as it was when the triggering command was >> started? >> >> If they do see the changes, then I could implement a >> constraint check without race condition based on the contents of some >> other table using a lock on that table (say, to check for relation >> cycles, or, in the same table, to limit the number of records). >> If they don't see the changes, then I fear the race condition free >> check can only be implemented using the SERIALIZABLE isolation level, >> which I cannot really use in my situation for performance reasons and >> the retry overhead. I know that using a lock might lead to a >> deadlock, but I'd want to give it a try. >> >> Thank you very much. >> Dirk Lattermann >> >> > > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes: > As I understand it a trigger function runs in its own transaction so the > rules from below apply: > https://www.postgresql.org/docs/12/transaction-iso.html No, a trigger is part of the calling transaction. There's nothing special about it other than the condition causing the call; visibility rules are the same as for any other function. So the answer to the OP's question depends on the transaction's isolation level and (for typical PLs) on whether the function is VOLATILE or not. serializable -> the transaction's initial snapshot is used throughout non serializable, volatile function -> each statement in the function takes a fresh snapshot non serializable, non-volatile function -> the function uses a single snapshot. For a non-deferred trigger, I think it shares the snapshot used by the triggering query. Not sure about exactly when the snapshot is taken for a deferred trigger. regards, tom lane
Thank you, Tom, for this valuable information. On Tue, 01 Sep 2020 11:02:01 -0400 1Tom Lane <tgl@sss.pgh.pa.us> wrote: > visibility rules are the same as for any other function. So the > answer to the OP's question depends on the transaction's isolation > level and (for typical PLs) on whether the function is VOLATILE or > not. > > serializable -> the transaction's initial snapshot is used throughout I suppose by writing serializable, you include repeatable read here, too? > > non serializable, volatile function -> each statement in the function > takes a fresh snapshot This is the needed behaviour for the validations I intend to do, then. > > non serializable, non-volatile function -> the function uses a single > snapshot. For a non-deferred trigger, I think it shares the snapshot > used by the triggering query. Not sure about exactly when the > snapshot is taken for a deferred trigger. So this case seems to be trickier than it looks at a first glance. It depends heavily on the use case, of course, in how far the snapshot time influences the correctness of a trigger written in this mode. I suggest adding this to the documentation and will try to use the commenting function there for it. Thanks! Dirk