Make the sleep a random number between 1 and 5 or 10 just to get things spaced out more.
If your app can do this without the generated by default constraint then I would have to consider that there is an issue with this constraint's implementation. It would be cause for a jira entry ... (We've had discussions about this constraint before...) Sent via BlackBerry. -Mike Segel Principal MSCC 312 952 8175 -----Original Message----- From: Igor Minar <[EMAIL PROTECTED]> Date: Mon, 19 Feb 2007 19:28:56 To:"Derby Discussion" <[email protected]> Subject: Re: Multiple transactions and unexpected permanent row lock in SYSCOLUMNS table This happens even when only 10 threads are inserting data into that table. I tried putting each thread to sleep for 1sec after each insert but the results were the same. I tried to change the id column to GENERATED BY DEFAULT and was generating ids in my app and that "solved" the problem. I don't think that there is anything wrong in my app (but everybody says that :-) ) I did my best to comment out everything I could and just keep the basic logic for inserting stuff into db and the problem was still there. cheers, i On Feb 19, 2007, at 2:53 PM, <[EMAIL PROTECTED]> wrote: > Something isn't right. > > You shouldn't be locking up unless Derby can't handle 100 simultaneous > connections all pounding the database at the same time. > > Have you tried putting some random sleeps in between the > transactions on > different threads? > > I'm assuming you're trying to simulate a load and test of Derby. > > The other issue is that it could be your code as well. > >> -----Original Message----- >> From: Igor Minar >> Sent: Saturday, February 17, 2007 6:19 PM >> To: Derby Discussion >> Subject: Re: Multiple transactions and unexpected permanent row >> lock in >> SYSCOLUMNS table >> >> >> On Feb 18, 2007, at 1:10 AM, Michael Segel wrote: >> >>> Making a big assumption... >>> >>> If each thread has its own connection, you will be blocked on your >>> identity column. >> >> Yes, each thread has its own connection retrieved from >> ClientConnectionPoolDataSource >> >>> But you shouldn't be blocked longer than it takes to get the >>> identity value. >> >> That's what I'm expecting as well, but it doesn't seem to be the >> case. >> >>> 100 threads w n records per transaction? >> >> Number of records per transaction is configurable. I was testing the >> app with 500-800 records per commit. >> >>> How much memory have you allocated to your app? >> >> -Xms10m -Xmx200m >> >> but I hardly ever need more than 16MB >> >>> And is the app on the same machine as derby? Same jvm? >> >> same machine, using networked mode = different JVM >> >> cheers, >> i >> >>> >>> Hth >>> Sent via BlackBerry. >>> >>> -Mike Segel >>> Principal >>> MSCC >>> 312 952 8175 >>> >>> >>> -----Original Message----- >>> From: Igor Minar >>> Date: Sun, 18 Feb 2007 00:34:06 >>> To:[email protected] >>> Subject: Multiple transactions and unexpected permanent row lock in >>> SYSCOLUMNS table >>> >>> Hi! >>> >>> After hours and hours of debugging I'm still seeing something that I >>> can't explain. Can anyone help please? >>> >>> I have multiple threads (10-100 threads) simultaneously inserting >>> records into table A. Each thread has its own transaction and >>> commits >>> only when all the records allocated for this thread are inserted. DB >>> isolation level is the default. >>> >>> CREATE TABLE A ( >>> id bigint NOT NULL GENERATED ALWAYS AS >>> IDENTITY >>> (START WITH 1, INCREMENT BY 1), >>> extra_col bigint NOT NULL, >>> CONSTRAINT pk_records PRIMARY KEY(id) >>> ); >>> >>> INSERT INTO A(extra_col) VALUES (?); >>> >>> >>> Everything is fine until one of the threads inserts 2nd-5th >>> record in >>> its transaction (this number varies) as the first thread out of all >>> of the threads running. >>> >>> By doing that, for some to me unknown reason it acquires some >>> "special" lock on and blocks all the other threads that try to >>> insert >>> into this table. >>> >>> From there on the app continues as a single threaded because all >>> the >>> other threads are blocked. If the thread manages to finish importing >>> all the records into db before the time out for the blocked threads, >>> one of the blocked threads acquires this "special" lock and this >>> continues until all the threads are finished. If the blocked threads >>> time out, an exception is thrown: >>> >>> ERROR 40XL2: A lock could not be obtained within the time requested. >>> The lockTable dump is: >>> XID |TYPE |MODE|LOCKCOUNT| >>> LOCKNAME >>> |STATE|TABLETYPE / LOCKOBJ |INDEXNAME / >>> CONTAINER_ID / (MODE for LATCH only) |TABLENAME / >>> CONGLOM_ID | >>> -------------------------------------------------------------------- >>> -- >>> -- >>> -------------------------------------------------------------------- >>> -- >>> -- >>> -------------------------------------------------------------------- >>> -- >>> -- >>> ---------------------------------------- >>> *** The following row is the victim *** >>> 8666203 |ROW |X |0 | >>> (5,639) >>> |WAIT |S | >>> NULL | >>> SYSCOLUMNS | >>> *** The above row is the victim *** >>> 8666254 |ROW |X |144 | >>> (5,639) >>> |GRANT|S | >>> NULL | >>> SYSCOLUMNS | >>> 8666246 |ROW |X |0 | >>> (5,639) >>> |WAIT |S | >>> NULL | >>> SYSCOLUMNS | >>> 8666254 |ROW |X |1 | >>> (4757,10) >>> |GRANT|T | >>> NULL | >>> RECORDS | >>> 8666254 |ROW |X |1 | >>> (4755,13) >>> |GRANT|T | >>> NULL | >>> RECORDS | >>> 8666254 |ROW |X |1 | >>> (4757,11) >>> |GRANT|T | >>> NULL | >>> RECORDS | >>> 8666254 |ROW |X |1 | >>> (4755,14) >>> |GRANT|T | >>> NULL |RECORDS >>> >>> >>> >>> So it seems that the running thread is blocking all the other >>> threads >>> on table SYSCOLUMNS. I suppose that this is because of the A.id >>> column that is generated from SYSCOLUMNS table, however what is the >>> reason for this row to be permanently locked by this thread? >>> >>> This is what the output from my log looks like: >>> >>> timestamp | threadname | log >>> 1171751801235 Thread-2 - saved record 3 >>> 1171751801236 Thread-5 - saved record 4 >>> 1171751801238 Thread-6 - saved record 5 >>> 1171751801240 Thread-4 - saved record 2 >>> 1171751801240 Thread-7 - saved record 6 >>> 1171751801241 Thread-8 - saved record 7 >>> 1171751801243 Thread-9 - saved record 8 >>> 1171751801244 Thread-10 - saved record 9 >>> 1171751801245 Thread-11 - saved record 10 >>> 1171751801247 Thread-3 - saved record 1 >>> 1171751802244 Thread-2 - saved record 11 >>> 1171751802255 Thread-5 - saved record 12 >>> 1171751802257 Thread-6 - saved record 13 >>> 1171751802258 Thread-4 - saved record 14 >>> 1171751802260 Thread-7 - saved record 15 >>> 1171751802266 Thread-8 - saved record 16 >>> 1171751802266 Thread-9 - saved record 17 >>> 1171751802267 Thread-10 - saved record 18 >>> 1171751802267 Thread-11 - saved record 19 >>> 1171751802268 Thread-3 - saved record 20 >>> 1171751803246 Thread-2 - saved record 21 >>> 1171751803257 Thread-5 - saved record 22 >>> 1171751803261 Thread-6 - saved record 23 >>> 1171751803262 Thread-4 - saved record 24 >>> 1171751803263 Thread-7 - saved record 25 >>> 1171751804265 Thread-7 - saved record 35 >>> 1171751805267 Thread-7 - saved record 36 >>> 1171751806269 Thread-7 - saved record 37 >>> 1171751807272 Thread-7 - saved record 38 >>> 1171751808273 Thread-7 - saved record 39 >>> ... >>> ... >>> 1171751827315 Thread-7 - saved record 138 >>> 1171751828316 Thread-7 - saved record 139 >>> ... >>> ... >>> >>> Thread7 locked the relevant SYSCOLUMNS row and doesn't let any other >>> thread acquire lock on that row. >>> >>> >>> If I don't run all inserts within a thread as one transaction and >>> leave autocommit on, I see this: >>> 1171754035170 Thread-3 - saved record 2 >>> 1171754035172 Thread-6 - saved record 5 >>> 1171754035172 Thread-2 - saved record 1 >>> 1171754035173 Thread-4 - saved record 3 >>> 1171754035173 Thread-5 - saved record 4 >>> 1171754035175 Thread-9 - saved record 8 >>> 1171754035177 Thread-8 - saved record 7 >>> 1171754035177 Thread-11 - saved record 10 >>> 1171754035179 Thread-10 - saved record 9 >>> 1171754035180 Thread-7 - saved record 6 >>> 1171754036238 Thread-3 - saved record 11 >>> 1171754036319 Thread-4 - saved record 12 >>> 1171754036321 Thread-6 - saved record 14 >>> 1171754036322 Thread-5 - saved record 13 >>> 1171754036326 Thread-8 - saved record 15 >>> 1171754036327 Thread-11 - saved record 16 >>> 1171754036327 Thread-9 - saved record 17 >>> 1171754036328 Thread-7 - saved record 18 >>> 1171754036328 Thread-10 - saved record 19 >>> 1171754036329 Thread-2 - saved record 20 >>> 1171754037241 Thread-3 - saved record 21 >>> 1171754037322 Thread-4 - saved record 22 >>> 1171754037324 Thread-6 - saved record 23 >>> 1171754037325 Thread-5 - saved record 24 >>> 1171754037329 Thread-8 - saved record 25 >>> 1171754037331 Thread-9 - saved record 27 >>> 1171754037331 Thread-11 - saved record 26 >>> 1171754037331 Thread-7 - saved record 28 >>> 1171754037333 Thread-2 - saved record 30 >>> ... >>> ... >>> >>> Threads are equally competing for privilege to insert record into db >>> and none of them is dominant. >>> This is exactly what I'd like to see with the autocommit set to off. >>> Am I missing something? Is there a way how to achieve that? >>> >>> Thanks for help. >>> >>> Igor >>> >>> derby: v10.2.2.0 >>> OS: MacOS Intel >>> java version "1.6.0-dp" >>> Java(TM) SE Runtime Environment (build 1.6.0-dp-b88-34) >>> Java HotSpot(TM) Client VM (build 1.6.0-b88-17-release, mixed mode, >>> sharing) >>> >>> >>> > > >
