Row level locking

[Umakanth Srinivasan <ka...@dhyanit.com>]

Mode: Embeded 
I was trying to generate sequence keys with my code, and I faced issue in
row level locking

First,
I locked my sequence keys holding table ( a table which says what is the
next key for a specific table), with the "FOR UPDATE OF <column name>"
keyword. 

Second,
Then updating the table by incrementing the existing value, and getting the
updated value.

With print traces in my code I found that when a select query locks a
particular row, and then before the next update, another connection is
allowed to make a select on the query and get the same value.

Am I missing anything here.


-- 
View this message in context: http://www.nabble.com/Row-level-locking-tf3745257.html#a10550668
Sent from the Apache Derby Developers mailing list archive at Nabble.com.

Re: Row level locking

[Øystein Grøvlen <Oy...@Sun.COM>]

Umakanth Srinivasan wrote:
> Mode: Embeded 
> I was trying to generate sequence keys with my code, and I faced issue in
> row level locking
> 
> First,
> I locked my sequence keys holding table ( a table which says what is the
> next key for a specific table), with the "FOR UPDATE OF <column name>"
> keyword. 
> 
> Second,
> Then updating the table by incrementing the existing value, and getting the
> updated value.
> 
> With print traces in my code I found that when a select query locks a
> particular row, and then before the next update, another connection is
> allowed to make a select on the query and get the same value.
> 
> Am I missing anything here.

Specifying FOR UPDATE will not by itself cause locks to be held after 
the query is completed.  In order to achieve that you will have to set 
the isolation level to REPEATABLE READ or SERIALIZABLE.

--
Øystein

Re: Row level locking

[Stanley Bradbury <St...@gmail.com>]

Umakanth Srinivasan wrote:
> No Iam not performing anything with respect to AutoCommit. I observed that
> this problem is related to the isolation level. In MySQL the problem did not
> occur. When I checked the default isolation level of MySQL it is
> REPEATABLE_READ, in Derby it is READ_COMMITED. 
>
> Hope I must have the AUTOCOMMIT set to on.
>   
My only point is that locks are released at commit or rollback and with 
AUTOCOMMIT ON a commit is issued at the end of the execution of a 
statement.  Thus I find using SELECT ... FOR UPDATE ... most effective 
with AUTOCOMMIT OFF.

Re: Row level locking

[Umakanth Srinivasan <ka...@dhyanit.com>]

No Iam not performing anything with respect to AutoCommit. I observed that
this problem is related to the isolation level. In MySQL the problem did not
occur. When I checked the default isolation level of MySQL it is
REPEATABLE_READ, in Derby it is READ_COMMITED. 

Hope I must have the AUTOCOMMIT set to on.
-- 
View this message in context: http://www.nabble.com/Row-level-locking-tf3745257.html#a10676514
Sent from the Apache Derby Developers mailing list archive at Nabble.com.

Re: Row level locking

[Stanley Bradbury <St...@gmail.com>]

Umakanth Srinivasan wrote:
> Mode: Embeded 
> I was trying to generate sequence keys with my code, and I faced issue in
> row level locking
>
> First,
> I locked my sequence keys holding table ( a table which says what is the
> next key for a specific table), with the "FOR UPDATE OF <column name>"
> keyword. 
>
> Second,
> Then updating the table by incrementing the existing value, and getting the
> updated value.
>
> With print traces in my code I found that when a select query locks a
> particular row, and then before the next update, another connection is
> allowed to make a select on the query and get the same value.
>
> Am I missing anything here.
>
>
>   
Are you turning off autocommit before performing SELECT ... FOR UPDATE 
OF ...  ?