PostgreSQL – Things Affected Globally in a Transaction

postgresqltransaction

Correct me if I am using the correct term: "affected globally" I mean, if I increase the sequence value, it will be increased for all other running transactions without the need for commit….

I know that sequences have this behavior, but my doubt is there is a list with some other roles that are affected globally, if i update it inside a transaction?

For example, if I set alter table mytable enable trigger all; this will affect the trigger behavior only in the local transaction or in other transactions, note that this is only a example, i am thinking about a lot of other things that i am need to be carefully about it.

Best Answer

alter table mytable enable trigger all; cannot affect concurrent transactions directly because the command takes an exclusive lock on the table before the change is applied. And the lock stays until the end of the transaction. (Of course, the lock itself can have indirect effects on concurrent transactions.)

Some effects that are visible to concurrent transactions before COMMIT (or even after ROLLBACK):

Locks, obviously (this is how other conflicts are avoided).
Sequences (like you already mentioned).
Entries in unique indexes (conflict with dupes even when still uncommitted).
Data written to log files (or anything else written to files).
Effects of dblink calls (emulating autonomous transactions, effectively).
Changes to prepared statements (only matters in case of a ROLLBACK, since prepared statements are only visible within the same session).
Long running transactions also can keep VACUUM from doing it's job, since rows still potentially visible in the snapshot cannot be removed. Can even lead to transaction ID wraparound in very extreme cases. (Well, other security measures kick in before that actually happens.)

I am sure there is more ...

Some commands cannot be executed inside a transaction at all. Like CREATE DATABASE, CREATE INDEX CONCURRENTLY or VACUUM.

Sql-server – Will a delete then insert within a transaction cascade

I'm only familiar with SQL Server:

Each operation is atomic. If you run a delete, and it cascades to other tables, those records are gone, too, as soon as the statement is over. They don't magically come back into existence unless the transaction is rolled back.

If you're relying on the ID values and don't want to cascade the related tables, consider switching to a merge strategy (where you use UPDATE or, preferably MERGE) instead of blowing everything away and starting from scratch.

I suppose you could try something like disabling the relationships during data loading, but given what I think you're trying to do, that seems fraught with peril. Definitely try a merge strategy first.

Sql-server – Transaction level difference between using a large IN filter VS. “BEGIN TRAN/COMMIT”

From a logging point of view 1 and 2 will be about the same as in both cases you are doing all the deletes within a single transaction. There will be lots of locking and probably blocking while the delete is being run. #3 is a single transaction per batch, so users won't be impacted very much and you'll have lots of small transactions in the transaction log instead of one large one. #2 and #3 should take about the same amount of time to run. #1 should take less time than #2 and #3 because it's just one command, however the run time will probably still cause problems.

I'd probably want to do something like this. This will minimize the locking and blocking that needs to happen by only dealing with 1000 rows at a time.

SELECT NULL
WHILE @@ROWCOUNT <> 0
BEGIN
  DELETE TOP (1000) FROM dbo.x
  WHERE ID IN ( /* if possible use "BETWEEN 1 and 100000" instead*/
  1
  ,2
  ,3
  ,4
  ...
  ,100000
  )
END

Best Answer

Related Solutions

Sql-server – Will a delete then insert within a transaction cascade

Sql-server – Transaction level difference between using a large IN filter VS. “BEGIN TRAN/COMMIT”

Related Question