Derby 10.1.1 - Question about transactions

[Kostas Karadamoglou <ka...@yahoo.gr>]

Hello all,

I am developing an application wich uses Derby 10.1.1

The application has multiple clients/ read connections that read tables 
and one  write conenction that write into the tables.


The connection that modifies the table mostly inserts rows.

There is a potential problem of a user/read connection reading data 
while the write connection inserts rows. This can cause read connection 
to execute outdated sql statements.

How can I stop read connections to read data before the write connection 
commit the insertions.

I know that there is a LOCK TABLE statement with Derby but I want to 
avoid it (I don't want to add string manipulation of statements).

Can I use the setIsolationLevel of JDBC Connection?

Which level is most appropriate?

The method is defined at the Connection class. Does affect all the 
connections or only the one that called the method?

Re: Derby 10.1.1 - Question about transactions

[Mike Matrigali <mi...@sbcglobal.net>]

There is a typo in the info below, the default isolation level for
derby is read committed.

Mike Matrigali wrote:
> what you need to read up about is isolation level and transactions.
> Derby implements
> jdbc standard isolation levels: read uncommitted, read committed,
> repeatable read, serializable.
> 
> For all isolation levels derby will hold the write lock on the rows
> inserted until end of transaction.  For all isolation levels except
> for read uncommitted, reading transactions will block before reading
> uncommitted inserted rows by the writer.  The default isolation level
> is read uncommitted.
> 
> From your brief description I don't think you have to do anything,
> other than insure that the write transactions are the scope that
> you need.  Note that by default autocommit is enabled so every
> statement issues a commit - which does not sound like what you
> want.
> 
> /mikem
> 
> Kostas Karadamoglou wrote:
> 
>> Hello all,
>>
>> I am developing an application wich uses Derby 10.1.1
>>
>> The application has multiple clients/ read connections that read
>> tables and one  write conenction that write into the tables.
>>
>>
>> The connection that modifies the table mostly inserts rows.
>>
>> There is a potential problem of a user/read connection reading data
>> while the write connection inserts rows. This can cause read
>> connection to execute outdated sql statements.
>>
>> How can I stop read connections to read data before the write
>> connection commit the insertions.
>>
>> I know that there is a LOCK TABLE statement with Derby but I want to
>> avoid it (I don't want to add string manipulation of statements).
>>
>> Can I use the setIsolationLevel of JDBC Connection?
>>
>> Which level is most appropriate?
>>
>> The method is defined at the Connection class. Does affect all the
>> connections or only the one that called the method?
>>
>>
>>
> 
> 

Re: Derby 10.1.1 - Question about transactions

[Mike Matrigali <mi...@sbcglobal.net>]

what you need to read up about is isolation level and transactions. 
Derby implements
jdbc standard isolation levels: read uncommitted, read committed,
repeatable read, serializable.

For all isolation levels derby will hold the write lock on the rows
inserted until end of transaction.  For all isolation levels except
for read uncommitted, reading transactions will block before reading
uncommitted inserted rows by the writer.  The default isolation level
is read uncommitted.

 From your brief description I don't think you have to do anything,
other than insure that the write transactions are the scope that
you need.  Note that by default autocommit is enabled so every
statement issues a commit - which does not sound like what you
want.

/mikem

Kostas Karadamoglou wrote:
> Hello all,
> 
> I am developing an application wich uses Derby 10.1.1
> 
> The application has multiple clients/ read connections that read tables 
> and one  write conenction that write into the tables.
> 
> 
> The connection that modifies the table mostly inserts rows.
> 
> There is a potential problem of a user/read connection reading data 
> while the write connection inserts rows. This can cause read connection 
> to execute outdated sql statements.
> 
> How can I stop read connections to read data before the write connection 
> commit the insertions.
> 
> I know that there is a LOCK TABLE statement with Derby but I want to 
> avoid it (I don't want to add string manipulation of statements).
> 
> Can I use the setIsolationLevel of JDBC Connection?
> 
> Which level is most appropriate?
> 
> The method is defined at the Connection class. Does affect all the 
> connections or only the one that called the method?
> 
> 
>