Only one PB from second database
Hi, I have a strange behaviour about the second database i use. It seems that using broker = PersistenceBrokerFactory.createPersistenceBroker(rushDb); always return the same broker/connection. My connection pool is setup as it have to keep 2 idle connections available, and it never occured. Still only one. How can i use several connection in this case? Note that this database is not not use to update datas. No transaction are used on it. Thanks. Here's my connection setup. jdbc-connection-descriptor jcd-alias=rushDb default-connection=false platform=MsSQLServer jdbc-level=2.0 driver=com.microsoft.jdbc.sqlserver.SQLServerDriver protocol=JDBC subprotocol=microsoft:sqlserver dbalias=//xxx.x.x.x:1433 username= password= batch-mode=true useAutoCommit=0 ignoreAutoCommitExceptions=true and pool setup : maxActive=5 maxIdle=-1 minIdle=2 maxWait=5000 whenExhaustedAction=2 validationQuery=SELECT CURRENT_TIMESTAMP testOnBorrow=true testOnReturn=false testWhileIdle=true timeBetweenEvictionRunsMillis=6 numTestsPerEvictionRun=2 minEvictableIdleTimeMillis=180 removedAbandonned=false removeAbandonedTimeout=300 logAbandoned=true
Re: Only one PB from second database
Hi Bruno, Bruno CROS wrote: Hi, I have a strange behaviour about the second database i use. It seems that using broker = PersistenceBrokerFactory.createPersistenceBroker(rushDb); always return the same broker/connection. My connection pool is setup as it have to keep 2 idle connections available, and it never occured. Still only one. How can i use several connection in this case? Note that this database is not not use to update datas. No transaction are used on it. how do you test this behavior? Please setup a test and lookup for two PB instances at the same time: broker_A = PersistenceBrokerFactory.createPersistenceBroker(rushDb); broker_B = PersistenceBrokerFactory.createPersistenceBroker(rushDb); Are A and B really the same broker instances? If you execute a query on both broker instances (don't close the broker after it) and then lookup the Connection from A and B - are the connections the same? regards, Armin Thanks. Here's my connection setup. jdbc-connection-descriptor jcd-alias=rushDb default-connection=false platform=MsSQLServer jdbc-level=2.0 driver=com.microsoft.jdbc.sqlserver.SQLServerDriver protocol=JDBC subprotocol=microsoft:sqlserver dbalias=//xxx.x.x.x:1433 username= password= batch-mode=true useAutoCommit=0 ignoreAutoCommitExceptions=true and pool setup : maxActive=5 maxIdle=-1 minIdle=2 maxWait=5000 whenExhaustedAction=2 validationQuery=SELECT CURRENT_TIMESTAMP testOnBorrow=true testOnReturn=false testWhileIdle=true timeBetweenEvictionRunsMillis=6 numTestsPerEvictionRun=2 minEvictableIdleTimeMillis=180 removedAbandonned=false removeAbandonedTimeout=300 logAbandoned=true - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Only one PB from second database
Hi Armin, In fact, i looked at the DB connections in the DB console. It was a bad idea, because connection disappear !! I looked with netstat -a , and i saw several sockets/connections... Well, i was experiencing some freezes with these connections with a pool setup maxActive set to -1. I didn't find any documentation on that value. What i known is that, when i put 0 (no limit), it seems there is no more freeze. Can you ligth up me about that. Thanks. Regards. On 5/5/06, Armin Waibel [EMAIL PROTECTED] wrote: Hi Bruno, Bruno CROS wrote: Hi, I have a strange behaviour about the second database i use. It seems that using broker = PersistenceBrokerFactory.createPersistenceBroker(rushDb); always return the same broker/connection. My connection pool is setup as it have to keep 2 idle connections available, and it never occured. Still only one. How can i use several connection in this case? Note that this database is not not use to update datas. No transaction are used on it. how do you test this behavior? Please setup a test and lookup for two PB instances at the same time: broker_A = PersistenceBrokerFactory.createPersistenceBroker(rushDb); broker_B = PersistenceBrokerFactory.createPersistenceBroker(rushDb); Are A and B really the same broker instances? If you execute a query on both broker instances (don't close the broker after it) and then lookup the Connection from A and B - are the connections the same? regards, Armin Thanks. Here's my connection setup. jdbc-connection-descriptor jcd-alias=rushDb default-connection=false platform=MsSQLServer jdbc-level=2.0 driver=com.microsoft.jdbc.sqlserver.SQLServerDriver protocol=JDBC subprotocol=microsoft:sqlserver dbalias=//xxx.x.x.x:1433 username= password= batch-mode=true useAutoCommit=0 ignoreAutoCommitExceptions=true and pool setup : maxActive=5 maxIdle=-1 minIdle=2 maxWait=5000 whenExhaustedAction=2 validationQuery=SELECT CURRENT_TIMESTAMP testOnBorrow=true testOnReturn=false testWhileIdle=true timeBetweenEvictionRunsMillis=6 numTestsPerEvictionRun=2 minEvictableIdleTimeMillis=180 removedAbandonned=false removeAbandonedTimeout=300 logAbandoned=true - 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):
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]
Avoiding Persistence Broker Leak
Hi Armin, Thanks for the idea to detect broker leak. It will show some bad coded methods, even they have been checked : commit never reached, broker not closed... no commit/abort !!! (find one, arghh ) Meanwhile, there was still some open broker detected. When i look into code, i found some old methods that were reading objects, with a dedicated transaction. I known now that this transaction is not necessary, and I know now it's even unwanted ! It seems to burn connections/brokers. So i add a little check to my getTransaction() method. Now, it searches for a current transaction, and il found, throw a Already open transaction. This let us detect the standalone update method (opening and closing Transaction), who are called inside an already open Transaction (as the old bad reads methods was called by update methods). Everything gets ok now. May be it can be an developpment setup to avoid broker leak due to the double opening Transaction (with same broker) Thanks a lot. Again. Regards
Re: Avoiding Persistence Broker Leak
I've used a similar solution, but when I get a broker, first I check if
one broker was already taken for this thread (and a usage counter is
incremented).
Then, when I start one operation, I just check if there is not already a
transaction. If there is no transaction, then I open one. Finally, when
I ask to close the broker, a usage counter for current thread is
decremented, and if it's zero, then broker is really closed.
This technique allow:
- Cross object transactions in same thread
- Avoid begin more than one transaction per broker
- Obligate to always open one transaction, what guarantee standard
behaviour independent of developer personal preferences (important for
groups). So, I can reuse a component written by another programmer
because I know if he execute some operation in database, I'll be in same
transaction.
- When no more object is using a broker, the broker is automatically
closed.
Resuming, all my code finish in one class that is responsible to take a
broker, start a transaction (if needed), execute operation, and close
broker (if there is no more objects using it, of course).
When I execute one operation, I delegate to Action method to start
transaction, commit or rollback. So, every action in my code has
following structure:
public void actionPerformed(ActionEvent evt) {
try {
MyPersonBrokerUC.beginTransaction(); // starts a transaction and
increments usage (to 1) for this thread
MyPersonBrokerUC.store(somePerson); // detect if is a insert or an
update (increments usage to 2) and does the job (return broker and
decrements to 1 again). Will use same broker and transaction started above
OtherPersonUC.dealWithNewPersons(somePerson); // will run under same
transaction (increments usage to 2, execute operation, and decrements to
1 again). I don't even need to know if there is a bunch of another calls
inside this method: all will run under same transaction.
MyPersonBrokerUC.commitTransaction(); // commit the transaction and
decrements usage (to 0, so broker is really closed)
} catch(Exception e) {
MyPersonBrokerUC.rollbackTransaction(); // rollback the transaction
and decrements usage (to 0, so broker is really closed)
DebugUtil.handleException(e);
}
}
UC (use cases) classes never begin, commit or rollback: it's a Action
task. Because a task always execute under unique thread, there is not
problems (if you wish to execute async operation, just start transaction
inside new thread). Works for MVC-Web development (a Servlet or a JSP
will be the action in this case).
Thanks to try...catch structure, there is no way to forget a broker
open, neither a transaction open.
Only one cons for this: when debugging, don't try to fix and continue,
because you will get broken brokers and transactions, leading to dead
lock and fatally to stop and restart.
OT: humm, trying to explain just in words this appear to be really
complicated, but in fact, it isn't. May be sometime I get spare time to
create some nice Sequence and Collaboration diagrams to explain this
Best regards,
Edson Richter
Bruno CROS escreveu:
Hi Armin,
Thanks for the idea to detect broker leak. It will show some bad coded
methods, even they have been checked : commit never reached, broker not
closed... no commit/abort !!! (find one, arghh )
Meanwhile, there was still some open broker detected. When i look into
code, i found some old methods that were reading objects, with a
dedicated
transaction. I known now that this transaction is not necessary, and I
know
now it's even unwanted ! It seems to burn connections/brokers.
So i add a little check to my getTransaction() method. Now, it searches
for a current transaction, and il found, throw a Already open
transaction. This let us detect the standalone update method
(opening and
closing Transaction), who are called inside an already open
Transaction (as
the old bad reads methods was called by update methods). Everything
gets ok
now.
May be it can be an developpment setup to avoid broker leak due to the
double opening Transaction (with same broker)
Thanks a lot. Again.
Regards
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
