Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
No, with the default transaction isolation level, REPEATABLE READ, that's how it is supposed to work. You've started a transaction in Window B, so Window B is immune to changes made in Window A until you finish the transaction in Window B. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html; I haven't explicitly started any transactions in Window B - it's select-only (with autocommit set to 0). Are you saying that even though transactions have happend and been committed in Window A, I won't be able to see those transactions in Window B? The relevant part of the documentation in the link you sent is, The query see[s] the changes made by exactly those transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by the transaction itself that issues the query. In otherwords, if you start a query (and it's a long running query), you won't see the results of any data committed by another session during the running of that query. Fine. That's expected. But if I am doing only queries (no transactions) via a connection, and no query is running when I commit data in another session, then the query-window should see the results of those changes. I suspect that the mysql client is implicitly starting a transaction when you do a set autocommit=0. Thus, any changes made by any other sessions won't be visible till you do a commit or rollback. Each time a commit or rollback is issued in the non-auto-commit session, you can see data changed by other sessions. Regardless, this is not a repeatable-read issue. I think it's a mysql client issue, and the fact that the client is creating transactions for you in the background. This is not how the Oracle client works - you are always in non-auto-commit mode (and I'd love to figure out how to set that - autocommit is so dangerous), and until you actually start a transaction with an update, insert, delete or select-for-update, no transaction is started, and you can see the changes made by other sessions once they've been committed (I tested SQL*Plus on Oracle 8i to make sure). David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
David Griffiths wrote: No, with the default transaction isolation level, REPEATABLE READ, that's how it is supposed to work. You've started a transaction in Window B, so Window B is immune to changes made in Window A until you finish the transaction in Window B. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html; I haven't explicitly started any transactions in Window B - it's select-only (with autocommit set to 0). Are you saying that even though transactions have happend and been committed in Window A, I won't be able to see those transactions in Window B? The key word is explicitly. You have implicitly started a transaction with your first SELECT, precisely because you turned AUTOCOMMIT off. That transaction continues until you COMMIT or ROLLBACK (or perform an action that implicitly commits http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or-rollback.html). That's the point of setting AUTOCOMMIT to off. If you only want to start transactions explicitly (with START TRANSACTION or BEGIN), then you need to leave AUTOCOMMIT on. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-and-autocommit.html. The relevant part of the documentation in the link you sent is, The query see[s] the changes made by exactly those transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by the transaction itself that issues the query. In otherwords, if you start a query (and it's a long running query), you won't see the results of any data committed by another session during the running of that query. Fine. That's expected. But if I am doing only queries (no transactions) via a connection, and no query is running when I commit data in another session, then the query-window should see the results of those changes. From the AUTOCOMMIT manual page cited above, In InnoDB, all user activity occurs inside a transaction. I suspect that the mysql client is implicitly starting a transaction when you do a set autocommit=0. Thus, any changes made by any other sessions won't be visible till you do a commit or rollback. Each time a commit or rollback is issued in the non-auto-commit session, you can see data changed by other sessions. With AUTOCOMMIT off, the transaction starts, in your case, with your first SELECT. Regardless, this is not a repeatable-read issue. I think it's a mysql client issue, and the fact that the client is creating transactions for you in the background. It's not the client. That's how InnoDB works. This is not how the Oracle client works - you are always in non-auto-commit mode (and I'd love to figure out how to set that - autocommit is so dangerous), and until you actually start a transaction with an update, insert, delete or select-for-update, no transaction is started, and you can see the changes made by other sessions once they've been committed (I tested SQL*Plus on Oracle 8i to make sure). I'll make no comments on how Oracle works, but what you seem to be describing is effectively what happens with AUTOCOMMIT on in MySQL. In general, I'd suggest that expecting any two RDBMSs (MySQL and Oracle, for example) to behave in exactly the same way will usually get you in trouble. David Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
I believe you - I'm just a but surprised. I guess I had a singular view of how a session should work based on Oracle. I would have expected that until you execute SQL that requires a commit or a rollback, you wouldn't be in a transaction. Unfortunately, if you have connections that are read and write, and one connection ends up being used for SELECTs only (just bad luck) , it's going to have an out-date view of the database. To me, a transaction is something you commit or rollback. You can't commit or rollback a SELECT unless you've done a locking-read. I guess Oracle is just smarter about it, only starting a transaction behind the scenes if you've actually done something that warrants a transaction. David Michael Stassen wrote: David Griffiths wrote: No, with the default transaction isolation level, REPEATABLE READ, that's how it is supposed to work. You've started a transaction in Window B, so Window B is immune to changes made in Window A until you finish the transaction in Window B. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html; I haven't explicitly started any transactions in Window B - it's select-only (with autocommit set to 0). Are you saying that even though transactions have happend and been committed in Window A, I won't be able to see those transactions in Window B? The key word is explicitly. You have implicitly started a transaction with your first SELECT, precisely because you turned AUTOCOMMIT off. That transaction continues until you COMMIT or ROLLBACK (or perform an action that implicitly commits http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or-rollback.html). That's the point of setting AUTOCOMMIT to off. If you only want to start transactions explicitly (with START TRANSACTION or BEGIN), then you need to leave AUTOCOMMIT on. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-and-autocommit.html. The relevant part of the documentation in the link you sent is, The query see[s] the changes made by exactly those transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by the transaction itself that issues the query. In otherwords, if you start a query (and it's a long running query), you won't see the results of any data committed by another session during the running of that query. Fine. That's expected. But if I am doing only queries (no transactions) via a connection, and no query is running when I commit data in another session, then the query-window should see the results of those changes. From the AUTOCOMMIT manual page cited above, In InnoDB, all user activity occurs inside a transaction. I suspect that the mysql client is implicitly starting a transaction when you do a set autocommit=0. Thus, any changes made by any other sessions won't be visible till you do a commit or rollback. Each time a commit or rollback is issued in the non-auto-commit session, you can see data changed by other sessions. With AUTOCOMMIT off, the transaction starts, in your case, with your first SELECT. Regardless, this is not a repeatable-read issue. I think it's a mysql client issue, and the fact that the client is creating transactions for you in the background. It's not the client. That's how InnoDB works. This is not how the Oracle client works - you are always in non-auto-commit mode (and I'd love to figure out how to set that - autocommit is so dangerous), and until you actually start a transaction with an update, insert, delete or select-for-update, no transaction is started, and you can see the changes made by other sessions once they've been committed (I tested SQL*Plus on Oracle 8i to make sure). I'll make no comments on how Oracle works, but what you seem to be describing is effectively what happens with AUTOCOMMIT on in MySQL. In general, I'd suggest that expecting any two RDBMSs (MySQL and Oracle, for example) to behave in exactly the same way will usually get you in trouble. David Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
If you are NOT in autocommit mode, your connection (or the server, it doesn't matter which) starts a transaction *when you issue your first command*. Every command you issue on that connection is in that initial transaction until you EXPLICITLY commit or rollback (or do something else that commits or rolls-back your transactions like ALTER TABLE) . At that point a new transaction is automatically started when you issue your next command. If I remember correctly, closing a connection with a pending transaction defaults to a ROLLBACK. That way if a transaction is left incomplete due to communications failure, you maintain a consistent database. If autocommit is enabled (SET autocommit=1) then each command executes within it's own mini-transaction (one little, tight transaction wrapped around each statement). Each SELECT can see what every other INSERT, UPDATE, or DELETE has done (assuming their transactions are committed) because it is not already inside a pending transaction. This is the default mode for user interaction for nearly every database product I have used. With autocommit active, you are required to explicitly issue a START TRANSACTION if you want a transaction that includes several commands. Are you sure that's not how Oracle operates, too? I ask because MS SQL acts the same as MySQL when it comes to autocommits Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Griffiths [EMAIL PROTECTED] wrote on 09/01/2005 12:33:55 PM: I believe you - I'm just a but surprised. I guess I had a singular view of how a session should work based on Oracle. I would have expected that until you execute SQL that requires a commit or a rollback, you wouldn't be in a transaction. Unfortunately, if you have connections that are read and write, and one connection ends up being used for SELECTs only (just bad luck) , it's going to have an out-date view of the database. To me, a transaction is something you commit or rollback. You can't commit or rollback a SELECT unless you've done a locking-read. I guess Oracle is just smarter about it, only starting a transaction behind the scenes if you've actually done something that warrants a transaction. David Michael Stassen wrote: David Griffiths wrote: No, with the default transaction isolation level, REPEATABLE READ, that's how it is supposed to work. You've started a transaction in Window B, so Window B is immune to changes made in Window A until you finish the transaction in Window B. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html; I haven't explicitly started any transactions in Window B - it's select-only (with autocommit set to 0). Are you saying that even though transactions have happend and been committed in Window A, I won't be able to see those transactions in Window B? The key word is explicitly. You have implicitly started a transaction with your first SELECT, precisely because you turned AUTOCOMMIT off. That transaction continues until you COMMIT or ROLLBACK (or perform an action that implicitly commits http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or- rollback.html). That's the point of setting AUTOCOMMIT to off. If you only want to start transactions explicitly (with START TRANSACTION or BEGIN), then you need to leave AUTOCOMMIT on. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-and-autocommit.html. The relevant part of the documentation in the link you sent is, The query see[s] the changes made by exactly those transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by the transaction itself that issues the query. In otherwords, if you start a query (and it's a long running query), you won't see the results of any data committed by another session during the running of that query. Fine. That's expected. But if I am doing only queries (no transactions) via a connection, and no query is running when I commit data in another session, then the query-window should see the results of those changes. From the AUTOCOMMIT manual page cited above, In InnoDB, all user activity occurs inside a transaction. I suspect that the mysql client is implicitly starting a transaction when you do a set autocommit=0. Thus, any changes made by any other sessions won't be visible till you do a commit or rollback. Each time a commit or rollback is issued in the non-auto-commit session, you can see data changed by other sessions. With AUTOCOMMIT off, the transaction starts, in your case, with your first SELECT. Regardless, this is not a repeatable-read issue. I think it's a mysql client issue, and the fact that the client is creating transactions for you
Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
Yah, I tested in SQL*Plus - one window could see inserts, updates and
deletes that had been committed in another window (in which a commit or
rollback had not been issued). I ran the test again - delete data from a
table in one window and commit the change, and a select in the other
window displays the results.
Note that SQL*Plus by default does not auto-commit, but the key elements
of the test are the same. Data committed in one session is visible in
another session once committed.
In Oracle/SQL*Plus, data committed in session A will show up in Session
B if Session B has an open transaction. Here's the example (using
session A and B).
Session A:
insert into temp_table (col1) values ('a');
Session B:
insert into temp_table (col1) values ('b');
At this point, neither is committed, and neither session can see what's
the other has done (the left hand doesn't know what the right is doing,
so to speak).
Session A:
commit;
Session B:
SQL select * from temp_table;
C
-
b
a
Session B has an open transaction, yet can see the data that was
committed in another transaction. It's view of the data is, Show me all
the data that has been committed to the database at the point where I
started my query, plus all changes that I've made yet not committed or
rolled back.
Oracle runs in READ COMMITTED (the above), while INNODB runs in
REPEATABLE READ. Big difference. And I (stupidly) assumed they ran as
the same transaction isolation level.
Learn something new every day.
David
[EMAIL PROTECTED] wrote:
If you are NOT in autocommit mode, your connection (or the server, it
doesn't matter which) starts a transaction *when you issue your first
command*. Every command you issue on that connection is in that
initial transaction until you EXPLICITLY commit or rollback (or do
something else that commits or rolls-back your transactions like ALTER
TABLE) . At that point a new transaction is automatically started when
you issue your next command. If I remember correctly, closing a
connection with a pending transaction defaults to a ROLLBACK. That way
if a transaction is left incomplete due to communications failure, you
maintain a consistent database.
If autocommit is enabled (SET autocommit=1) then each command
executes within it's own mini-transaction (one little, tight
transaction wrapped around each statement). Each SELECT can see what
every other INSERT, UPDATE, or DELETE has done (assuming their
transactions are committed) because it is not already inside a pending
transaction. This is the default mode for user interaction for nearly
every database product I have used. With autocommit active, you are
required to explicitly issue a START TRANSACTION if you want a
transaction that includes several commands.
Are you sure that's not how Oracle operates, too? I ask because MS SQL
acts the same as MySQL when it comes to autocommits
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
David Griffiths [EMAIL PROTECTED] wrote on 09/01/2005 12:33:55 PM:
I believe you - I'm just a but surprised. I guess I had a singular view
of how a session should work based on Oracle. I would have expected
that
until you execute SQL that requires a commit or a rollback, you
wouldn't
be in a transaction. Unfortunately, if you have connections that are
read and write, and one connection ends up being used for SELECTs only
(just bad luck) , it's going to have an out-date view of the database.
To me, a transaction is something you commit or rollback. You can't
commit or rollback a SELECT unless you've done a locking-read. I guess
Oracle is just smarter about it, only starting a transaction behind the
scenes if you've actually done something that warrants a transaction.
David
Michael Stassen wrote:
David Griffiths wrote:
No, with the default transaction isolation level, REPEATABLE READ,
that's how it is supposed to work. You've started a transaction in
Window B, so Window B is immune to changes made in Window A until
you
finish the transaction in Window B. See the manual for details
http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html;
I haven't explicitly started any transactions in Window B - it's
select-only (with autocommit set to 0). Are you saying that even
though
transactions have happend and been committed in Window A, I won't be
able
to see those transactions in Window B?
The key word is explicitly. You have implicitly started a
transaction with your first SELECT, precisely because you turned
AUTOCOMMIT off. That transaction continues until you COMMIT or
ROLLBACK (or perform an action that implicitly commits
http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or-
rollback.html).
That's the point of setting AUTOCOMMIT to off. If you only want to
start transactions explicitly (with START TRANSACTION or BEGIN), then
you need to leave AUTOCOMMIT on. See the manual for details
Weird delete behavior on mysql 4.0 - rows not disappearing....
I just discovered some weird behaviour with MySQL 4.0 (4.0.24 and
4.0.18) using InnoDB.
If you have two connections to mysql (I use the mysql client), one of
which has autocommit turned on, an the other turned off, a row deleted
from the client with autocommit turned on still shows up in the client
with autocommit turned off, even after a commit.
That's complicated, so here's an example.
CREATE TABLE bug_find (col1 VARCHAR(10) NOT NULL);
Now open two windows (I'll call them Window A and Window B).
Leave Window A alone (I am assuming your client is in auto-commit mode).
In Window B, type,
SET autocommit = 0;
In Window A, type
INSERT INTO bug_find (col1) VALUES ('a');
This should be committed automatically.
In Window B, type
SELECT * from bug_find;
The column should be there.
In Window A, type,
DELETE FROM bug_find;
Again, this should be committed.
In Window B, type,
SELECT * FROM bug_find;
Whoops - still there, even though it's been removed.
In Window A, type,
commit;
In Window B, type,
SELECT * FROM bug_find;
Still there.
To make it disappear from Window B, type,
commit;
That makes no sense. The changes Window B sees (that are made by Window
A) should not depend on issuing a commit - it has to see any data
committed by Window A (unless it's trying to avoid dirty reads, which
isn't the case here).
If Window B is in autocommit mode, you see the deletion right away. It
seems to be the autocommit=0 that's screwing stuff up. I haven't tested
this with the JDBC drivers, or with the Query Browser, or anything else.
It may just be a MySQL client issue.
This is a big problem with data consistency. Note that this bug also
exists for updates (any updates made in Window A are not seen by Window
B until Window B issues a commit). Also, turning autocommit off in a
session half way, and the same behaviour happens.
Is this a known bug?
David.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
David Griffiths wrote:
I just discovered some weird behaviour with MySQL 4.0 (4.0.24 and
4.0.18) using InnoDB.
If you have two connections to mysql (I use the mysql client), one of
which has autocommit turned on, an the other turned off, a row deleted
from the client with autocommit turned on still shows up in the client
with autocommit turned off, even after a commit.
That's complicated, so here's an example.
CREATE TABLE bug_find (col1 VARCHAR(10) NOT NULL);
Now open two windows (I'll call them Window A and Window B).
Leave Window A alone (I am assuming your client is in auto-commit mode).
In Window B, type,
SET autocommit = 0;
In Window A, type
INSERT INTO bug_find (col1) VALUES ('a');
This should be committed automatically.
In Window B, type
SELECT * from bug_find;
The column should be there.
In Window A, type,
DELETE FROM bug_find;
Again, this should be committed.
In Window B, type,
SELECT * FROM bug_find;
Whoops - still there, even though it's been removed.
In Window A, type,
commit;
In Window B, type,
SELECT * FROM bug_find;
Still there.
To make it disappear from Window B, type,
commit;
That makes no sense. The changes Window B sees (that are made by Window
A) should not depend on issuing a commit - it has to see any data
committed by Window A (unless it's trying to avoid dirty reads, which
isn't the case here).
No, with the default transaction isolation level, REPEATABLE READ, that's
how it is supposed to work. You've started a transaction in Window B, so
Window B is immune to changes made in Window A until you finish the
transaction in Window B. See the manual for details
http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html
If Window B is in autocommit mode, you see the deletion right away. It
seems to be the autocommit=0 that's screwing stuff up. I haven't tested
this with the JDBC drivers, or with the Query Browser, or anything else.
It may just be a MySQL client issue.
Each time you commit, you end the transaction and start a new one. The new
transaction gets a new snapshot of the data, so it sees previously committed
changes. With autocommit on, you are doing that automatically with every
statement. With autocommit off, you start a transaction with your first
select that doesn't end till you commit.
This is a big problem with data consistency. Note that this bug also
exists for updates (any updates made in Window A are not seen by Window
B until Window B issues a commit). Also, turning autocommit off in a
session half way, and the same behaviour happens.
I think you have it backwards. REPEATABLE READ transactions assure that for
the duration of a transaction, the only changes to your copy of the data are
the changes you make.
Is this a known bug?
It's not a bug.
David.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird delete behavior on mysql 4.0 - rows not disappearing....
On Wed, Aug 31, 2005 at 11:18:40PM -0400, Michael Stassen wrote: No, with the default transaction isolation level, REPEATABLE READ, that's how it is supposed to work. You've started a transaction in Window B, so Window B is immune to changes made in Window A until you finish the transaction in Window B. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html Is this a known bug? It's not a bug. Oracle works in a similar fashion. -Jason Martin -- Never eat anything bigger than your head. This message is PGP/MIME signed. pgpnWgmFXjYfU.pgp Description: PGP signature
