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);
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.
| [reply] [d/l] |
|
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] |
|
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: 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] |
|
| [reply] |
Re: DBI mysql set transaction isolation level (trace)
by Anonymous Monk on Feb 12, 2014 at 19:58 UTC
|
| [reply] [d/l] |
|
| [reply] |
|
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.
| [reply] [d/l] |
|
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.
| [reply] [d/l] |
Re: DBI mysql set transaction isolation level
by Anonymous Monk on Feb 13, 2014 at 15:53 UTC
|
$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';
| [reply] [d/l] |
|
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] |
|
yes, all points taken!
thanks much!
| [reply] |
|
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] |
|
|