db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brett Wooldridge <brett.wooldri...@gmail.com>
Subject Re: Second thread is blocked until the first thread commit the transaction, I can not understand why...
Date Mon, 03 Aug 2009 08:09:49 GMT
I think you misunderstand the semantics of read_commited.  I suggest reading
this: http://en.wikipedia.org/wiki/Isolation_(database_systems)

Basically, read_committed means this:

"Data records retrieved by a query are not prevented from modification by
some other transaction. Non-repeatable
reads<http://en.wikipedia.org/wiki/Isolation_(computer_science)#Non-repeatable_reads>
may
occur, meaning data retrieved in a
SELECT<http://en.wikipedia.org/wiki/Select_(SQL)> statement
may be modified by some other transaction when it commits. In this isolation
level, read locks are acquired on selected data but they are released
immediately whereas write locks are released at the end of the transaction."

The locking semantic you are looking for does not exist as part of the ANSI
standard.  The isolation levels only define read lock behavior, not write
lock behavior.  Granted they are somewhat intertwined, but still distinct.
 Read_committed only means that a WRITE is not blocked by a READ, it does
not mean that a READ is not blocked by a WRITE.

Also from that page:

However, the [isolation level] definitions above have been criticised in the
paper A Critique of ANSI SQL Isolation
Levels<http://citeseer.ist.psu.edu/berenson95critique.html> as
being ambiguous, and as not accurately reflecting the isolation provided by
many databases:
This paper shows a number of weaknesses in the anomaly approach to defining
isolation levels. The three ANSI phenomena are ambiguous. Even their
broadest interpretations do not exclude anomalous behavior. This leads to
some counter-intuitive results. In particular, lock-based isolation levels
have different characteristics than their ANSI equivalents. This is
disconcerting because commercial database systems typically use locking.
Additionally, the ANSI phenomena do not distinguish among several isolation
levels popular in commercial systems.


On Mon, Aug 3, 2009 at 4:33 PM, sarah.kho <sarah.kho@gmail.com> wrote:

>
> Hi
> I am learning transaction and isolation levels. I tried to use
> read_committed in one thread and then in another thread insert some data
> into a table. the reader thread is blocked and waits until the first thread
> commit the transaction to complete the select statement. What I can not
> understand is: shouldn't the second thread only read what is already
> committed instead of waiting until the inserting thread finishes its job?
>
> Inserted thread code:
>
>  [code]
>
> String userName = "app";
>            String password = "app";
>            String url = "jdbc:derby://localhost:1527/sample";
>
> Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
>            conn = DriverManager.getConnection(url, userName, password);
>            conn.setAutoCommit(false);
>            PreparedStatement st = conn.prepareStatement("insert into
> APP.TABLE1(name, lastname) values('a','a')");
>            st.executeUpdate();
>
>            Thread.sleep(20000);
>
>            conn.commit();
> [/code]
>
>
> reader thread code:
> [code]
>
>            String userName = "app";
>            String password = "app";
>            String url = "jdbc:derby://localhost:1527/sample";
>
> Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
>            conn = DriverManager.getConnection(url, userName, password);
>            conn.setAutoCommit(false);
>
> conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
>
>
>            Statement st2 = conn.createStatement();
>
>            ResultSet rs = st2.executeQuery("select * from APP.TABLE1");
>
> [/code]
>
> also I tried and execute these code in two different application and the
> result is the same. The reader application waits for almost 20 seconds
> before it read the data.
> --
> View this message in context:
> http://www.nabble.com/Second-thread-is-blocked-until-the-first-thread-commit-the-transaction%2C-I-can-not-understand-why...-tp24786526p24786526.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
>

Mime
View raw message