Re: Procedure based sequencence generator repeating values (SOLVED)
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): 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 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Procedure based sequencence generator repeating values (SOLVED)
Ok, this really solved. Final procedure code (works on SQL2K and SQL2K5): 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 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Procedure based sequencence generator repeating values (MAYBE SOLVED)
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Procedure based sequencence generator repeating values
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Procedure based sequencence generator repeating values
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Procedure based sequencence generator repeating values
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Procedure based sequencence generator repeating values
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]