search

ibmdb / ruby-ibmdb

Rails Adapter/Ruby driver for IBM DB2 and IBM Informix
42 stars 49 forks source link

Query hangs from db2 with no any exception return #71

Closed asdffly77 closed 6 years ago

asdffly77 commented 7 years ago

Problem Description ubuntu server 14.04 ruby 2.2.4, rails 4.2.4 ,ibm_db2 gem 3.0.4, when use connect.where(ajbs:'255700000516944').limit(1).order("zdbh desc").first to get data from db2,the query was hanging, and no return just hanging there forever , but i run the same code query on my local machine the query can return nil form db2 whith no hangs,my local machine environment is mac os rails 4.2.4,ruby 2.3.0 ,ibm_db2 gem 3.0.4

hi-nakamura commented 7 years ago

I hope you will forgive me that I'm not good at English. I also have the same problem. Please refer to following information.

environment

centos 6.8, ruby 2.2.4, rails 4.2.7.1, ibm_db2 gem 3.0.4

situation

Hang up occasionally when running rake test

investigation result

hung up code

Asset.find_by(xxxxx: 'AAAAAA').try(:yyyyy) -> IBM_DB.fetch_array()

snapshot(Excerpt)

  Database name                              = XXDBTEST
  Database path                              = /home/db2inst1/xxdbtest/db2inst1/NODE0000/SQL00001/MEMBER0000/
  Input database alias                       = XXDBTEST
  Locks held                                 = 2
  Applications currently connected           = 3
  Agents currently waiting on locks          = 0
  Snapshot timestamp                         = 03/06/2017 06:34:15.672194

  Application handle                         = 7530
  Application ID                             = 127.0.0.1.58097.170306060717
  Sequence number                            = 08016
  Application name                           = rake test -v
  CONNECT Authorization ID                   = DB2INST1
  Application status                         = UOW Waiting
  Status change time                         = Not Collected
  Application code page                      = 1208
  Locks held                                 = 1
  Total wait time (ms)                       = Not Collected

  List Of Locks
   Lock Name                   = 0x5359534C564C3031DDECEF28C1
   Lock Attributes             = 0x00000000
   Release Flags               = 0x40000000
   Lock Count                  = 1
   Hold Count                  = 0
   Lock Object Name            = 0
   Object Type                 = Internal Plan Lock
   Mode                        = S

db2pd

$ db2pd -db xxdbtest -lock -transactions -applications -dynamic

Database Member 0 -- Database XXDBTEST -- Active -- Up 2 days 03:11:11 -- Date 2017-03-06-07.28.31.042303

Locks:
Address            TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att        ReleaseFlg rrIID
0x00007F027DA06600 3          5359534C564C3031DDECEF28C1 PlanLock   ..S  G   3          1   0          0x00000000 0x40000000 0     
0x00007F027DCF6280 13         5359534C564C3031DDECEF28C1 PlanLock   ..S  G   13         1   0          0x00000000 0x40000000 0     

Database Member 0 -- Database XXDBTEST -- Active -- Up 2 days 03:11:11 -- Date 2017-03-06-07.28.31.042363

Transactions:
Address            AppHandl [nod-index] TranHdl    Locks      State   Tflag      Tflag2     Firstlsn           Lastlsn            Firstlso             Lastlso              SpaceReserved   LogSpace        TID            AxRegCnt   GXID     ClientUserID                   ClientWrkstnName               ClientApplName                 ClientAccntng                  
0x00007F027CCC7F00 4257     [000-04257] 3          1          READ    0x00000000 0x00000000 0x0000000000000000 0x00000000007608A5 0                    0                    0               0               0x000000103D93 1          0        n/a                            vagrant-centos65.vagrantup.com n/a                            n/a                            
0x00007F027CCC9A00 4258     [000-04258] 4          0          READ    0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                    0                    0               0               0x0000001022D8 1          0        n/a                            n/a                            n/a                            n/a                            
0x00007F027CCCB500 4259     [000-04259] 5          0          READ    0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                    0                    0               0               0x0000001384C4 1          0        n/a                            n/a                            n/a                            n/a                            
0x00007F027CCCD000 4260     [000-04260] 6          0          READ    0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                    0                    0               0               0x0000001022DA 1          0        n/a                            n/a                            n/a                            n/a                            
0x00007F027CCCEB00 4261     [000-04261] 7          0          READ    0x00000000 0x00000020 0x0000000000000000 0x000000000097C721 0                    0                    0               0               0x000000135FF0 1          0        n/a                            n/a                            n/a                            n/a                            
0x00007F027CCD0600 4262     [000-04262] 8          0          READ    0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                    0                    0               0               0x0000001022DC 1          0        n/a                            n/a                            n/a                            n/a                            
0x00007F027CCD2100 4263     [000-04263] 9          0          READ    0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                    0                    0               0               0x0000001022DD 1          0        n/a                            n/a                            n/a                            n/a                            
0x00007F027CCD3C00 4264     [000-04264] 10         0          READ    0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                    0                    0               0               0x0000001022E0 1          0        n/a                            n/a                            db2evml_DB2DETAILDEADLOCK      n/a                            
0x00007F027CCD5700 7380     [000-07380] 11         0          READ    0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0                    0                    0               0               0x000000126100 1          0        n/a                            n/a                            n/a                            n/a                            
0x00007F027CCD8D00 7530     [000-07530] 13         1          READ    0x00000000 0x00000000 0x0000000000000000 0x00000000009836D8 0                    0                    0               0               0x000000138304 1          0        n/a                            vagrant-centos65.vagrantup.com n/a                            n/a                            

Database Member 0 -- Database XXDBTEST -- Active -- Up 2 days 03:11:11 -- Date 2017-03-06-07.28.31.042523

Applications:
Address            AppHandl [nod-index] NumAgents  CoorEDUID  Status                  C-AnchID C-StmtUID  L-AnchID L-StmtUID  Appid                                                            WorkloadID  WorkloadOccID CollectActData          CollectActPartition     CollectSectionActuals  
0x00000002042D0080 4263     [000-04263] 1          3125       ConnectCompleted        0        0          0        0          *LOCAL.DB2.170304041740                                          0           0             N                       C                       N  
0x0000000204320080 4262     [000-04262] 1          3124       ConnectCompleted        0        0          0        0          *LOCAL.DB2.170304041739                                          0           0             N                       C                       N  
0x00000002042A0080 7380     [000-07380] 1          3127       UOW-Waiting             0        0          553      22         *LOCAL.db2inst1.170306052009                                     1           37            N                       C                       N  
0x0000000203560080 4261     [000-04261] 1          3123       ConnectCompleted        0        0          0        0          *LOCAL.DB2.170304041738                                          0           0             N                       C                       N  
0x00000002045E0080 7530     [000-07530] 1          3129       UOW-Waiting             0        0          425      37         127.0.0.1.58097.170306060717                                     1           65            N                       C                       N  
0x00000002043C0080 4260     [000-04260] 1          3122       ConnectCompleted        0        0          0        0          *LOCAL.DB2.170304041737                                          0           0             N                       C                       N  
0x0000000203590080 4259     [000-04259] 1          3121       ConnectCompleted        0        0          0        0          *LOCAL.DB2.170304041736                                          0           0             N                       C                       N  
0x00000002037D0080 4258     [000-04258] 1          3120       ConnectCompleted        0        0          0        0          *LOCAL.DB2.170304041735                                          0           0             N                       C                       N  
0x0000000204370080 4264     [000-04264] 1          3126       ConnectCompleted        0        0          0        0          *LOCAL.DB2.170304041741                                          0           0             N                       C                       N  
0x0000000204430080 4257     [000-04257] 1          2797       UOW-Waiting             0        0          425      37         127.0.0.1.57924.170304041720                                     1           1             N                       C                       N  

External Connection Attributes
Address            AppHandl [nod-index] ClientIPAddress                          EncryptionLvl SystemAuthID                                                                                                                    
0x00000002042D0080 4263     [000-04263] n/a                                      None          DB2INST1                                                                                                                        
0x0000000204320080 4262     [000-04262] n/a                                      None          DB2INST1                                                                                                                        
0x00000002042A0080 7380     [000-07380] n/a                                      None          DB2INST1                                                                                                                        
0x0000000203560080 4261     [000-04261] n/a                                      None          DB2INST1                                                                                                                        
0x00000002045E0080 7530     [000-07530] 127.0.0.1                                None          DB2INST1                                                                                                                        
0x00000002043C0080 4260     [000-04260] n/a                                      None          DB2INST1                                                                                                                        
0x0000000203590080 4259     [000-04259] n/a                                      None          DB2INST1                                                                                                                        
0x00000002037D0080 4258     [000-04258] n/a                                      None          DB2INST1                                                                                                                        
0x0000000204370080 4264     [000-04264] n/a                                      None          DB2INST1                                                                                                                        
0x0000000204430080 4257     [000-04257] 127.0.0.1                                None          DB2INST1                                                                                                                        

Trusted Connection Attributes
Address            AppHandl [nod-index] TrustedContext                                                                                                                   ConnTrustType                RoleInherited                                                                                                                   
0x00000002042D0080 4263     [000-04263] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204320080 4262     [000-04262] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x00000002042A0080 7380     [000-07380] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000203560080 4261     [000-04261] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x00000002045E0080 7530     [000-07530] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x00000002043C0080 4260     [000-04260] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000203590080 4259     [000-04259] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x00000002037D0080 4258     [000-04258] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204370080 4264     [000-04264] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204430080 4257     [000-04257] n/a                                                                                                                              non trusted                  n/a                                                                                                                             

Autonomous Routine Connections
Address            AppHandl [nod-index] Status          Autonomous Routine Handl [nod-index] Status                 

Anonymous Block Connections
Address            AppHandl [nod-index] Status          Anonymous Block Handl [nod-index] Status                 

Database Member 0 -- Database XXDBTEST -- Active -- Up 2 days 03:11:11 -- Date 2017-03-06-07.28.31.043751

Dynamic Cache:
Current Memory Used           739068
Total Heap Size               1271398
Cache Overflow Flag           0
Number of References          284420
Number of Statement Inserts   95632
Number of Statement Deletes   95620
Number of Variation Inserts   98566
Number of Statements          12

Dynamic SQL Statements:
Address            AnchID StmtUID    NumEnv     NumVar     NumRef     NumExe     Text 
0x00007F0282F97C00 164    34         1          1          16         16         SELECT COLNAME, TYPENAME FROM SYSCAT.COLUMNS WHERE TABNAME='POLICY' AND TABSCHEMA='SYSTOOLS'
0x00007F0282DC4BA0 168    1          0          0          49836      49836      SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
0x00007F0282D7FAE0 310    76         1          1          96         32         SELECT STATS_FLAG FROM SYSTOOLS.HMON_ATM_INFO WHERE SCHEMA = ? AND NAME = ? AND CREATE_TIME = ? WITH UR
0x00007F0282F94720 422    71         1          1          15         5          SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE TBSPACEID = ? AND TABLEID = ? AND CREATE_TIME = ? AND TYPE <> 'H' WITH UR
0x00007F0282FEF000 425    37         0          0          9888       5151       SELECT  ASSET.* FROM ASSET WHERE ASSET.xxxxx = ? FETCH FIRST 1 ROWS ONLY
0x00007F0282D4A0C0 450    104        1          1          16         16         SELECT TRIGNAME FROM SYSCAT.TRIGGERS WHERE TABNAME='POLICY' AND TABSCHEMA='SYSTOOLS'
0x00007F0282D7EC60 553    22         0          0          1          1          values current timestamp
0x00007F0282D7F540 554    25         0          0          37         37         DELETE FROM XX_CCCC_DDDD
0x00007F0283137F80 580    32         1          1          16         16         CALL SYSPROC.SYSINSTALLOBJECTS('POLICY','V','','')
0x00007F0282D4C980 760    1282       0          0          1          1          DELETE FROM XX_EEEEE
0x00007F0282F94500 809    78         1          1          47         47         SET CURRENT LOCK TIMEOUT 5
0x00007F0282DCF900 1014   59         1          1          16         16         SELECT POLICY FROM SYSTOOLS.POLICY WHERE MED='DB2CommonMED' AND DECISION='NOP' AND NAME='CommonPolicy'

Dynamic SQL Environments:
Address            AnchID StmtUID    EnvID      Iso QOpt Blk
0x00007F0282DCF440 164    34         1          CS  5    B
0x00007F0282DC7300 310    76         1          UR  5    B
0x00007F0282DCC1C0 422    71         1          UR  5    B
0x00007F0282D4A260 450    104        1          CS  5    B
0x00007F02831380A0 580    32         1          CS  5    B
0x00007F0282F96DC0 809    78         1          UR  5    B
0x00007F0282DCFAA0 1014   59         1          CS  5    B

Dynamic SQL Variations:
Address            AnchID StmtUID    EnvID      VarID      NumRef     Typ Lockname                   Val Insert Time                Sect Size  Num Copies
0x00007F0283130080 164    34         1          1          16         6   220000000100000001008014D6 Y   2017-03-06-06.11.11.744324 7360       1     
0x00007F0282DC75E0 310    76         1          1          96         6   4C000000010000000100C026D6 Y   2017-03-06-06.11.11.809726 7928       1     
0x00007F0282DC2500 422    71         1          1          15         6   47000000010000000100C034D6 Y   2017-03-06-06.11.13.621142 8432       1     
0x00007F0282F94940 450    104        1          1          16         6   680000000100000001004038D6 Y   2017-03-06-06.11.11.764216 7080       1     
0x00007F0282D72500 580    32         1          1          16         13  200000000100000001008048D6 Y   2017-03-06-06.11.11.692942 6192       1     
0x00007F0282DC0080 809    78         1          1          47         9   4E0000000100000001002065D6 Y   2017-03-06-06.11.11.789476 2152       1     
0x00007F0282F90080 1014   59         1          1          16         6   3B000000010000000100C07ED6 Y   2017-03-06-06.11.11.772063 6808       1
asdffly77 commented 7 years ago

@hi-nakamura i had fix this issues, i upgrade ruby version from 2.2.4 to 2.3.0 and some other versions.just do not use 2.2.4.try it

hi-nakamura commented 7 years ago

Thank you. I tried Ruby 2.3.0. but was the same result

hi-nakamura commented 7 years ago

unicorn pstack of the same phenomenon

Thread 2 (Thread 0x7f5317118700 (LWP 7286)):
#0  0x00007f5316162283 in poll () from /lib64/libc.so.6
#1  0x00007f531728cb59 in thread_timer ()
#2  0x00007f5316ce5aa1 in start_thread () from /lib64/libpthread.so.0
#3  0x00007f531616baad in clone () from /lib64/libc.so.6
Thread 1 (Thread 0x7f5317109700 (LWP 7284)):
#0  0x00007f5316ce968c in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x00007f531728bb53 in gvl_acquire ()
#2  0x00007f531728d046 in blocking_region_end ()
#3  0x00007f531728f1eb in call_without_gvl ()
#4  0x00007f53087ea75b in ibm_Ruby_Thread_Call () from /var/www/vendor/bundle/ruby/2.2.0/extensions/x86_64-linux/2.2.0-static/ibm_db-3.0.3/ibm_db.so
#5  0x00007f53087ebab2 in ibm_db_fetch_array () from /var/www/vendor/bundle/ruby/2.2.0/extensions/x86_64-linux/2.2.0-static/ibm_db-3.0.3/ibm_db.so
#6  0x00007f531726773f in vm_call_cfunc ()
#7  0x00007f531726c93d in vm_exec_core ()
#8  0x00007f531727130f in vm_exec ()
#9  0x00007f5317274b7f in invoke_block_from_c ()
#10 0x00007f5317275995 in rb_yield ()
#11 0x00007f5317299a97 in rb_ary_each ()
#12 0x00007f531726773f in vm_call_cfunc ()
#13 0x00007f531726b3b6 in vm_exec_core ()
#14 0x00007f531727130f in vm_exec ()
#15 0x00007f5317274b7f in invoke_block_from_c ()
#16 0x00007f5317274f18 in vm_invoke_proc ()
#17 0x00007f53172750fa in rb_vm_invoke_proc ()
#18 0x00007f531714c2ed in proc_call ()
#19 0x00007f531726773f in vm_call_cfunc ()
#20 0x00007f531726c93d in vm_exec_core ()
#21 0x00007f531727130f in vm_exec ()
#22 0x00007f5317274b7f in invoke_block_from_c ()
#23 0x00007f5317274f18 in vm_invoke_proc ()
#24 0x00007f53172750fa in rb_vm_invoke_proc ()
#25 0x00007f531714c2ed in proc_call ()
#26 0x00007f531726773f in vm_call_cfunc ()
#27 0x00007f531726c93d in vm_exec_core ()
#28 0x00007f531727130f in vm_exec ()
#29 0x00007f5317274b7f in invoke_block_from_c ()
#30 0x00007f5317274f18 in vm_invoke_proc ()
#31 0x00007f53172750fa in rb_vm_invoke_proc ()
#32 0x00007f531714c2ed in proc_call ()
#33 0x00007f531726773f in vm_call_cfunc ()
#34 0x00007f531726c93d in vm_exec_core ()
#35 0x00007f531727130f in vm_exec ()
#36 0x00007f5317274b7f in invoke_block_from_c ()
#37 0x00007f5317275995 in rb_yield ()
#38 0x00007f5317299a97 in rb_ary_each ()
#39 0x00007f531726773f in vm_call_cfunc ()
#40 0x00007f531726b3b6 in vm_exec_core ()
#41 0x00007f531727130f in vm_exec ()
#42 0x00007f5317274b7f in invoke_block_from_c ()
#43 0x00007f5317275789 in catch_i ()
#44 0x00007f5317263b98 in rb_catch_protect ()
#45 0x00007f5317263cf0 in rb_f_catch ()
#46 0x00007f531726773f in vm_call_cfunc ()
#47 0x00007f531726b3b6 in vm_exec_core ()
#48 0x00007f531727130f in vm_exec ()
#49 0x00007f531727e81e in rb_iseq_eval ()
#50 0x00007f5317145ffb in rb_load_internal0 ()
#51 0x00007f531714704b in rb_f_load ()
#52 0x00007f531726773f in vm_call_cfunc ()
#53 0x00007f531727c733 in vm_call_method ()
#54 0x00007f531726c93d in vm_exec_core ()
#55 0x00007f531727130f in vm_exec ()
#56 0x00007f531727e81e in rb_iseq_eval ()
#57 0x00007f5317145ffb in rb_load_internal0 ()
#58 0x00007f531714704b in rb_f_load ()
#59 0x00007f531726773f in vm_call_cfunc ()
#60 0x00007f531727c733 in vm_call_method ()
#61 0x00007f531726c93d in vm_exec_core ()
#62 0x00007f531727130f in vm_exec ()
#63 0x00007f531727e81e in rb_iseq_eval ()
#64 0x00007f5317145ffb in rb_load_internal0 ()
#65 0x00007f531714704b in rb_f_load ()
#66 0x00007f531726773f in vm_call_cfunc ()
#67 0x00007f531727c733 in vm_call_method ()
#68 0x00007f531726c93d in vm_exec_core ()
#69 0x00007f531727130f in vm_exec ()
#70 0x00007f5317271be1 in rb_iseq_eval_main ()
#71 0x00007f5317142697 in ruby_exec_internal ()
#72 0x00007f5317145b86 in ruby_run_node ()
#73 0x00007f531714042b in main ()
hi-nakamura commented 7 years ago

I confirmed that it reproduced under the following two conditions.

・Perform file upload with ruby on rails application ・Refer to the table where DECIMAL type exists