DBI mysql set transaction isolation level

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: DBI mysql set transaction isolation level by moritz (Cardinal) on Feb 13, 2014 at 06:49 UTC
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 435 Server version: 5.5.35-0ubuntu0.12.04.2 (Ubuntu) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights res +erved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input st +atement. mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM information_schema.global_variables WHERE -> variable_name = 'tx_isolation'; +---------------+-----------------+ \| VARIABLE_NAME \| VARIABLE_VALUE \| +---------------+-----------------+ \| TX_ISOLATION \| REPEATABLE-READ \| +---------------+-----------------+ 1 row in set (0.01 sec) mysql> [download] Doesn't seem to be a DBI problem. If I remeber correctly, the SQL standard only specifies minimum isolation properties for the various isolation level; giving the user a level with more isolation than requested thus isn't a violation of the standard. Perl 6 - the future is here, just unevenly distributed	[reply] [d/l]
Re^2: DBI mysql set transaction isolation level by Anonymous Monk on Feb 13, 2014 at 11:46 UTC
I'm not seeing your point, as you posted output from the mysql shell, but are referring to the DBI? I was succesful in changing the isolation both in the shell and the GUI, but not in DBI. thanks!	[reply]
Re^3: DBI mysql set transaction isolation level by McA (Priest) on Feb 13, 2014 at 12:44 UTC
Hi, I have to admit, that I can see the point of Moritz' answer. When he fires the statements without using DBI he gets the same result which you don't expect and think of being the fault of DBI. At least there is a case where you get the same - in your view "wrong" result - without using DBI. This is a hint that the problem is not DBI related, isn't it? IMHO a valueable hint. Now my hint: There are many variables in MySQL which have a session scope or a global scope. So, without digging deeper I think you change the session transaction level but ask the schema for the global variable. Best regards McA	[reply]
Re^4: DBI mysql set transaction isolation level by Anonymous Monk on Feb 13, 2014 at 14:22 UTC
Re^5: DBI mysql set transaction isolation level by Anonymous Monk on Feb 13, 2014 at 15:42 UTC
Re: DBI mysql set transaction isolation level by Anonymous Monk on Feb 12, 2014 at 21:24 UTC
`$dbh->do()` isn't meant for SELECTs and will not return rows. Use `$dbh->selectall_arrayref()` or the other similarly-named functions.	[reply] [d/l] [select]
Re^2: DBI mysql set transaction isolation level by Anonymous Monk on Feb 13, 2014 at 11:42 UTC
Sorry, I pasted the wrong line, please see trace above reply	[reply]
Re: DBI mysql set transaction isolation level (trace) by Anonymous Monk on Feb 12, 2014 at 19:58 UTC
What is supposed to happen? get more info `DBI->trace(999)` and https://dev.mysql.com/doc/refman/5.0/en/set-transaction.html	[reply] [d/l]
Re^2: DBI mysql set transaction isolation level (trace) by Anonymous Monk on Feb 12, 2014 at 20:34 UTC
Thanks for the timely response! I'll run the trace tommorrow (leaving now), but what is supposed to happen is the isolation should change from repeatable read (default) to read committed	[reply]
Re^3: DBI mysql set transaction isolation level (trace) by Anonymous Monk on Feb 13, 2014 at 11:39 UTC
I ran the trace please see below</p? DBI 1.631-ithread default trace level set to 0x300/7 (pid 4275 pi +100800000) at sel_ftp_files.pl line 82 via sel_ftp_files.pl line 55 -> do for DBD::mysql::db (DBI::db=HASH(0x100f392d8)~0x100f39188 'S +ET TRANSACTION ISOLATION LEVEL READ COMMITTED') thr#100800000 mysql.xs do() use_server_side_prepare 0, async 0 mysql_st_internal_execute MYSQL_VERSION_ID 50528 >parse_params statement SET TRANSACTION ISOLATION LEVEL READ COMMITTED <- do= '0E0' at ./sel_ftp_files.pl line 86 via at ./sel_ftp_files +.pl line 55 -> commit for DBD::mysql::db (DBI::db=HASH(0x100f392d8)~0x100f3918 +8) thr#100800000 <- commit= 1 at ./sel_ftp_files.pl line 88 via at ./sel_ftp_files +.pl line 55 $VAR1 = '0E0'; -> selectrow_array for DBD::mysql::db (DBI::db=HASH(0x100f392d8)~0 +x100f39188 'SELECT * FROM information_schema.global_variables WHERE v +ariable_name = 'tx_isolation'') thr#100800000 1 -> prepare for DBD::mysql::db (DBI::db=HASH(0x100f39188)~INNER 'SE +LECT * FROM information_schema.global_variables WHERE variable_name = + 'tx_isolation'' undef) thr#100800000 2 -> _async_check for DBD::mysql::db (DBI::db=HASH(0x100f39188)~INNE +R) thr#100800000 2 <- _async_check= 1 at /Library/Perl/5.10.0/darwin-thread-multi-2le +vel/DBD/mysql.pm line 227 via at ./sel_ftp_files.pl line 92 New 'DBI::st' (for DBD::mysql::st, parent=DBI::db=HASH(0x100f39188 +), id=undef) dbih_setup_handle(DBI::st=HASH(0x100f3a698)=>DBI::st=HASH(0x100f3a +4d0), DBD::mysql::st, 100f3c128, Null!) dbih_make_com(DBI::db=HASH(0x100f39188), 10221b9a0, DBD::mysql::st +, 448, 0) thr#100800000 dbih_setup_attrib(DBI::st=HASH(0x100f3a4d0), Err, DBI::db=HASH(0x1 +00f39188)) SCALAR(0x10088dba8) (already defined) dbih_setup_attrib(DBI::st=HASH(0x100f3a4d0), State, DBI::db=HASH(0 +x100f39188)) SCALAR(0x10088dc68) (already defined) dbih_setup_attrib(DBI::st=HASH(0x100f3a4d0), Errstr, DBI::db=HASH( +0x100f39188)) SCALAR(0x10088dc08) (already defined) dbih_setup_attrib(DBI::st=HASH(0x100f3a4d0), TraceLevel, DBI::db=H +ASH(0x100f39188)) 0 (already defined) dbih_setup_attrib(DBI::st=HASH(0x100f3a4d0), FetchHashKeyName, DBI +::db=HASH(0x100f39188)) 'NAME' (already defined) dbih_setup_attrib(DBI::st=HASH(0x100f3a4d0), HandleSetErr, DBI::db +=HASH(0x100f39188)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0x100f3a4d0), HandleError, DBI::db= +HASH(0x100f39188)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0x100f3a4d0), ReadOnly, DBI::db=HAS +H(0x100f39188)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0x100f3a4d0), Profile, DBI::db=HASH +(0x100f39188)) undef (not defined) -> dbd_st_prepare MYSQL_VERSION_ID 50528, SQL statement: SELECT * +FROM information_schema.global_variables WHERE variable_name = 'tx_is +olation' >- dbd_st_free_result_sets <- dbd_st_free_result_sets RC -1 <- dbd_st_free_result_sets >count_params statement SELECT * FROM information_schema.global_variab +les WHERE variable_name = 'tx_isolation' <- dbd_st_prepare 1 <- prepare= DBI::st=HASH(0x100f3a698) at ./sel_ftp_files.pl line 9 +2 via at ./sel_ftp_files.pl line 55 -> dbd_st_execute for 100f6c5c0 >- dbd_st_free_result_sets <- dbd_st_free_result_sets RC -1 <- dbd_st_free_result_sets mysql_st_internal_execute MYSQL_VERSION_ID 50528 >parse_params statement SELECT * FROM information_schema.global_variab +les WHERE variable_name = 'tx_isolation' <- dbd_st_execute returning imp_sth->row_num 1 -> dbd_st_fetch dbd_st_fetch for 100f6c5c0, chopblanks 0 dbd_st_fetch result set details imp_sth->result=1004d6030 mysql_num_fields=2 mysql_num_rows=1 mysql_affected_rows=1 dbd_st_fetch for 100f6c5c0, currow= 1 dbih_setup_fbav alloc for 2 fields dbih_setup_fbav now 2 fields <- dbd_st_fetch, 2 cols --> dbd_st_finish >- dbd_st_free_result_sets <- dbd_st_free_result_sets RC -1 <- dbd_st_free_result_sets <-- dbd_st_finish <- selectrow_array= ( 'TX_ISOLATION' 'REPEATABLE-READ' ) [2 items] + at ./sel_ftp_files.pl line 92 via at ./sel_ftp_files.pl line 55 <> DESTROY(DBI::st=HASH(0x100f3a698)) ignored for outer handle (in +ner DBI::st=HASH(0x100f3a4d0) has ref cnt 1) -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x100f3a4d0)~INNER) th +r#100800000 <- DESTROY= undef at ./sel_ftp_files.pl line 93 via at ./sel_ftp_ +files.pl line 93 DESTROY (dbih_clearcom) (sth 0x100f3a4d0, com 0x10221ce00, imp DBD +::mysql::st): FLAGS 0x100191: COMSET Warn RaiseError PrintError PrintWarn PARENT DBI::db=HASH(0x100f39188) KIDS 0 (0 Active) NUM_OF_FIELDS 2 NUM_OF_PARAMS 0 dbih_clearcom 0x100f3a4d0 (com 0x10221ce00, type 3) done. -> commit for DBD::mysql::db (DBI::db=HASH(0x100f392d8)~0x100f3918 +8) thr#100800000 <- commit= 1 at ./sel_ftp_files.pl line 93 via at ./sel_ftp_files +.pl line 55 $VAR1 = 'TX_ISOLATION'; $VAR2 = 'REPEATABLE-READ'; ### TESTING REMOVE FOR PRODUCTION -- DBI::END ($@: , $!: ) -> disconnect_all for DBD::mysql::dr (DBI::dr=HASH(0x100f3e128)~0x +100f38848) thr#100800000 <- disconnect_all= (not implemented) at /Library/Perl/5.10.0/darwi +n-thread-multi-2level/DBI.pm line 750 via at ./sel_ftp_files.pl line + 100 ! <> DESTROY(DBI::db=HASH(0x100f392d8)) ignored for outer handle (in +ner DBI::db=HASH(0x100f39188) has ref cnt 1) ! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x100f39188)~INNER) th +r#100800000 imp_dbh->pmysql: 102811600 ! <- DESTROY= undef during global destruction DESTROY (dbih_clearcom) (dbh 0x100f39188, com 0x10221b9a0, imp glo +bal destruction): FLAGS 0x100191: COMSET Warn RaiseError PrintError PrintWarn PARENT DBI::dr=HASH(0x100f38848) KIDS 0 (0 Active) IMP_DATA HASH(0x100f39200) dbih_clearcom 0x100f39188 (com 0x10221b9a0, type 2) done. ! <> DESTROY(DBI::dr=HASH(0x100f3e128)) ignored for outer handle (in +ner DBI::dr=HASH(0x100f38848) has ref cnt 1) ! -> DESTROY in DBD::_::common for DBD::mysql::dr (DBI::dr=HASH(0x10 +0f38848)~INNER) thr#100800000 ! <- DESTROY= undef during global destruction DESTROY (dbih_clearcom) (drh 0x100f38848, com 0x1020c4dc0, imp glo +bal destruction): FLAGS 0x180215: COMSET Active Warn PrintWarn AutoCommit PARENT undef KIDS 1 (1 Active) dbih_clearcom 0x100f38848 (com 0x1020c4dc0, type 1) done. [download]	[reply] [d/l]
Re^3: DBI mysql set transaction isolation level (trace) by Anonymous Monk on Feb 13, 2014 at 11:40 UTC
I ran the trace please see below DBI 1.631-ithread default trace level set to 0x300/7 (pid 4275 pi +100800000) at sel_ftp_files.pl line 82 via sel_ftp_files.pl line 55 -> do for DBD::mysql::db (DBI::db=HASH(0x100f392d8)~0x100f39188 'S +ET TRANSACTION ISOLATION LEVEL READ COMMITTED') thr#100800000 mysql.xs do() use_server_side_prepare 0, async 0 mysql_st_internal_execute MYSQL_VERSION_ID 50528 >parse_params statement SET TRANSACTION ISOLATION LEVEL READ COMMITTED <- do= '0E0' at ./sel_ftp_files.pl line 86 via at ./sel_ftp_files +.pl line 55 -> commit for DBD::mysql::db (DBI::db=HASH(0x100f392d8)~0x100f3918 +8) thr#100800000 <- commit= 1 at ./sel_ftp_files.pl line 88 via at ./sel_ftp_files +.pl line 55 $VAR1 = '0E0'; -> selectrow_array for DBD::mysql::db (DBI::db=HASH(0x100f392d8)~0 +x100f39188 'SELECT * FROM information_schema.global_variables WHERE v +ariable_name = 'tx_isolation'') thr#100800000 1 -> prepare for DBD::mysql::db (DBI::db=HASH(0x100f39188)~INNER 'SE +LECT * FROM information_schema.global_variables WHERE variable_name = + 'tx_isolation'' undef) thr#100800000 2 -> _async_check for DBD::mysql::db (DBI::db=HASH(0x100f39188)~INNE +R) thr#100800000 2 <- _async_check= 1 at /Library/Perl/5.10.0/darwin-thread-multi-2le +vel/DBD/mysql.pm line 227 via at ./sel_ftp_files.pl line 92 New 'DBI::st' (for DBD::mysql::st, parent=DBI::db=HASH(0x100f39188 +), id=undef) dbih_setup_handle(DBI::st=HASH(0x100f3a698)=>DBI::st=HASH(0x100f3a +4d0), DBD::mysql::st, 100f3c128, Null!) dbih_make_com(DBI::db=HASH(0x100f39188), 10221b9a0, DBD::mysql::st +, 448, 0) thr#100800000 dbih_setup_attrib(DBI::st=HASH(0x100f3a4d0), Err, DBI::db=HASH(0x1 +00f39188)) SCALAR(0x10088dba8) (already defined) dbih_setup_attrib(DBI::st=HASH(0x100f3a4d0), State, DBI::db=HASH(0 +x100f39188)) SCALAR(0x10088dc68) (already defined) dbih_setup_attrib(DBI::st=HASH(0x100f3a4d0), Errstr, DBI::db=HASH( +0x100f39188)) SCALAR(0x10088dc08) (already defined) dbih_setup_attrib(DBI::st=HASH(0x100f3a4d0), TraceLevel, DBI::db=H +ASH(0x100f39188)) 0 (already defined) dbih_setup_attrib(DBI::st=HASH(0x100f3a4d0), FetchHashKeyName, DBI +::db=HASH(0x100f39188)) 'NAME' (already defined) dbih_setup_attrib(DBI::st=HASH(0x100f3a4d0), HandleSetErr, DBI::db +=HASH(0x100f39188)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0x100f3a4d0), HandleError, DBI::db= +HASH(0x100f39188)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0x100f3a4d0), ReadOnly, DBI::db=HAS +H(0x100f39188)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0x100f3a4d0), Profile, DBI::db=HASH +(0x100f39188)) undef (not defined) -> dbd_st_prepare MYSQL_VERSION_ID 50528, SQL statement: SELECT * +FROM information_schema.global_variables WHERE variable_name = 'tx_is +olation' >- dbd_st_free_result_sets <- dbd_st_free_result_sets RC -1 <- dbd_st_free_result_sets >count_params statement SELECT * FROM information_schema.global_variab +les WHERE variable_name = 'tx_isolation' <- dbd_st_prepare 1 <- prepare= DBI::st=HASH(0x100f3a698) at ./sel_ftp_files.pl line 9 +2 via at ./sel_ftp_files.pl line 55 -> dbd_st_execute for 100f6c5c0 >- dbd_st_free_result_sets <- dbd_st_free_result_sets RC -1 <- dbd_st_free_result_sets mysql_st_internal_execute MYSQL_VERSION_ID 50528 >parse_params statement SELECT * FROM information_schema.global_variab +les WHERE variable_name = 'tx_isolation' <- dbd_st_execute returning imp_sth->row_num 1 -> dbd_st_fetch dbd_st_fetch for 100f6c5c0, chopblanks 0 dbd_st_fetch result set details imp_sth->result=1004d6030 mysql_num_fields=2 mysql_num_rows=1 mysql_affected_rows=1 dbd_st_fetch for 100f6c5c0, currow= 1 dbih_setup_fbav alloc for 2 fields dbih_setup_fbav now 2 fields <- dbd_st_fetch, 2 cols --> dbd_st_finish >- dbd_st_free_result_sets <- dbd_st_free_result_sets RC -1 <- dbd_st_free_result_sets <-- dbd_st_finish <- selectrow_array= ( 'TX_ISOLATION' 'REPEATABLE-READ' ) [2 items] + at ./sel_ftp_files.pl line 92 via at ./sel_ftp_files.pl line 55 <> DESTROY(DBI::st=HASH(0x100f3a698)) ignored for outer handle (in +ner DBI::st=HASH(0x100f3a4d0) has ref cnt 1) -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x100f3a4d0)~INNER) th +r#100800000 <- DESTROY= undef at ./sel_ftp_files.pl line 93 via at ./sel_ftp_ +files.pl line 93 DESTROY (dbih_clearcom) (sth 0x100f3a4d0, com 0x10221ce00, imp DBD +::mysql::st): FLAGS 0x100191: COMSET Warn RaiseError PrintError PrintWarn PARENT DBI::db=HASH(0x100f39188) KIDS 0 (0 Active) NUM_OF_FIELDS 2 NUM_OF_PARAMS 0 dbih_clearcom 0x100f3a4d0 (com 0x10221ce00, type 3) done. -> commit for DBD::mysql::db (DBI::db=HASH(0x100f392d8)~0x100f3918 +8) thr#100800000 <- commit= 1 at ./sel_ftp_files.pl line 93 via at ./sel_ftp_files +.pl line 55 $VAR1 = 'TX_ISOLATION'; $VAR2 = 'REPEATABLE-READ'; ### TESTING REMOVE FOR PRODUCTION -- DBI::END ($@: , $!: ) -> disconnect_all for DBD::mysql::dr (DBI::dr=HASH(0x100f3e128)~0x +100f38848) thr#100800000 <- disconnect_all= (not implemented) at /Library/Perl/5.10.0/darwi +n-thread-multi-2level/DBI.pm line 750 via at ./sel_ftp_files.pl line + 100 ! <> DESTROY(DBI::db=HASH(0x100f392d8)) ignored for outer handle (in +ner DBI::db=HASH(0x100f39188) has ref cnt 1) ! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x100f39188)~INNER) th +r#100800000 imp_dbh->pmysql: 102811600 ! <- DESTROY= undef during global destruction DESTROY (dbih_clearcom) (dbh 0x100f39188, com 0x10221b9a0, imp glo +bal destruction): FLAGS 0x100191: COMSET Warn RaiseError PrintError PrintWarn PARENT DBI::dr=HASH(0x100f38848) KIDS 0 (0 Active) IMP_DATA HASH(0x100f39200) dbih_clearcom 0x100f39188 (com 0x10221b9a0, type 2) done. ! <> DESTROY(DBI::dr=HASH(0x100f3e128)) ignored for outer handle (in +ner DBI::dr=HASH(0x100f38848) has ref cnt 1) ! -> DESTROY in DBD::_::common for DBD::mysql::dr (DBI::dr=HASH(0x10 +0f38848)~INNER) thr#100800000 ! <- DESTROY= undef during global destruction DESTROY (dbih_clearcom) (drh 0x100f38848, com 0x1020c4dc0, imp glo +bal destruction): FLAGS 0x180215: COMSET Active Warn PrintWarn AutoCommit PARENT undef KIDS 1 (1 Active) dbih_clearcom 0x100f38848 (com 0x1020c4dc0, type 1) done. [download]	[reply] [d/l]
Re: DBI mysql set transaction isolation level by Anonymous Monk on Feb 13, 2014 at 15:53 UTC
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]	[reply] [d/l]
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]


P is for Practical
	PerlMonks