db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: Multiple transactions and unexpected permanent row lock in SYSCOLUMNS table
Date Mon, 19 Feb 2007 13:53:32 GMT
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 [mailto:igor.minar@gmail.com]
> 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 <iiminar@gmail.com>
> > Date: Sun, 18 Feb 2007 00:34:06
> > To:derby-user@db.apache.org
> > 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)
> >
> >
> >




Mime
View raw message