143 reputation
19
bio website linkedin.com/in/dkatsubo
location The Netherlands
age 34
visits member for 2 years, 5 months
seen Oct 7 '13 at 10:39

Fun, make fun of everything!


Nov
6
awarded  Notable Question
May
9
awarded  Popular Question
Oct
7
comment Parallel inserts cause deadlock on DB2
In my question in code block there is {{create table}} statement. From it you can see that there is only one index (for primary key). Unfortunately I cannot drop primary key for bulk inserts, as I want DB to check the consistency. Also it makes everything complicated: how do I decide which transaction drops the index and which re-enables it?
Sep
11
awarded  Popular Question
Mar
8
awarded  Scholar
Mar
8
awarded  Supporter
Mar
8
accepted CREATE INDEX for long columns
Nov
19
comment Parallel inserts cause deadlock on DB2
Thanks for reply. I cannot test it though as we have migrated to MS SQL. My feeling is that it won't help: DB2 will wait for LOCKTIMEOUT and fail anyway. And LOCKSIZE TABLE is certainly not an option. Parameters LOCKTIMEOUT and DLCHKTIME just delay the convulsion before death. However if you can run the scenario over your instance of DB2 I will appreciate. I can send you the test script (Java).
Oct
19
awarded  Commentator
Oct
19
comment Parallel inserts cause deadlock on DB2
You mean MS SQL 2008 has specific WITH NOLOCK option? I think this is very specific option, at least I don't know anything similar in MySQL, HSQL and DB2, which I would like to stay compatible with (otherwise I would need to support several dialects). MS SQL handles the test scenario very well without this option :)
Oct
19
comment Parallel inserts cause deadlock on DB2
What NOLOCK means in other DB you refer to? Give me a link please.
Oct
19
awarded  Editor
Oct
19
revised Parallel inserts cause deadlock on DB2
added 410 characters in body
Oct
19
comment Parallel inserts cause deadlock on DB2
Yes, it exists. But how exactly it solves the problem? These are two separate transactions, as far as I know, it is not possible to create a savepoint and fork two "subtransactions" to rollback both of them in case of error (in one of them). More over, if it would be possible and both transactions are rolledback, they will hit the same problem when they are re-run, as problem is 100% reproducible.
Oct
12
comment Parallel inserts cause deadlock on DB2
@ChrisAldrich: Have a look at DDL statement. The table has only two columns, no extra indices (only PK). I don't have a admin access to DB, but out DBA says that locks are exhausted.
Sep
26
comment Parallel inserts cause deadlock on DB2
Where to you see a deadlock (check SQL log again)? MySQL managed to complete the test application in 4 seconds. Another run with 10 parallel threads, each inserted 1.000.000 lines (uhh, tough), completed in 1 hour.
Sep
25
asked Parallel inserts cause deadlock on DB2
Jun
26
comment CREATE INDEX for long columns
You have a good point here, because MySQL does not follow SQL standard recommendations. However id integer generated by default as identity works perfectly OK for HSQL and DB2. And on SQL Server as you said. Not bad?
Jun
26
awarded  Student
Jun
26
comment CREATE INDEX for long columns
Thanks for links. If I have varchar(20), does it count as 20 (ASCII) bytes or 40 bytes (UTF-16), or ...? I have page table size 4K which fits well all columns (otherwise I would get error at CREATE TABLE). What would be the solution in my case?