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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
