You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Edson Carlos Ericksson Richter <ed...@mgrinformatica.com.br> on 2006/04/22 16:50:41 UTC
Procedure based sequencence generator repeating values
Hi!
There is a very long time since my last problem with OJB - thank you
guys, it's awesome how good work was done until now.
In last month, I was migrating from Oracle to MS SQL, and changed from
Sequence Manager Oracle native to Procedure based on MS SQL. Worked
great on tests, but when in production, sometimes procedure returns same
value for two users, overriding content of child tables (it override
child tables, then throw duplicate primary key exception for parent
table but changes on child tables are not being rolled back). I'm sure
I'm beginning and rollbacking transactions, and opening and closing
connections (i never had this problem with Oracle, by example).
I was thinking if the problem wasn't default transaction level for MS
SQL being different from Oracle (and MaxDB, that I used before getting
in nervous with constant crashes/bad performance...).
Someone has faced similar problems?
Thanks,
Edson Richter
Re: Procedure based sequencence generator repeating values (SOLVED)
Posted by Armin Waibel <ar...@apache.org>.
Hi Edson,
congratulation for figure out this issue and thanks for detailed
description and fixed procedure.
I will add a link to your last post in sequence-manager documentation
and update the procedure example for mssql.
regards,
Armin
Edson Carlos Ericksson Richter wrote:
> Ok, this really solved. Final procedure code (works on SQL2K and SQL2K5):
>
> <code>
>
> CREATE PROCEDURE OJB_NEXTVAL_PROC
> @SEQ_NAME varchar(150)
> AS
> declare @MAX_KEY BIGINT
>
> set nocount off
>
> set @MAX_KEY = 0
>
> UPDATE OJB_NEXTVAL_SEQ
> SET @MAX_KEY = MAX_KEY = MAX_KEY + 1
> WHERE SEQ_NAME = @SEQ_NAME
>
> -- return an error if
> -- sequence does not exist
> -- so we will know if someone
> -- truncates the table
> if @MAX_KEY = 0
> RAISERROR ('Sequence %s does not exists!', 16, 1, @SEQ_NAME)
> else
> select @MAX_KEY
>
> RETURN @MAX_KEY
>
>
> </code>
>
> I changed from SELECT 1/0 because "Division by zero error" (what
> procedure is giving the error?!?) is not so intuitive as "Sequence
> SQ_PERSON_ID does not exists!" (huh, that sequence is missing!).
>
> And don't forget to add to every trigger you write:
>
> on begginning of the trigger: SET NOCOUNT On
> on finish of the trigger: SET NOCOUNT Off
>
> Put your database in full recovery model, so you have transactional
> behaviour control (on simple recovery model things works badly - I'm a
> MS certified SQL professional, but I don't understand why it's not
> working: it should be!).
>
> And finally, put your app under
>
> useAutoCommit="2"
>
> (on jdbc-connection descriptor, this means "always set autoCommit(false)
> when open connection" - don't forget, your app became responsible to
> BeginTransactions and to CommitTransactions, otherwise they will be
> rolled back).
> This solved all my transactional problems when using PB API with SQL
> Server 2K + Service Pack 4 (SP4 is very important for this scenario -
> look bellow).
>
> History of status for each service pack:
>
> 1) With SP2, transactional problems are sparse: from times to times, got
> deadlock in database due very large views (!)
> 2) With SP3, transactional problems came frequent. Almost every
> operational with large tables lead to deadlocks. Performance came
> terrible (even when there is no deadlock). This two new "features" from
> SP3 made app almost ununsable.
> 3) With SP4, transactional problems are sparse, but autonumeration stop
> to work. Then above statements where executed (corrections on procedure
> and triggers), and in "useAutoCommit" attribute, plus SQL Server in full
> recovery model solved.
>
> Now, SQL Server is stable, running 3 medium sized databases (one for VB
> app, one for pure Servlets/JSP app, and one for a Swing/OJB app), each
> with about 1Gb of data.
>
> Thanks to you all, I expect this report helps anyone trying to work with
> SQL Server + OJB + autonumbering + several simultaneous users + heavy
> transactional control under PB API.
>
>
> Richter
>
>
>
> Edson Carlos Ericksson Richter escreveu:
>> Ok, guys. I think I discovered a piece of solution for this problem:
>>
>> 1) Database must be in Full recovery model
>> 2) Every trigger must start with SET NOCOUNT ON and end with SET
>> NOCOUNT OFF
>> 3) The OBJ_NEXTVAL_PROC should start with SET NOCOUNT OFF
>>
>> We are in testing fase right now, but appear the problem is solved.
>>
>> Thanks for tips (special do Armin, who was unique to respond :( ).
>> I'll let you know (during next week) if this really solved. If so,
>> I'll ask to add this notes to documentation, to avoid hours of
>> reserach to others in future.
>>
>>
>>
>> Best regards,
>>
>> Edson Richter
>>
>>
>
>
>
> ------------------------------------------------------------------------
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Procedure based sequencence generator repeating values (SOLVED)
Posted by Edson Carlos Ericksson Richter <ed...@mgrinformatica.com.br>.
Ok, this really solved. Final procedure code (works on SQL2K and SQL2K5):
<code>
CREATE PROCEDURE OJB_NEXTVAL_PROC
@SEQ_NAME varchar(150)
AS
declare @MAX_KEY BIGINT
set nocount off
set @MAX_KEY = 0
UPDATE OJB_NEXTVAL_SEQ
SET @MAX_KEY = MAX_KEY = MAX_KEY + 1
WHERE SEQ_NAME = @SEQ_NAME
-- return an error if
-- sequence does not exist
-- so we will know if someone
-- truncates the table
if @MAX_KEY = 0
RAISERROR ('Sequence %s does not exists!', 16, 1, @SEQ_NAME)
else
select @MAX_KEY
RETURN @MAX_KEY
</code>
I changed from SELECT 1/0 because "Division by zero error" (what
procedure is giving the error?!?) is not so intuitive as "Sequence
SQ_PERSON_ID does not exists!" (huh, that sequence is missing!).
And don't forget to add to every trigger you write:
on begginning of the trigger: SET NOCOUNT On
on finish of the trigger: SET NOCOUNT Off
Put your database in full recovery model, so you have transactional
behaviour control (on simple recovery model things works badly - I'm a
MS certified SQL professional, but I don't understand why it's not
working: it should be!).
And finally, put your app under
useAutoCommit="2"
(on jdbc-connection descriptor, this means "always set autoCommit(false)
when open connection" - don't forget, your app became responsible to
BeginTransactions and to CommitTransactions, otherwise they will be
rolled back).
This solved all my transactional problems when using PB API with SQL
Server 2K + Service Pack 4 (SP4 is very important for this scenario -
look bellow).
History of status for each service pack:
1) With SP2, transactional problems are sparse: from times to times, got
deadlock in database due very large views (!)
2) With SP3, transactional problems came frequent. Almost every
operational with large tables lead to deadlocks. Performance came
terrible (even when there is no deadlock). This two new "features" from
SP3 made app almost ununsable.
3) With SP4, transactional problems are sparse, but autonumeration stop
to work. Then above statements where executed (corrections on procedure
and triggers), and in "useAutoCommit" attribute, plus SQL Server in full
recovery model solved.
Now, SQL Server is stable, running 3 medium sized databases (one for VB
app, one for pure Servlets/JSP app, and one for a Swing/OJB app), each
with about 1Gb of data.
Thanks to you all, I expect this report helps anyone trying to work with
SQL Server + OJB + autonumbering + several simultaneous users + heavy
transactional control under PB API.
Richter
Edson Carlos Ericksson Richter escreveu:
> Ok, guys. I think I discovered a piece of solution for this problem:
>
> 1) Database must be in Full recovery model
> 2) Every trigger must start with SET NOCOUNT ON and end with SET
> NOCOUNT OFF
> 3) The OBJ_NEXTVAL_PROC should start with SET NOCOUNT OFF
>
> We are in testing fase right now, but appear the problem is solved.
>
> Thanks for tips (special do Armin, who was unique to respond :( ).
> I'll let you know (during next week) if this really solved. If so,
> I'll ask to add this notes to documentation, to avoid hours of
> reserach to others in future.
>
>
>
> Best regards,
>
> Edson Richter
>
>
Re: Procedure based sequencence generator repeating values (MAYBE
SOLVED)
Posted by Edson Carlos Ericksson Richter <ed...@mgrinformatica.com.br>.
Ok, guys. I think I discovered a piece of solution for this problem:
1) Database must be in Full recovery model
2) Every trigger must start with SET NOCOUNT ON and end with SET NOCOUNT OFF
3) The OBJ_NEXTVAL_PROC should start with SET NOCOUNT OFF
We are in testing fase right now, but appear the problem is solved.
Thanks for tips (special do Armin, who was unique to respond :( ). I'll
let you know (during next week) if this really solved. If so, I'll ask
to add this notes to documentation, to avoid hours of reserach to others
in future.
Best regards,
Edson Richter
Edson Carlos Ericksson Richter escreveu:
> Ok, guys: now, I'm lost!
>
> I changed auto-commit from 1 to 2, and now, appear OJB is trying to
> insert records twice - on every new object I store, I get primary key
> violation!
>
> I'm using PB API with MsSQL Server 2000 SP4 and JDBC Driver for MS SQL
> Server 2005 latest release. Someone knows what is going on? Any tips,
> please!!!
>
>
> Richter
>
>
>
> Edson Carlos Ericksson Richter escreveu:
>> Hi!
>>
>> I'm still with this problem.
>>
>> Could someone say if SequenceManagers use default connection present
>> at current thread?
>> Or it's executed inside same transaction (I don't know if this
>> implies same connection)?
>>
>>
>> Thanks for any clarifications!
>>
>> Richter
>>
>>
>> Edson Carlos Ericksson Richter escreveu:
>>> Armin Waibel escreveu:
>>>> Hi,
>>>>
>>>> I'm not a database expert, so please forbear with me ;-)
>>>> Edson Carlos Ericksson Richter wrote:
>>>>> Hi!
>>>>>
>>>>> There is a very long time since my last problem with OJB - thank
>>>>> you guys, it's awesome how good work was done until now.
>>>>> In last month, I was migrating from Oracle to MS SQL, and changed
>>>>> from Sequence Manager Oracle native to Procedure based on MS SQL.
>>>>> Worked great on tests, but when in production, sometimes procedure
>>>>> returns same value for two users, overriding content of child
>>>>> tables (it override child tables, then throw duplicate primary key
>>>>> exception for parent table but changes on child tables are not
>>>>> being rolled back).
>>>>
>>>> Are you sure that the changes written to database or could it be a
>>>> caching problem?
>>> Well, independent of being a cache problem, I gettin records
>>> overwritten, what is bad per se.
>>>>
>>>>
>>>>> I'm sure I'm beginning and rollbacking transactions, and opening
>>>>> and closing connections (i never had this problem with Oracle, by
>>>>> example).
>>>>
>>>> Did you made concurrency tests against
>>>> SequenceManagerStoredProcedureImpl? In OJB test-suite you can find
>>>> a test case called SequenceManagerTest. This test case include
>>>> concurrency sequence generation tests.
>>>> Is the issue reproduceable via unit tests?
>>> No, I've made no tests... I tried community before (withou much
>>> luck, until now :D )
>>>
>>>>
>>>> In SequenceManagerStoredProcedureImpl source code I can't find
>>>> critical sections. So I assume it's a MSSQL concurrency problem. Does
>>>> UPDATE OJB_NEXTVAL_SEQ...
>>>> exclusive lock the table row?
>>> Well, I was supposing it is. But after reading SP4 fixlist for
>>> SQL2000, I start having doubts... So, I'll try to increase to
>>> REPETEABLE READ as default transaction level, and see what happens
>>> (god save our souls, because REPETEABLE READ almost always gives
>>> problems of deadlocking with SQL Server).
>>>
>>> I appreciate your comments, and I'll expect sequence gerenator
>>> author give some comments (I really appreciate if thats possible)...
>>>
>>>
>>> Thanks,
>>>
>>> Edson Richter
>>>
>>>
>>> ------------------------------------------------------------------------
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>> ------------------------------------------------------------------------
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
> ------------------------------------------------------------------------
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Procedure based sequencence generator repeating values
Posted by Edson Carlos Ericksson Richter <ed...@mgrinformatica.com.br>.
Ok, guys: now, I'm lost!
I changed auto-commit from 1 to 2, and now, appear OJB is trying to
insert records twice - on every new object I store, I get primary key
violation!
I'm using PB API with MsSQL Server 2000 SP4 and JDBC Driver for MS SQL
Server 2005 latest release. Someone knows what is going on? Any tips,
please!!!
Richter
Edson Carlos Ericksson Richter escreveu:
> Hi!
>
> I'm still with this problem.
>
> Could someone say if SequenceManagers use default connection present
> at current thread?
> Or it's executed inside same transaction (I don't know if this implies
> same connection)?
>
>
> Thanks for any clarifications!
>
> Richter
>
>
> Edson Carlos Ericksson Richter escreveu:
>> Armin Waibel escreveu:
>>> Hi,
>>>
>>> I'm not a database expert, so please forbear with me ;-)
>>> Edson Carlos Ericksson Richter wrote:
>>>> Hi!
>>>>
>>>> There is a very long time since my last problem with OJB - thank
>>>> you guys, it's awesome how good work was done until now.
>>>> In last month, I was migrating from Oracle to MS SQL, and changed
>>>> from Sequence Manager Oracle native to Procedure based on MS SQL.
>>>> Worked great on tests, but when in production, sometimes procedure
>>>> returns same value for two users, overriding content of child
>>>> tables (it override child tables, then throw duplicate primary key
>>>> exception for parent table but changes on child tables are not
>>>> being rolled back).
>>>
>>> Are you sure that the changes written to database or could it be a
>>> caching problem?
>> Well, independent of being a cache problem, I gettin records
>> overwritten, what is bad per se.
>>>
>>>
>>>> I'm sure I'm beginning and rollbacking transactions, and opening
>>>> and closing connections (i never had this problem with Oracle, by
>>>> example).
>>>
>>> Did you made concurrency tests against
>>> SequenceManagerStoredProcedureImpl? In OJB test-suite you can find a
>>> test case called SequenceManagerTest. This test case include
>>> concurrency sequence generation tests.
>>> Is the issue reproduceable via unit tests?
>> No, I've made no tests... I tried community before (withou much luck,
>> until now :D )
>>
>>>
>>> In SequenceManagerStoredProcedureImpl source code I can't find
>>> critical sections. So I assume it's a MSSQL concurrency problem. Does
>>> UPDATE OJB_NEXTVAL_SEQ...
>>> exclusive lock the table row?
>> Well, I was supposing it is. But after reading SP4 fixlist for
>> SQL2000, I start having doubts... So, I'll try to increase to
>> REPETEABLE READ as default transaction level, and see what happens
>> (god save our souls, because REPETEABLE READ almost always gives
>> problems of deadlocking with SQL Server).
>>
>> I appreciate your comments, and I'll expect sequence gerenator author
>> give some comments (I really appreciate if thats possible)...
>>
>>
>> Thanks,
>>
>> Edson Richter
>>
>>
>> ------------------------------------------------------------------------
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
> ------------------------------------------------------------------------
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Procedure based sequencence generator repeating values
Posted by Edson Carlos Ericksson Richter <ed...@mgrinformatica.com.br>.
Hi!
I'm still with this problem.
Could someone say if SequenceManagers use default connection present at
current thread?
Or it's executed inside same transaction (I don't know if this implies
same connection)?
Thanks for any clarifications!
Richter
Edson Carlos Ericksson Richter escreveu:
> Armin Waibel escreveu:
>> Hi,
>>
>> I'm not a database expert, so please forbear with me ;-)
>> Edson Carlos Ericksson Richter wrote:
>>> Hi!
>>>
>>> There is a very long time since my last problem with OJB - thank you
>>> guys, it's awesome how good work was done until now.
>>> In last month, I was migrating from Oracle to MS SQL, and changed
>>> from Sequence Manager Oracle native to Procedure based on MS SQL.
>>> Worked great on tests, but when in production, sometimes procedure
>>> returns same value for two users, overriding content of child tables
>>> (it override child tables, then throw duplicate primary key
>>> exception for parent table but changes on child tables are not being
>>> rolled back).
>>
>> Are you sure that the changes written to database or could it be a
>> caching problem?
> Well, independent of being a cache problem, I gettin records
> overwritten, what is bad per se.
>>
>>
>>> I'm sure I'm beginning and rollbacking transactions, and opening and
>>> closing connections (i never had this problem with Oracle, by example).
>>
>> Did you made concurrency tests against
>> SequenceManagerStoredProcedureImpl? In OJB test-suite you can find a
>> test case called SequenceManagerTest. This test case include
>> concurrency sequence generation tests.
>> Is the issue reproduceable via unit tests?
> No, I've made no tests... I tried community before (withou much luck,
> until now :D )
>
>>
>> In SequenceManagerStoredProcedureImpl source code I can't find
>> critical sections. So I assume it's a MSSQL concurrency problem. Does
>> UPDATE OJB_NEXTVAL_SEQ...
>> exclusive lock the table row?
> Well, I was supposing it is. But after reading SP4 fixlist for
> SQL2000, I start having doubts... So, I'll try to increase to
> REPETEABLE READ as default transaction level, and see what happens
> (god save our souls, because REPETEABLE READ almost always gives
> problems of deadlocking with SQL Server).
>
> I appreciate your comments, and I'll expect sequence gerenator author
> give some comments (I really appreciate if thats possible)...
>
>
> Thanks,
>
> Edson Richter
>
>
> ------------------------------------------------------------------------
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Procedure based sequencence generator repeating values
Posted by Edson Carlos Ericksson Richter <ed...@mgrinformatica.com.br>.
Armin Waibel escreveu:
> Hi,
>
> I'm not a database expert, so please forbear with me ;-)
> Edson Carlos Ericksson Richter wrote:
>> Hi!
>>
>> There is a very long time since my last problem with OJB - thank you
>> guys, it's awesome how good work was done until now.
>> In last month, I was migrating from Oracle to MS SQL, and changed
>> from Sequence Manager Oracle native to Procedure based on MS SQL.
>> Worked great on tests, but when in production, sometimes procedure
>> returns same value for two users, overriding content of child tables
>> (it override child tables, then throw duplicate primary key exception
>> for parent table but changes on child tables are not being rolled back).
>
> Are you sure that the changes written to database or could it be a
> caching problem?
Well, independent of being a cache problem, I gettin records
overwritten, what is bad per se.
>
>
>> I'm sure I'm beginning and rollbacking transactions, and opening and
>> closing connections (i never had this problem with Oracle, by example).
>
> Did you made concurrency tests against
> SequenceManagerStoredProcedureImpl? In OJB test-suite you can find a
> test case called SequenceManagerTest. This test case include
> concurrency sequence generation tests.
> Is the issue reproduceable via unit tests?
No, I've made no tests... I tried community before (withou much luck,
until now :D )
>
> In SequenceManagerStoredProcedureImpl source code I can't find
> critical sections. So I assume it's a MSSQL concurrency problem. Does
> UPDATE OJB_NEXTVAL_SEQ...
> exclusive lock the table row?
Well, I was supposing it is. But after reading SP4 fixlist for SQL2000,
I start having doubts... So, I'll try to increase to REPETEABLE READ as
default transaction level, and see what happens (god save our souls,
because REPETEABLE READ almost always gives problems of deadlocking with
SQL Server).
I appreciate your comments, and I'll expect sequence gerenator author
give some comments (I really appreciate if thats possible)...
Thanks,
Edson Richter
Re: Procedure based sequencence generator repeating values
Posted by Armin Waibel <ar...@apache.org>.
Hi,
I'm not a database expert, so please forbear with me ;-)
Edson Carlos Ericksson Richter wrote:
> Hi!
>
> There is a very long time since my last problem with OJB - thank you
> guys, it's awesome how good work was done until now.
> In last month, I was migrating from Oracle to MS SQL, and changed from
> Sequence Manager Oracle native to Procedure based on MS SQL. Worked
> great on tests, but when in production, sometimes procedure returns same
> value for two users, overriding content of child tables (it override
> child tables, then throw duplicate primary key exception for parent
> table but changes on child tables are not being rolled back).
Are you sure that the changes written to database or could it be a
caching problem?
> I'm sure
> I'm beginning and rollbacking transactions, and opening and closing
> connections (i never had this problem with Oracle, by example).
Did you made concurrency tests against
SequenceManagerStoredProcedureImpl? In OJB test-suite you can find a
test case called SequenceManagerTest. This test case include concurrency
sequence generation tests.
Is the issue reproduceable via unit tests?
In SequenceManagerStoredProcedureImpl source code I can't find critical
sections. So I assume it's a MSSQL concurrency problem. Does
UPDATE OJB_NEXTVAL_SEQ...
exclusive lock the table row?
regards,
Armin
>
> I was thinking if the problem wasn't default transaction level for MS
> SQL being different from Oracle (and MaxDB, that I used before getting
> in nervous with constant crashes/bad performance...).
>
> Someone has faced similar problems?
>
> Thanks,
>
>
> Edson Richter
>
>
>
> ------------------------------------------------------------------------
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org