Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

DBI mysql set transaction isolation level

by Anonymous Monk
on Feb 12, 2014 at 19:42 UTC ( #1074673=perlquestion: print w/ replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Greetings Monks!

I'm trying to set transaction isolation level in mysql and can't seem to get it to work. I've tried a bunch of permutations, different isolation levels, different places (directly after the connect), varying syntax and when I test for the level it shows it hasn't changed from the default (repeatable read). I googled and re-googled and saw about 20 results saying to do it the way I'm doing it, somewhere along the line found a result saying this couldn't be done in DBI. Is this true? Is the problem with the syntax, the DBI, my test, or the fact that it's Wed and it's gonna snow AGAIN! Any and all help appreciated. Thanks in advance.

my $result = $dbh->do("SET TRANSACTION ISOLATION LEVEL READ COMMITTED" +); print Dumper($result); my @results = $dbh->do("SELECT * FROM information_schema.global_variab +les WHERE variable_name = 'tx_isolation'"); print Dumper(@results);

Comment on DBI mysql set transaction isolation level
Download Code
Re: DBI mysql set transaction isolation level (trace)
by Anonymous Monk on Feb 12, 2014 at 19:58 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

        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.

        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.
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.

      Sorry, I pasted the wrong line, please see trace above reply

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>

    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.

      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!

        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

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';
      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.

        yes, all points taken!

        thanks much!

        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 !!!

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1074673]
Approved by boftx
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (10)
As of 2014-08-23 15:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (174 votes), past polls