sql qqq gt cfml #lucee

Join Slack

sql qqq -->

# lucee

David Rogers

05/10/2023, 6:49 PM

sql qqq -->

David Rogers

05/10/2023, 6:50 PM

Copy code

transaction isolation="read_committed" {
  writedump(queryExecute("select @@TRANCOUNT")) // 0 ???
}

shouldn't transaction count / depth be 1 ?

David Rogers

05/10/2023, 6:50 PM

maybe there is something magical about implicit transactions

sknowlton

05/10/2023, 6:57 PM

There has to be an actual query there for CF to take its transaction and 'do something' with it. It won't BEGIN TRANSACTION until you actually query something, such that

Copy code

transaction isolation="read_committed" {
			queryExecute( "select top 1 * from users" );
			writeDump( queryExecute( "select @@TRANCOUNT" ) ) // 0 ???
		}

Will return 1

David Rogers

05/10/2023, 6:58 PM

you know I have to relearn that every week

zackster

05/10/2023, 7:04 PM

adds to mental modal

David Rogers

05/10/2023, 7:26 PM

"mental modal" is a humorous image, like a popup dialog in your brain. db connections are per thread yeah? e.g.

queryExecute("begin transaction")

means "every query on this thread is part of this transaction until explicit commit"? I guess I'd expect it would have to be like that, to support its own

transaction{...}

blocks, but curious if it's a guarantee.

David Rogers

05/11/2023, 12:20 PM

can the engine be convinced to temporarily increase transaction isolation level? the

repeatable_read

block wrapped in a

read_committed

block here doesn't seem to issue a

set transaction isolation level ...

statement. The last isolation level, lexically inside the

repeatable_read

block, is

read_committed

Copy code

forceStartTransaction = () => queryExecute("select top 1 * from users")
	dumpCurrentIsolation = () => writedump(queryExecute("
		SELECT CASE transaction_isolation_level
			WHEN 0 THEN 'Unspecified'
			WHEN 1 THEN 'ReadUncommitted'
			WHEN 2 THEN 'ReadCommitted'
			WHEN 3 THEN 'RepeatableRead'
			WHEN 4 THEN 'Serializable'
			WHEN 5 THEN 'Snapshot'
		END AS TransactionIsolationLevel
		FROM sys.dm_exec_sessions
		WHERE session_id = @@SPID;
	"))

	transaction isolation="read_committed" {
		forceStartTransaction()
		dumpCurrentIsolation(); // read_committed
	}

	transaction isolation="repeatable_read" {
		forceStartTransaction()
		dumpCurrentIsolation(); // read_committed
	}

	transaction isolation="read_committed" {
		forceStartTransaction()
		dumpCurrentIsolation(); // read_committed
		transaction isolation="repeatable_read" {
			forceStartTransaction();
			dumpCurrentIsolation(); // read_committed (?)
		}
	}

relevant sqlserver blurb:

With one exception, you can switch from one isolation level to another at any time during a transaction. The exception occurs when changing from any isolation level to SNAPSHOT isolation. Doing this causes the transaction to fail and roll back. However, you can change a transaction started in SNAPSHOT isolation to any other isolation level.

Open in Slack

Previous Next