PostgreSQL – How to Check if the Current Connection is in a Transaction

postgresqltransaction

I need my program code to ensure that certain part of logic is being executed within a transaction.

What query would tell me the current transaction ID / other information that would allow me to determine if I am in a transaction from within the transaction?

BEGIN;
-- How to check if I am in a transaction?
COMMIT;

Best Answer

A simple way is to compare now() to statement_timestamp().

now() gives the current date and time (start of current transaction).
statement_timestamp() gives the current date and time (start of current statement).

Example:

SELECT now() = statement_timestamp();
-- TRUE

BEGIN;
SELECT now() = statement_timestamp();
-- FALSE

The other alternative is to execute two queries:

SELECT txid_current();
SELECT txid_current();

and compare the resulting xid. If it is the same, then you are in a transaction.

The downside to the latter approach is that every txid_current() increments xid value and will further advance you to a wraparound.

Related Solutions

Sql-server – Half of a transaction completes, when the other half fails

@@ERROR is reset after every statement. The error from your attempt to alter the first procedure is no longer detectable via @@ERROR after you successfully altered the second procedure. Here is an even simpler repro:

SELECT 1/0;
GO
SELECT @@ERROR; -- 8134

However if I put a successful statement in between:

SELECT 1/0;
GO
SELECT 1/1;
GO
SELECT @@ERROR; -- 0

In your case, you are doing this:

ALTER dbo.p1 ... -- fails
GO
-- @@ERROR here would be <> 0, but you're not checking it here!

ALTER dbo.p2 ... -- succeeds
GO
-- You're checking @@ERROR here, but success is 0

The only way your current logic would roll everything back is if the last ALTER failed. You need to check for @@ERROR after every ALTER. Here is an example.

USE tempdb;
GO
CREATE TABLE #x(err INT);
GO

BEGIN TRANSACTION;
GO

CREATE PROCEDURE dbo.foo -- fails
AS 
  SELECT foo FROM sys.objects;
GO
IF @@ERROR <> 0 
  INSERT #x(err) SELECT 1;
GO

CREATE PROCEDURE dbo.blat -- succeeds, but will get rolled back
AS 
  SELECT 1;
GO
IF @@ERROR <> 0 
  INSERT #x(err) SELECT 1;
GO

IF EXISTS (SELECT 1 FROM #x)
  ROLLBACK TRANSACTION;
ELSE
  COMMIT TRANSACTION;

DROP TABLE #x;

Mysql – Do queries called from within a transaction but found within another method act the same

MySQL does not care about your PHP code structure. One transaction is what is between start and commit/rollback, sent over one connection - thats important, if your $this->db->createCommand()->execute/query creates a separate connection for some reason then it won't work but that should not be the usual case, just mentioning it because it sometimes happen so better to check.

You can have multiple transaction "open" using multiple connections but then you can deadlock yourself so beware (MySQL can automatically resolve deadlocks on the DB layer but not if you lock a record from one connection and then try to access it from some other, because your app is single-threaded (probably) and the deadlock detection expects parallel clients.

Best Answer

Related Solutions

Sql-server – Half of a transaction completes, when the other half fails

Mysql – Do queries called from within a transaction but found within another method act the same

Related Question