Question: database commit

  • Thread starter www.guanfenglin.com
  • Start date
W

www.guanfenglin.com

Hello Guys,

I have a database commit question here:
suppose I have a transaction, inside the transaction i have 2 queries
to run then commit. The 1st query is to update the database, and the
second query is to select from the database which relates to the 1st
query, without commiting the first query to the database, will the 2nd
query select the correct information?

Thanks guys!!
 
O

Oliver Wong

www.guanfenglin.com said:
Hello Guys,

I have a database commit question here:
suppose I have a transaction, inside the transaction i have 2 queries
to run then commit. The 1st query is to update the database, and the
second query is to select from the database which relates to the 1st
query, without commiting the first query to the database, will the 2nd
query select the correct information?

In practice, this is implementation dependent (and it depends on what
you consider to the be "correct" information). I'm not an SQL guru, but I
believe that in SQL, the 2nd query should fetch the updated values from the
1st query. When using Hibernate though, and setting the commit mode to
manual, I found that the 2nd query will fetch the old un-updated values,
even from the same transaction.

- Oliver
 
T

Thomas Kellerer

www.guanfenglin.com wrote on 06.02.2006 21:47:
Hello Guys,

I have a database commit question here:
suppose I have a transaction, inside the transaction i have 2 queries
to run then commit. The 1st query is to update the database, and the
second query is to select from the database which relates to the 1st
query, without commiting the first query to the database, will the 2nd
query select the correct information?


As long as the update and the select are run in the same transaction,
then yes the select will (and has to if the DBMS conforms to the ACID
principles) the updated value. For the JDBC drivers I know, this means
that both statements have to be executed using the same Connection
object, otherwise they'll never be in the same transaction.

Thomas
 
T

Thomas Kellerer

Oliver Wong wrote on 06.02.2006 22:10:
In practice, this is implementation dependent (and it depends on what
you consider to the be "correct" information).

The "correct" information is nothing that needs to be "considered" and
should not be (DBMS) implementation dependent. This is well defined by
the SQL standard (ACID), and it has to be the value that has been set
using the UPDATE statement as long as both statements are execute in the
same transaction.

Thomas
 
W

www.guanfenglin.com

thanks guys
Thomas said:
Oliver Wong wrote on 06.02.2006 22:10:

The "correct" information is nothing that needs to be "considered" and
should not be (DBMS) implementation dependent. This is well defined by
the SQL standard (ACID), and it has to be the value that has been set
using the UPDATE statement as long as both statements are execute in the
same transaction.

Thomas
 
S

steve

Hello Guys,

I have a database commit question here:
suppose I have a transaction, inside the transaction i have 2 queries
to run then commit. The 1st query is to update the database, and the
second query is to select from the database which relates to the 1st
query, without commiting the first query to the database, will the 2nd
query select the correct information?

Thanks guys!!


Here Be dragons!

it would depend on the database and how it has been setup in this regard.

On oracle & with the default settings , your answer would be Yes.
but oracle also allows it to be set so that the answer would be no.

( consider a long running query & update over several hours , where you would
NOT want to see updated data)

There is an article on oracle website.

I think it was in the (recent) oracle magazine, which is available on line.

They specifically "go out" and mess up some compeating databases on this
point.

They show how it is possible to radically mess up the data and corrupt the
meaning of the data. ( i think it related to on line banking)

Steve
 
O

Oliver Wong

Thomas Kellerer said:
Oliver Wong wrote on 06.02.2006 22:10:

The "correct" information is nothing that needs to be "considered" and
should not be (DBMS) implementation dependent. This is well defined by the
SQL standard (ACID), and it has to be the value that has been set using
the UPDATE statement as long as both statements are execute in the same
transaction.

Just a small nitpick, but note that the OP never actually mentions SQL,
and the OP also never mentions which information (s)he considers to be the
"correct" information.

So I brought up SQL as an example of one behaviour, and Hibernate as an
example of a different behaviour. I believe Hibernate is in fact incorrect
in its behaviour, in that should emulate SQL's behaviour (as they claim that
their HQL language is "SQL-like"), but regardless of whether its behaviour
is "correct" or not, that's what its behaviour does, and unless you want to
try to "fix" Hibernate, you'll have to deal with its behaviour if you're
gonna use it for your own projects.

- Oliver
 
S

steve

Oliver Wong wrote on 06.02.2006 22:10:

The "correct" information is nothing that needs to be "considered" and
should not be (DBMS) implementation dependent. This is well defined by
the SQL standard (ACID), and it has to be the value that has been set
using the UPDATE statement as long as both statements are execute in the
same transaction.

Thomas

the default should be ACID,

BUT

it depends on the preceding code in the sql & how the DBA has setup the
database.

steve
 
T

Thomas Hawtin

Roedy said:
sounds like a collectable acronym. What does it stand for and do you
have a definitive URL?

Atomicity, Consistency, Isolation, and Durability

http://en.wikipedia.org/wiki/ACID

Or grab yourself any introductory database book.

SQL/JDBC allow relaxing isolation (Connection.setTransactionIsolation)
and consistency can be deferred between statements within a transaction.
There's an unfortunate habit of making transactions non-durable, and a
naive dba might not be aware of that. Database utilities and often SQL
DDL (Data Definition Language) may not make any attempt at ACID.

Tom Hawtin
 
W

www.guanfenglin.com

My another question,

if there is a update statement is executed but not committed, will that
affect the select statement from another connection to the same
database/table?
 
O

Oliver Wong

www.guanfenglin.com said:
My another question,

if there is a update statement is executed but not committed, will that
affect the select statement from another connection to the same
database/table?

Again, you haven't specified if you're talking about SQL. A lot of the
answers you get will probably assume you're using SQL, but there are other
query languages out there, and their behaviour can be different.

Anyway, I believe that the "correct" behaviour in SQL is that the SELECT
statement in the different connection will NOT get the updated value.

But again, in practice, things might be different. I believe the MySQL
RDBM does not correctly implement transactions, and so "non-committed"
updates WILL affect the select in another connection (because actually all
UPDATEs are committed, even if you request them not to be).

- Oliver
 
T

Thomas Hawtin

www.guanfenglin.com said:
if there is a update statement is executed but not committed, will that
affect the select statement from another connection to the same
database/table?

Depends upon the transaction isolation level. See:

http://download.java.net/jdk6/docs/api/java/sql/Connection.html#setTransactionIsolation(int)

For the various options

TRANSACTION_SERIALIZABLE - This does what you expect. Attempting to
think about the implications of other isolation levels is not good for
your mental health. (The standard technique for dealing with other
isolation levels is to not bother thinking about it.) However,
performance, particularly on huge transactions like nightly reports, can
suffer.

TRANSACTION_REPEATABLE_READ - This may see the committed update
statement. However, the other transaction either sees an altered row or
it doesn't. If the transaction found the row in a select before the
update statement, any further selects will see the original row. It will
not see uncommitted updates.

TRANSACTION_READ_COMMITTED - The select transaction *may* see the
updated rows, even if it read the old values. It will not see
uncommitted updates.

TRANSACTION_READ_UNCOMMITTED - It may see the uncommitted update.

TRANSACTION_NONE - Commit is not supported.

You said in another post you were using Oracle. The default is
TRANSACTION_READ_COMMITTED, so the uncommitted update will not be read.
Even so, I suggest switching the transaction isolation level to
TRANSACTION_SERIALIZABLE unless you absolutely must change that.

Tom Hawtin
 
J

Jon Martin Solaas

www.guanfenglin.com said:
Hi Oliver,

Yes I am talking about Oracle databases.

Oracle will be pretty much standards conformant, ie. other connections
won't see the changes before they're commited.
 
J

Jon Martin Solaas

Oliver said:
But again, in practice, things might be different. I believe the MySQL
RDBM does not correctly implement transactions, and so "non-committed"
updates WILL affect the select in another connection (because actually all
UPDATEs are committed, even if you request them not to be).

Is this still true as of MySQL 5.0? If so I'm nothing less than amazed
.... how come people will even touch the product?
 
T

Thomas Hawtin

Jon said:
Is this still true as of MySQL 5.0? If so I'm nothing less than amazed
... how come people will even touch the product?

It appears to be like this...

MySQL comes with a number of storage engines. Each one does different
things, but you can configure which one you want.

MyISAM doesn't support transactions. InnoDB does support transactions
and defaults to repeatable read isolation level (tighter than the Oracle
default).

It appears that MySQL defaults to MyISAM, which is utterly shameful.
MySQL has a history of not using transactions.

I can only think MySQL popularity over, say, PostgreSQL is down to users
not understanding transactions let along transaction isolation levels.
Not that Oracle users seem to have a clue about transaction isolation.

Tom Hawtin
 
S

steve

sounds like a collectable acronym. What does it stand for and do you
have a definitive URL?

have a look on oracles web shite , there is more than a few acronyms there,
but as i used it ....


ACID
The basic properties of a database transaction: Atomicity, Consistency,
Isolation, and Durability. All Oracle transactions comply with these
properties.

€ Atomicity - The entire sequence of actions must be either completed or
aborted. The transaction cannot be partially successful.

€ Consistency - The transaction takes the resources from one consistent
state to another.

€ Isolation - A transaction's effect is not visible to other transactions
until the transaction is committed.

€ Durability - Changes made by the committed transaction are permanent an
d must survive system failure.




the link is

http://www.orafaq.com/glossary/faqglosa.htm
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top