I have got one scenario where I need to insert 16k records in a DB table. So along with normal DB batch insert I have created Callable task which will take up respective batch(size of 500 records) and will do insertion in independent manner. I am curious to know how underlying database will take these requests. Does database locking at page level will block rest of java threads until first thread with batch of 500 records get committed?
-
What database are you using?Zim-Zam O'Pootertoot– Zim-Zam O'Pootertoot2015-12-05 16:26:25 +00:00Commented Dec 5, 2015 at 16:26
-
@Zim-ZamO'Pootertoot sybaseShashi Shankar– Shashi Shankar2015-12-05 20:15:49 +00:00Commented Dec 5, 2015 at 20:15
Add a comment
|
2 Answers
My answer is for Sybase ASE. For Sybase IQ see Guillaume's answer.
Does database locking at page level will block rest of java threads until first thread with batch of 500 records get committed?
That depends on what locking granularity you have set. According to Sybase's doc, there are three locking granularities:
- Allpages locking, which locks datapages and index pages
- Datapages locking, which locks only the data pages
- Datarows locking, which locks only the data rows
So, if you select Allpages your threads will block until the current batch gets committed. Otherwise, your threads will not block, but will naturally incur a higher locking overhead.
For full details on Sybase ASE's locking granularity, see this documentation.
Comments
From Sybase IQ's documentation:
Sybase allows multiple readers, but only one writer to a table.
So, unless you open and close the transaction for every row you insert (which will be slow), your Threads will have to wait until one transaction closes to start a new one.
2 Comments
Andy Guibert
can you provide a link to that doc? I'm curious because Sybase does have db/table/row level locking.