Re: DBI mysql set transaction isolation level

last test based on suggestions

$dbh->{AutoCommit} = 0;    # SET FOR TRANSACTION
my $result = $dbh->do("SET TRANSACTION ISOLATION LEVEL READ COMMITTED"
+);
print Dumper($result);
my @row_ary = $dbh->selectrow_array("SELECT * FROM information_schema.
+session_variables WHERE variable_name LIKE 'tx_%'");
print Dumper(@row_ary);


$VAR1 = '0E0';
$VAR1 = 'TX_ISOLATION';
$VAR2 = 'REPEATABLE-READ';
[download]

Comment on Re: DBI mysql set transaction isolation level Download Code

Replies are listed 'Best First'.
Re^2: DBI mysql set transaction isolation level by Anonymous Monk on Feb 13, 2014 at 16:56 UTC
Just shooting in the dark here, but from the MySQL docs: SET [GLOBAL \| SESSION] TRANSACTION ISOLATION LEVEL { REPEATABLE READ \| READ COMMITTED \| READ UNCOMMITTED \| SERIALIZABLE } Scope of the Isolation Level You can set the isolation level globally, for the current session, or for the next transaction: With the GLOBAL keyword, the statement sets the default transaction level globally for all subsequent sessions. Existing sessions are unaffected. With the SESSION keyword, the statement sets the default transaction level for all subsequent transactions performed within the current session. Without any SESSION or GLOBAL keyword, the statement sets the isolation level for the next (not started) transaction performed within the current session. The last bullet is interesting. So, first I'd test using one of those keywords, and checking whether the respective system table updates. The other thing I'd test is, try to do it in the MySQL command-line and check whether you get the results you expect. Does setting the isolation level show in session variables? What about with the SESSION keyword? What if you begin a transaction with `BEGIN`? What if you start a DBI transaction after mucking with the setting? If these suggestions don't work, I'd probably start a new thread summarising the situation; this thread is a bit of a mess and pretty deep in SoPW already.	[reply] [d/l]
Re^3: DBI mysql set transaction isolation level by Anonymous Monk on Feb 13, 2014 at 19:41 UTC
yes, all points taken! thanks much!	[reply]
Re^3: DBI mysql set transaction isolation level by Anonymous Monk on Feb 13, 2014 at 21:09 UTC
that was it!!! The difference between SET TRANSACTION ISOLATION LEVEL READ COMMITTED AND SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED AND as you pointed out in docs Without any SESSION or GLOBAL keyword, the statement sets the isolation level for the next (not started) transaction performed within the current session. Thanks all !!!	[reply]