[MDEV-13754] TRANSACTION sometimes stay in "KILLLED' STATE for inifinitve time! - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Incomplete
Affects Version/s: 10.2.8
Fix Version/s: N/A
Component/s: OTHER
Labels:
- need_feedback

Description

Hi dudes, after upgrade from mariadb 10.1 to 10.2.8, some simple transaction is running for infinitive times...

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where Time > 9000\G

I have 19 transaction that is running for this time

they won't die with kill command..

           ID: 10245887

         USER: novid

         HOST:

           DB: api

      COMMAND: Killed

         TIME: 106767

        STATE: NULL

         INFO: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')

      TIME_MS: 106767343.552

        STAGE: 0

    MAX_STAGE: 0

     PROGRESS: 0.000

  MEMORY_USED: 92848

EXAMINED_ROWS: 0

     QUERY_ID: 300183941

  INFO_BINARY: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')

          TID: 15109

and with SHOW ENGINE INNODB STATUS\G

---TRANSACTION 46385651188, ACTIVE 107004 sec

8 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 4

MySQL thread id 10245887, OS thread handle 0x7f898dcb6700, query id 300183941 api

insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')

Trx read view will not see trx with id >= 46385651191, sees < 46385650307

changing in these transaction is so small (2 or 3) so waiting is not for transaction rollback.

Thread stay in killed state and so some record is lock for infinitive time

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

42003.gdb
97 kB
2017-10-03 12:09
43205.gdb
114 kB
2017-10-03 12:09
after.gdb
92 kB
2017-10-02 22:42
after-restart.gdb
67 kB
2017-10-03 00:14
before.gdb
92 kB
2017-10-02 22:42
before-restart.gdb
90 kB
2017-10-03 00:14
innodb-status
48 kB
2017-10-03 00:14
my.cnf
6 kB
2017-09-08 08:39

Issue Links

relates to

MDEV-11896 thd_get_error_context_description race condition

Closed

MDEV-13983 Mariadb becomes unresponsive

Closed

Activity

Ascending order - Click to sort in descending order

novid created issue - 2017-09-07 10:53

novid made changes - 2017-09-07 10:55

Field	Original Value	New Value
Description	Hi dudes, after upgrade from mariadb 10.1 to 10.2.8, some simple transaction is running for infinitive times... SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where Time > 9000\G I have 19 transaction that is running for this time :( they won't die with kill command.. ************************* 19. row ************************* ID: 10245887 USER: amghezi HOST: api-sv10.snapp:20934 DB: base-api COMMAND: Killed TIME: 106767 STATE: NULL INFO: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04') TIME_MS: 106767343.552 STAGE: 0 MAX_STAGE: 0 PROGRESS: 0.000 MEMORY_USED: 92848 EXAMINED_ROWS: 0 QUERY_ID: 300183941 INFO_BINARY: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04') TID: 15109 and with SHOW ENGINE INNODB STATUS\G ---TRANSACTION 46385651188, ACTIVE 107004 sec 8 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 4 MySQL thread id 10245887, OS thread handle 0x7f898dcb6700, query id 300183941 api-sv10.snapp 172.16.77.123 amghezi insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04') Trx read view will not see trx with id >= 46385651191, sees < 46385650307 changing in these transaction is so small (2 or 3) so waiting is not for transaction rollback. Thread stay in killed state and so some record is lock for infinitive time :(	Hi dudes, after upgrade from mariadb 10.1 to 10.2.8, some simple transaction is running for infinitive times... SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where Time > 9000\G I have 19 transaction that is running for this time :( they won't die with kill command.. ID: 10245887 USER: amghezi HOST: api-sv10.snapp:20934 DB: base-api COMMAND: Killed TIME: 106767 STATE: NULL INFO: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04') TIME_MS: 106767343.552 STAGE: 0 MAX_STAGE: 0 PROGRESS: 0.000 MEMORY_USED: 92848 EXAMINED_ROWS: 0 QUERY_ID: 300183941 INFO_BINARY: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04') TID: 15109 and with SHOW ENGINE INNODB STATUS\G ---TRANSACTION 46385651188, ACTIVE 107004 sec 8 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 4 MySQL thread id 10245887, OS thread handle 0x7f898dcb6700, query id 300183941 api-sv10.snapp 172.16.77.123 amghezi insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04') Trx read view will not see trx with id >= 46385651191, sees < 46385650307 changing in these transaction is so small (2 or 3) so waiting is not for transaction rollback. Thread stay in killed state and so some record is lock for infinitive time :(

novid made changes - 2017-09-07 10:57

Description

Hi dudes, after upgrade from mariadb 10.1 to 10.2.8, some simple transaction is running for infinitive times...

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where Time > 9000\G

I have 19 transaction that is running for this time :(

they won't die with kill command..

           ID: 10245887
         USER: amghezi
         HOST: api-sv10.snapp:20934
           DB: base-api
      COMMAND: Killed
         TIME: 106767
        STATE: NULL
         INFO: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
      TIME_MS: 106767343.552
        STAGE: 0
    MAX_STAGE: 0
     PROGRESS: 0.000
  MEMORY_USED: 92848
EXAMINED_ROWS: 0
     QUERY_ID: 300183941
  INFO_BINARY: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
          TID: 15109

and with SHOW ENGINE INNODB STATUS\G

---TRANSACTION 46385651188, ACTIVE 107004 sec
8 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 4
MySQL thread id 10245887, OS thread handle 0x7f898dcb6700, query id 300183941 api-sv10.snapp 172.16.77.123 amghezi
insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
Trx read view will not see trx with id >= 46385651191, sees < 46385650307

changing in these transaction is so small (2 or 3) so waiting is not for transaction rollback.

Thread stay in killed state and so some record is lock for infinitive time :(

Hi dudes, after upgrade from mariadb 10.1 to 10.2.8, some simple transaction is running for infinitive times...

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where Time > 9000\G

I have 19 transaction that is running for this time :(

they won't die with kill command..

           ID: 10245887
         USER: amghezi
         HOST:
           DB: base-api
      COMMAND: Killed
         TIME: 106767
        STATE: NULL
         INFO: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
      TIME_MS: 106767343.552
        STAGE: 0
    MAX_STAGE: 0
     PROGRESS: 0.000
  MEMORY_USED: 92848
EXAMINED_ROWS: 0
     QUERY_ID: 300183941
  INFO_BINARY: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
          TID: 15109

and with SHOW ENGINE INNODB STATUS\G

---TRANSACTION 46385651188, ACTIVE 107004 sec
8 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 4
MySQL thread id 10245887, OS thread handle 0x7f898dcb6700, query id 300183941 api-sv10.snapp 172.16.77.123 amghezi
insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
Trx read view will not see trx with id >= 46385651191, sees < 46385650307

changing in these transaction is so small (2 or 3) so waiting is not for transaction rollback.

Thread stay in killed state and so some record is lock for infinitive time :(

novid made changes - 2017-09-07 10:58

Description

Hi dudes, after upgrade from mariadb 10.1 to 10.2.8, some simple transaction is running for infinitive times...

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where Time > 9000\G

I have 19 transaction that is running for this time :(

they won't die with kill command..

           ID: 10245887
         USER: amghezi
         HOST:
           DB: base-api
      COMMAND: Killed
         TIME: 106767
        STATE: NULL
         INFO: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
      TIME_MS: 106767343.552
        STAGE: 0
    MAX_STAGE: 0
     PROGRESS: 0.000
  MEMORY_USED: 92848
EXAMINED_ROWS: 0
     QUERY_ID: 300183941
  INFO_BINARY: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
          TID: 15109

and with SHOW ENGINE INNODB STATUS\G

---TRANSACTION 46385651188, ACTIVE 107004 sec
8 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 4
MySQL thread id 10245887, OS thread handle 0x7f898dcb6700, query id 300183941 api-sv10.snapp 172.16.77.123 amghezi
insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
Trx read view will not see trx with id >= 46385651191, sees < 46385650307

changing in these transaction is so small (2 or 3) so waiting is not for transaction rollback.

Thread stay in killed state and so some record is lock for infinitive time :(

Hi dudes, after upgrade from mariadb 10.1 to 10.2.8, some simple transaction is running for infinitive times...

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where Time > 9000\G

I have 19 transaction that is running for this time :(

they won't die with kill command..

           ID: 10245887
         USER: novid
         HOST:
           DB: api
      COMMAND: Killed
         TIME: 106767
        STATE: NULL
         INFO: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
      TIME_MS: 106767343.552
        STAGE: 0
    MAX_STAGE: 0
     PROGRESS: 0.000
  MEMORY_USED: 92848
EXAMINED_ROWS: 0
     QUERY_ID: 300183941
  INFO_BINARY: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
          TID: 15109

and with SHOW ENGINE INNODB STATUS\G

---TRANSACTION 46385651188, ACTIVE 107004 sec
8 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 4
MySQL thread id 10245887, OS thread handle 0x7f898dcb6700, query id 300183941 api-sv10.snapp 172.16.77.123 amghezi
insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
Trx read view will not see trx with id >= 46385651191, sees < 46385650307

changing in these transaction is so small (2 or 3) so waiting is not for transaction rollback.

Thread stay in killed state and so some record is lock for infinitive time :(

novid made changes - 2017-09-07 11:01

Description

Hi dudes, after upgrade from mariadb 10.1 to 10.2.8, some simple transaction is running for infinitive times...

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where Time > 9000\G

I have 19 transaction that is running for this time :(

they won't die with kill command..

           ID: 10245887
         USER: novid
         HOST:
           DB: api
      COMMAND: Killed
         TIME: 106767
        STATE: NULL
         INFO: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
      TIME_MS: 106767343.552
        STAGE: 0
    MAX_STAGE: 0
     PROGRESS: 0.000
  MEMORY_USED: 92848
EXAMINED_ROWS: 0
     QUERY_ID: 300183941
  INFO_BINARY: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
          TID: 15109

and with SHOW ENGINE INNODB STATUS\G

---TRANSACTION 46385651188, ACTIVE 107004 sec
8 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 4
MySQL thread id 10245887, OS thread handle 0x7f898dcb6700, query id 300183941 api-sv10.snapp 172.16.77.123 amghezi
insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
Trx read view will not see trx with id >= 46385651191, sees < 46385650307

changing in these transaction is so small (2 or 3) so waiting is not for transaction rollback.

Thread stay in killed state and so some record is lock for infinitive time :(

Hi dudes, after upgrade from mariadb 10.1 to 10.2.8, some simple transaction is running for infinitive times...

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where Time > 9000\G

I have 19 transaction that is running for this time :(

they won't die with kill command..

           ID: 10245887
         USER: novid
         HOST:
           DB: api
      COMMAND: Killed
         TIME: 106767
        STATE: NULL
         INFO: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
      TIME_MS: 106767343.552
        STAGE: 0
    MAX_STAGE: 0
     PROGRESS: 0.000
  MEMORY_USED: 92848
EXAMINED_ROWS: 0
     QUERY_ID: 300183941
  INFO_BINARY: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
          TID: 15109

and with SHOW ENGINE INNODB STATUS\G

---TRANSACTION 46385651188, ACTIVE 107004 sec
8 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 4
MySQL thread id 10245887, OS thread handle 0x7f898dcb6700, query id 300183941 api
insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
Trx read view will not see trx with id >= 46385651191, sees < 46385650307

changing in these transaction is so small (2 or 3) so waiting is not for transaction rollback.

Thread stay in killed state and so some record is lock for infinitive time :(

Elena Stepanova made changes - 2017-09-07 12:23

Description

Hi dudes, after upgrade from mariadb 10.1 to 10.2.8, some simple transaction is running for infinitive times...

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where Time > 9000\G

I have 19 transaction that is running for this time :(

they won't die with kill command..

           ID: 10245887
         USER: novid
         HOST:
           DB: api
      COMMAND: Killed
         TIME: 106767
        STATE: NULL
         INFO: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
      TIME_MS: 106767343.552
        STAGE: 0
    MAX_STAGE: 0
     PROGRESS: 0.000
  MEMORY_USED: 92848
EXAMINED_ROWS: 0
     QUERY_ID: 300183941
  INFO_BINARY: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
          TID: 15109

and with SHOW ENGINE INNODB STATUS\G

---TRANSACTION 46385651188, ACTIVE 107004 sec
8 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 4
MySQL thread id 10245887, OS thread handle 0x7f898dcb6700, query id 300183941 api
insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
Trx read view will not see trx with id >= 46385651191, sees < 46385650307

changing in these transaction is so small (2 or 3) so waiting is not for transaction rollback.

Thread stay in killed state and so some record is lock for infinitive time :(

Hi dudes, after upgrade from mariadb 10.1 to 10.2.8, some simple transaction is running for infinitive times...

{code:sql}
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where Time > 9000\G
{code}

I have 19 transaction that is running for this time :(

they won't die with kill command..

{noformat}
           ID: 10245887
         USER: novid
         HOST:
           DB: api
      COMMAND: Killed
         TIME: 106767
        STATE: NULL
         INFO: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
      TIME_MS: 106767343.552
        STAGE: 0
    MAX_STAGE: 0
     PROGRESS: 0.000
  MEMORY_USED: 92848
EXAMINED_ROWS: 0
     QUERY_ID: 300183941
  INFO_BINARY: insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
          TID: 15109
{noformat}

and with SHOW ENGINE INNODB STATUS\G

{noformat}
---TRANSACTION 46385651188, ACTIVE 107004 sec
8 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 4
MySQL thread id 10245887, OS thread handle 0x7f898dcb6700, query id 300183941 api
insert into `driver_status_changelog` (`status`, `driver_id`, `updated_at`, `created_at`) values ('4', '65767', '2017-09-06 09:22:04', '2017-09-06 09:22:04')
Trx read view will not see trx with id >= 46385651191, sees < 46385650307
{noformat}

changing in these transaction is so small (2 or 3) so waiting is not for transaction rollback.

Thread stay in killed state and so some record is lock for infinitive time :(

Elena Stepanova made changes - 2017-09-07 12:26

Labels

need_feedback

novid made changes - 2017-09-08 08:39

Attachment

my.cnf [ 44185 ]

Elena Stepanova made changes - 2017-09-08 10:50

Labels

need_feedback

Elena Stepanova made changes - 2017-10-01 17:35

Labels

need_feedback

novid made changes - 2017-10-02 22:42

Attachment

before.gdb [ 44275 ]

novid made changes - 2017-10-02 22:42

Attachment

after.gdb [ 44276 ]

Elena Stepanova made changes - 2017-10-02 23:28

Labels

need_feedback

novid made changes - 2017-10-03 00:14

Attachment

before-restart.gdb [ 44277 ]

novid made changes - 2017-10-03 00:14

Attachment

after-restart.gdb [ 44278 ]

novid made changes - 2017-10-03 00:14

Attachment

innodb-status [ 44279 ]

novid made changes - 2017-10-03 12:09

Attachment

42003.gdb [ 44280 ]

novid made changes - 2017-10-03 12:09

Attachment

43205.gdb [ 44281 ]

Sergei Golubchik made changes - 2017-12-25 11:30

Priority

Critical [ 2 ]

Major [ 3 ]

Marko Mäkelä made changes - 2020-07-31 03:54

Link

This issue relates to ~~MDEV-11896~~ [ ~~MDEV-11896~~ ]

Marko Mäkelä made changes - 2020-07-31 03:54

Link

This issue relates to ~~MDEV-13983~~ [ ~~MDEV-13983~~ ]

Marko Mäkelä made changes - 2020-07-31 03:54

Labels

need_feedback

Elena Stepanova made changes - 2020-08-28 15:27

Component/s		OTHER [ 10125 ]
Fix Version/s		N/A [ 14700 ]
Resolution		Incomplete [ 4 ]
Status	Open [ 1 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:45

Workflow

MariaDB v3 [ 82487 ]

MariaDB v4 [ 152783 ]

People

Assignee:: Unassigned

Reporter:: novid

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2017-09-07 10:53

Updated:: 2020-08-28 15:27

Resolved:: 2020-08-28 15:27

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration