You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@tomee.apache.org by Matt Hogstrom <ma...@hogstrom.org> on 2006/07/27 04:47:37 UTC
Pessimistic locking strategy for CMP beans in 1.1.1
I wanted to let people know about one of the fixes I'm putting into 1.1.1. I addressed this issue
in a note previously and it has to do with our locking model for CMP persistence. This note is
applicable to OpenEJB-2.1.1-SNAPSHOT as well as TranQL 1.3.1-SNAPSHOT.
Currently users deploying CMP beans have no mechanism to specify if they want to lock the row in a
DB when they execute a finder on a CMP entity. This means that there is no locking in the database
and multiple concurrent users have a high degree of either corrupting data or getting SQL -911
deadlocks in their application.
To mitigate this issue I am adding a few new elements to the OpenEJB schema to allow users to
specify this option. Here is my current thinking and I'd like some feedback if you have time and
are interested.
Basically there are two ways locking is implemented. The first is a pessimistic strategy that
relies on the database to enforce locking. Unfortunately, different DBMS's have various ways to
address this. It is generally accomplished by setting the appropriate isolation level and
specifying *for update* on the select clause. I believe that with the knowledge of a pessimistic
strategy the DBSyntaxGenerators in TranQL can put out the appropriate SQL to accomplish this.
The second method is to use an optimistic strategy where a mono incrementing column or timestamp is
used to disambiguate tuples from each other. The container keeps track of the value of the
optimistic column. I'm planning on implementing this later but thought we'd make the schema changes
now.
I would like to add a <locking-strategy> section to the entity-bean element after the pre-fetch group.
The locking strategy really is either optimistic or pessimistic. Currently I'm focusing on
pessimistic and need to finalize the optimistic options but this is enough for discussion.
Something like:
<xs:element name="locking-strategy">
<xs:complexType>
<xs:sequence>
<xs:element name="optimistic-locking" maxOccurs="1">
<xs:complexType>
<xs:sequence>
<xs:element name="optimistic-column" type="xs:string"/>
<xs:element name="optimistic-type" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
<xs:complexType>
<xs:sequence>
<xs:element name="pessimistic-locking" maxOccurs="1">
<xs:complexType>
<xs:sequence>
<xs:element name="select-for-update" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
This seems clunky to me...is there a better way to express this idea as the locking strategy
requires one or the other but not both. I think the above is ok and will validate in the builder
but want some feedback.
Here is what I would expect a user to code:
<entity>
<ejb-name>KeyGenEJB</ejb-name>
<table-name>KeyGenEJB</table-name>
<cmp-field-mapping>
<cmp-field-name>keyVal</cmp-field-name>
<table-column>keyVal</table-column>
</cmp-field-mapping>
<cmp-field-mapping>
<cmp-field-name>keyName</cmp-field-name>
<table-column>keyName</table-column>
</cmp-field-mapping>
<locking-strategy>
<pessimistic-locking>
</locking-strategy>
.
.
or
.
.
<locking-strategy>
<optimistic-locking>
<optimistic-column>occColumn</optimistic-column>
<optimistic-type>TIMESTAMP</optimistic-type>
</locking-strategy>
</entity>
BAH...NOTE I wrote this abot 8 hours ago and here it sits unsent...bummer. Well, here it is now.
Re: Pessimistic locking strategy for CMP beans in 1.1.1
Posted by Matt Hogstrom <ma...@hogstrom.org>.
Thanks Mohammed...that's what I ws looking for. cheers.
Mohammed Nour wrote:
> Hi Matt...
>
> On 7/27/06, Matt Hogstrom <ma...@hogstrom.org> wrote:
>>
>> I wanted to let people know about one of the fixes I'm putting into
>> 1.1.1. I
>> addressed this issue
>> in a note previously and it has to do with our locking model for CMP
>> persistence. This note is
>> applicable to OpenEJB-2.1.1-SNAPSHOT as well as TranQL 1.3.1-SNAPSHOT.
>>
>> Currently users deploying CMP beans have no mechanism to specify if they
>> want to lock the row in a
>> DB when they execute a finder on a CMP entity. This means that there is
>> no locking in the database
>> and multiple concurrent users have a high degree of either corrupting
>> data
>> or getting SQL -911
>> deadlocks in their application.
>>
>> To mitigate this issue I am adding a few new elements to the OpenEJB
>> schema to allow users to
>> specify this option. Here is my current thinking and I'd like some
>> feedback if you have time and
>> are interested.
>>
>> Basically there are two ways locking is implemented. The first is a
>> pessimistic strategy that
>> relies on the database to enforce locking. Unfortunately, different
>> DBMS's have various ways to
>> address this. It is generally accomplished by setting the appropriate
>> isolation level and
>> specifying *for update* on the select clause. I believe that with the
>> knowledge of a pessimistic
>> strategy the DBSyntaxGenerators in TranQL can put out the appropriate SQL
>> to accomplish this.
>>
>> The second method is to use an optimistic strategy where a mono
>> incrementing column or timestamp is
>> used to disambiguate tuples from each other. The container keeps
>> track of
>> the value of the
>> optimistic column. I'm planning on implementing this later but thought
>> we'd make the schema changes
>> now.
>>
>> I would like to add a <locking-strategy> section to the entity-bean
>> element after the pre-fetch group.
>>
>> The locking strategy really is either optimistic or
>> pessimistic. Currently I'm focusing on
>> pessimistic and need to finalize the optimistic options but this is
>> enough
>> for discussion.
>>
>> Something like:
>>
>> <xs:element name="locking-strategy">
>> <xs:complexType>
>> <xs:sequence>
>> <xs:element name="optimistic-locking"
>> maxOccurs="1">
>> <xs:complexType>
>> <xs:sequence>
>> <xs:element name="optimistic-column"
>> type="xs:string"/>
>> <xs:element name="optimistic-type"
>> type="xs:string"/>
>> </xs:sequence>
>> </xs:complexType>
>> </xs:element>
>> </xs:sequence>
>> </xs:complexType>
>> <xs:complexType>
>> <xs:sequence>
>> <xs:element name="pessimistic-locking"
>> maxOccurs="1">
>> <xs:complexType>
>> <xs:sequence>
>> <xs:element name="select-for-update"
>> minOccurs="0"/>
>> </xs:sequence>
>> </xs:complexType>
>> </xs:element>
>> </xs:sequence>
>> </xs:complexType>
>> </xs:element>
>>
>> This seems clunky to me...is there a better way to express this idea as
>> the locking strategy
>> requires one or the other but not both. I think the above is ok and will
>> validate in the builder
>> but want some feedback.
>
>
> To make an instance of this XML-Schema contains only one locking strategy,
> you have to make it as follows:
>
> <xs:element name="locking-strategy">
> <xs:complexType>
> <xs:sequence>
> <xs:choice>
> <xs:element name="optimistic-locking"
> maxOccurs="1">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="optimistic-column"
> type="xs:string"/>
> <xs:element name="optimistic-type"
> type="xs:string"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="pessimistic-locking"
> maxOccurs="1">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="select-for-update"
> minOccurs="0"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:choice>
> </xs:sequence>
> <xs:complexType>
> </xs:element>
>
>
> Here is what I would expect a user to code:
>>
>> <entity>
>> <ejb-name>KeyGenEJB</ejb-name>
>> <table-name>KeyGenEJB</table-name>
>> <cmp-field-mapping>
>> <cmp-field-name>keyVal</cmp-field-name>
>> <table-column>keyVal</table-column>
>> </cmp-field-mapping>
>> <cmp-field-mapping>
>> <cmp-field-name>keyName</cmp-field-name>
>> <table-column>keyName</table-column>
>> </cmp-field-mapping>
>> <locking-strategy>
>> <pessimistic-locking>
>> </locking-strategy>
>> .
>> .
>> or
>> .
>> .
>> <locking-strategy>
>> <optimistic-locking>
>> <optimistic-column>occColumn</optimistic-column>
>> <optimistic-type>TIMESTAMP</optimistic-type>
>> </locking-strategy>
>> </entity>
>>
>> BAH...NOTE I wrote this abot 8 hours ago and here it sits
>> unsent...bummer. Well, here it is now.
>>
>
Re: Pessimistic locking strategy for CMP beans in 1.1.1
Posted by Mohammed Nour <no...@gmail.com>.
Hi Matt...
On 7/27/06, Matt Hogstrom <ma...@hogstrom.org> wrote:
>
> I wanted to let people know about one of the fixes I'm putting into 1.1.1. I
> addressed this issue
> in a note previously and it has to do with our locking model for CMP
> persistence. This note is
> applicable to OpenEJB-2.1.1-SNAPSHOT as well as TranQL 1.3.1-SNAPSHOT.
>
> Currently users deploying CMP beans have no mechanism to specify if they
> want to lock the row in a
> DB when they execute a finder on a CMP entity. This means that there is
> no locking in the database
> and multiple concurrent users have a high degree of either corrupting data
> or getting SQL -911
> deadlocks in their application.
>
> To mitigate this issue I am adding a few new elements to the OpenEJB
> schema to allow users to
> specify this option. Here is my current thinking and I'd like some
> feedback if you have time and
> are interested.
>
> Basically there are two ways locking is implemented. The first is a
> pessimistic strategy that
> relies on the database to enforce locking. Unfortunately, different
> DBMS's have various ways to
> address this. It is generally accomplished by setting the appropriate
> isolation level and
> specifying *for update* on the select clause. I believe that with the
> knowledge of a pessimistic
> strategy the DBSyntaxGenerators in TranQL can put out the appropriate SQL
> to accomplish this.
>
> The second method is to use an optimistic strategy where a mono
> incrementing column or timestamp is
> used to disambiguate tuples from each other. The container keeps track of
> the value of the
> optimistic column. I'm planning on implementing this later but thought
> we'd make the schema changes
> now.
>
> I would like to add a <locking-strategy> section to the entity-bean
> element after the pre-fetch group.
>
> The locking strategy really is either optimistic or
> pessimistic. Currently I'm focusing on
> pessimistic and need to finalize the optimistic options but this is enough
> for discussion.
>
> Something like:
>
> <xs:element name="locking-strategy">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="optimistic-locking"
> maxOccurs="1">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="optimistic-column"
> type="xs:string"/>
> <xs:element name="optimistic-type"
> type="xs:string"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> <xs:complexType>
> <xs:sequence>
> <xs:element name="pessimistic-locking"
> maxOccurs="1">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="select-for-update"
> minOccurs="0"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
>
> This seems clunky to me...is there a better way to express this idea as
> the locking strategy
> requires one or the other but not both. I think the above is ok and will
> validate in the builder
> but want some feedback.
To make an instance of this XML-Schema contains only one locking strategy,
you have to make it as follows:
<xs:element name="locking-strategy">
<xs:complexType>
<xs:sequence>
<xs:choice>
<xs:element name="optimistic-locking"
maxOccurs="1">
<xs:complexType>
<xs:sequence>
<xs:element name="optimistic-column"
type="xs:string"/>
<xs:element name="optimistic-type"
type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="pessimistic-locking"
maxOccurs="1">
<xs:complexType>
<xs:sequence>
<xs:element name="select-for-update"
minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:sequence>
<xs:complexType>
</xs:element>
Here is what I would expect a user to code:
>
> <entity>
> <ejb-name>KeyGenEJB</ejb-name>
> <table-name>KeyGenEJB</table-name>
> <cmp-field-mapping>
> <cmp-field-name>keyVal</cmp-field-name>
> <table-column>keyVal</table-column>
> </cmp-field-mapping>
> <cmp-field-mapping>
> <cmp-field-name>keyName</cmp-field-name>
> <table-column>keyName</table-column>
> </cmp-field-mapping>
> <locking-strategy>
> <pessimistic-locking>
> </locking-strategy>
> .
> .
> or
> .
> .
> <locking-strategy>
> <optimistic-locking>
> <optimistic-column>occColumn</optimistic-column>
> <optimistic-type>TIMESTAMP</optimistic-type>
> </locking-strategy>
> </entity>
>
> BAH...NOTE I wrote this abot 8 hours ago and here it sits
> unsent...bummer. Well, here it is now.
>
Re: Pessimistic locking strategy for CMP beans in 1.1.1
Posted by Oliver Zeigermann <ol...@gmail.com>.
Hey, again!
Thanks for the detailed answers. See my comments inline.
2006/7/27, Matt Hogstrom <ma...@hogstrom.org>:
> > 2.) Are the requests to load the bean from the db still inside a
> > transaction?
>
> Yes
>
> If so what isolation level?
>
> Ahhh....the 64,000 dollar question. Every RDBMs is slightly different in their implementations.
> Oracle would use read-committed as would DB2 for the optimistic scenario. For pessimistic DB2 would
> need to bump up to Read Stability (RS) which is the equivalent of REPEATABLE_READ. This is because
> the lock duration is different for DB2 than it is for Oracle at READ_COMMITTED. So the correct
> answer is it depends.
So, if I understand this correctly than this is something the user has
to set, right? If so don't you need another field in the bean
descriptor for the isolation level of the request?
Additionally, I have no big idea of DB2 isoaltion levels, but I had
the impression SERIALIZABLE was the one for an optimistic scenario in
Oracle - even though it does not sound like it would be. AFAIK this
level does not use any blocking locks, but rahter some sort of
snapshot isolation. Am I wrong?
> > 3.) Is there something like caching of beans in Geronimo? If so how is
> > this handled, especially in a distributed scenario?
>
> Currently there is a per Tx cache. I believe Gianny was working on a global cache but I don't know
> the state of it. Although, the global cache is on a per G instance and not a cluster. We need to
> work on that.
Very interesting! Have you already inverstigated what happens when
some objects in a tx come from the cache and others from the db? Also,
what to do when an object changes in the db while it is used from the
cache. Might happen when one cluster node writes changes to an object
in the db. Is there any notification mechanism? I guess JGroups is no
option because of the bad LGPL license. Are there any alternatives?
Any thoughts to enlighten me?
Cheers
Oliver
Re: Pessimistic locking strategy for CMP beans in 1.1.1
Posted by Matt Hogstrom <ma...@hogstrom.org>.
Oliver Zeigermann wrote:
> Hi, Matt!
>
> I am rather new to Geronimo, so please be patient if my questions
> sound rather stupid.
>
> 2006/7/27, Matt Hogstrom <ma...@hogstrom.org>:
>> The second method is to use an optimistic strategy where a mono
>> incrementing column or timestamp is
>> used to disambiguate tuples from each other. The container keeps
>> track of the value of the
>> optimistic column. I'm planning on implementing this later but
>> thought we'd make the schema changes
>> now.
>
> Questions:
> 1.) I was wondering what to do upon conflict. I can only think of
> letting the transaction fail. Right?
For optimistic there are two options I think. One is to simply rollback the Tx and call it a day
which is probably the most common scenario. The other would be to Roll the Tx back and then restart
the request. The assumption being that since this is an optimistic scenario the second attempt
would most probably succeed.
> 2.) Are the requests to load the bean from the db still inside a
> transaction?
Yes
If so what isolation level?
Ahhh....the 64,000 dollar question. Every RDBMs is slightly different in their implementations.
Oracle would use read-committed as would DB2 for the optimistic scenario. For pessimistic DB2 would
need to bump up to Read Stability (RS) which is the equivalent of REPEATABLE_READ. This is because
the lock duration is different for DB2 than it is for Oracle at READ_COMMITTED. So the correct
answer is it depends.
I guess there still can be a deadlock in the db.
I don't think you can 100% avoid those but a lot of that also depends on the applciation to enlist
resources in the same order to avoid that.
> 3.) Is there something like caching of beans in Geronimo? If so how is
> this handled, especially in a distributed scenario?
Currently there is a per Tx cache. I believe Gianny was working on a global cache but I don't know
the state of it. Although, the global cache is on a per G instance and not a cluster. We need to
work on that.
>
> Thanks in advance for any hints and cheers
>
> Oliver
>
>
>
Re: Pessimistic locking strategy for CMP beans in 1.1.1
Posted by Oliver Zeigermann <ol...@gmail.com>.
Hi, Matt!
I am rather new to Geronimo, so please be patient if my questions
sound rather stupid.
2006/7/27, Matt Hogstrom <ma...@hogstrom.org>:
> The second method is to use an optimistic strategy where a mono incrementing column or timestamp is
> used to disambiguate tuples from each other. The container keeps track of the value of the
> optimistic column. I'm planning on implementing this later but thought we'd make the schema changes
> now.
Questions:
1.) I was wondering what to do upon conflict. I can only think of
letting the transaction fail. Right?
2.) Are the requests to load the bean from the db still inside a
transaction? If so what isolation level? I guess there still can be a
deadlock in the db.
3.) Is there something like caching of beans in Geronimo? If so how is
this handled, especially in a distributed scenario?
Thanks in advance for any hints and cheers
Oliver
Re: Pessimistic locking strategy for CMP beans in 1.1.1
Posted by Dain Sundstrom <da...@iq80.com>.
I'm not sure this will cover the common strategies used by most
containers. I recall implementing a few different strategies in
another appserver:
o DB auto update sequence
o Container manual update sequence
o DB last modified timestamp column
o Container manual update sequence (unreliable but people use it)
o Check column values haven't changed between select an update
o All columns
o Some special columns
o Only columns being updated
o All columns that were read in during the tx
I doubt we need all of these, but I think we should allow room for
growth in the schema.
-dain
On Jul 31, 2006, at 7:38 AM, Matt Hogstrom wrote:
> Perhaps one additional element in the optimistic section like:
>
> <concurrency-control>[DATABASE | CONTAINER]</concurrency-control>
>
>
>
> Dain Sundstrom wrote:
>> +1 looks good
>> On Jul 26, 2006, at 7:47 PM, Matt Hogstrom wrote:
>>> <locking-strategy>
>>> <optimistic-locking>
>>> <optimistic-column>occColumn</optimistic-column>
>>> <optimistic-type>TIMESTAMP</optimistic-type>
>>> </locking-strategy>
>> one comment... this xml implies that the database is handling the
>> auto increment/update of the optimistic lock column. I have seen
>> schemas that assume that the application code will be handling
>> this, with some sql like this:
>> UPDATE foo
>> SET value = newValue, ver = 5
>> WHERE pk = myPk AND ver = 4
>> If the database is auto updating the row, you will need to reread
>> the column after any update, so if you make another update in the
>> same transaction, you can assure you know the current value of the
>> optimistic column.
>> -dain
Re: Pessimistic locking strategy for CMP beans in 1.1.1
Posted by Matt Hogstrom <ma...@hogstrom.org>.
Perhaps one additional element in the optimistic section like:
<concurrency-control>[DATABASE | CONTAINER]</concurrency-control>
Dain Sundstrom wrote:
> +1 looks good
>
> On Jul 26, 2006, at 7:47 PM, Matt Hogstrom wrote:
>
>> <locking-strategy>
>> <optimistic-locking>
>> <optimistic-column>occColumn</optimistic-column>
>> <optimistic-type>TIMESTAMP</optimistic-type>
>> </locking-strategy>
>
> one comment... this xml implies that the database is handling the auto
> increment/update of the optimistic lock column. I have seen schemas
> that assume that the application code will be handling this, with some
> sql like this:
>
> UPDATE foo
> SET value = newValue, ver = 5
> WHERE pk = myPk AND ver = 4
>
> If the database is auto updating the row, you will need to reread the
> column after any update, so if you make another update in the same
> transaction, you can assure you know the current value of the optimistic
> column.
>
> -dain
>
>
>
Re: Pessimistic locking strategy for CMP beans in 1.1.1
Posted by Dain Sundstrom <da...@iq80.com>.
+1 looks good
On Jul 26, 2006, at 7:47 PM, Matt Hogstrom wrote:
> <locking-strategy>
> <optimistic-locking>
> <optimistic-column>occColumn</optimistic-column>
> <optimistic-type>TIMESTAMP</optimistic-type>
> </locking-strategy>
one comment... this xml implies that the database is handling the
auto increment/update of the optimistic lock column. I have seen
schemas that assume that the application code will be handling this,
with some sql like this:
UPDATE foo
SET value = newValue, ver = 5
WHERE pk = myPk AND ver = 4
If the database is auto updating the row, you will need to reread the
column after any update, so if you make another update in the same
transaction, you can assure you know the current value of the
optimistic column.
-dain