Skip to content

Instantly share code, notes, and snippets.

@scottfrazer

scottfrazer/queries.md

Last active April 1, 2016 18:07
Show Gist options
  • Save scottfrazer/75135d65f24e974f2fab15a61e4fff21 to your computer and use it in GitHub Desktop.
Save scottfrazer/75135d65f24e974f2fab15a61e4fff21 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
queries.md

Session 1 (or any session)

DELETE FROM FAILURE_EVENT;
DELETE FROM RUNTIME_ATTRIBUTES;
DELETE FROM EXECUTION_INFO;
DELETE FROM SYMBOL;
DELETE FROM WORKFLOW_EXECUTION_AUX;
DELETE FROM EXECUTION_EVENT;
DELETE FROM EXECUTION;
DELETE FROM WORKFLOW_EXECUTION;

ALTER TABLE FAILURE_EVENT AUTO_INCREMENT = 1;
ALTER TABLE RUNTIME_ATTRIBUTES AUTO_INCREMENT = 1;
ALTER TABLE EXECUTION_INFO AUTO_INCREMENT = 1;
ALTER TABLE SYMBOL AUTO_INCREMENT = 1;
ALTER TABLE WORKFLOW_EXECUTION_AUX AUTO_INCREMENT = 1;
ALTER TABLE EXECUTION_EVENT AUTO_INCREMENT = 1;
ALTER TABLE EXECUTION AUTO_INCREMENT = 1;
ALTER TABLE WORKFLOW_EXECUTION AUTO_INCREMENT = 1;

SET autocommit=0;
insert into `WORKFLOW_EXECUTION` (`WORKFLOW_EXECUTION_UUID`,`WORKFLOW_NAME`,`STATUS`,`START_DT`,`END_DT`)  values ('3db8b664-177d-40e7-9176-0f1e0e05be7a','w','Submitted','2016-04-01 09:09:25.322',null);
insert into `WORKFLOW_EXECUTION_AUX` (`WORKFLOW_EXECUTION_ID`,`WDL_SOURCE`,`JSON_INPUTS`,`WORKFLOW_OPTIONS`)  values (1,'task a {command{}}\nworkflow w {\n  call a\n  call a as b\n  call a as c\n}\n          ','{}','{}');
insert into `EXECUTION` (`WORKFLOW_EXECUTION_ID`,`CALL_FQN`,`IDX`,`STATUS`,`RC`,`START_DT`,`END_DT`,`BACKEND_TYPE`,`ALLOWS_RESULT_REUSE`,`DOCKER_IMAGE_HASH`,`RESULTS_CLONED_FROM`,`EXECUTION_HASH`,`ATTEMPT`)  values (1,'w.a',-1,'NotStarted',null,null,null,'Local',1,null,null,null,1);
insert into `EXECUTION` (`WORKFLOW_EXECUTION_ID`,`CALL_FQN`,`IDX`,`STATUS`,`RC`,`START_DT`,`END_DT`,`BACKEND_TYPE`,`ALLOWS_RESULT_REUSE`,`DOCKER_IMAGE_HASH`,`RESULTS_CLONED_FROM`,`EXECUTION_HASH`,`ATTEMPT`)  values (1,'w.b',-1,'NotStarted',null,null,null,'Local',1,null,null,null,1);
insert into `EXECUTION` (`WORKFLOW_EXECUTION_ID`,`CALL_FQN`,`IDX`,`STATUS`,`RC`,`START_DT`,`END_DT`,`BACKEND_TYPE`,`ALLOWS_RESULT_REUSE`,`DOCKER_IMAGE_HASH`,`RESULTS_CLONED_FROM`,`EXECUTION_HASH`,`ATTEMPT`)  values (1,'w.c',-1,'NotStarted',null,null,null,'Local',1,null,null,null,1);
commit;

Session 1

SET autocommit=0;

Session 2

SET autocommit=0;

Session 1

insert into `RUNTIME_ATTRIBUTES` (`EXECUTION_ID`,`ATTRIBUTE_NAME`,`ATTRIBUTE_VALUE`,`RUNTIME_ATTRIBUTE_ID`)  values (1,'e','1',null) on duplicate key update `EXECUTION_ID`=VALUES(`EXECUTION_ID`), `ATTRIBUTE_NAME`=VALUES(`ATTRIBUTE_NAME`), `ATTRIBUTE_VALUE`=VALUES(`ATTRIBUTE_VALUE`);

Session 2

!!!!! THIS WILL CAUSE MYSQL TO HANG !!!!!

insert into `RUNTIME_ATTRIBUTES` (`EXECUTION_ID`,`ATTRIBUTE_NAME`,`ATTRIBUTE_VALUE`,`RUNTIME_ATTRIBUTE_ID`)  values (2,'e','1',null) on duplicate key update `EXECUTION_ID`=VALUES(`EXECUTION_ID`), `ATTRIBUTE_NAME`=VALUES(`ATTRIBUTE_NAME`), `ATTRIBUTE_VALUE`=VALUES(`ATTRIBUTE_VALUE`);

The SHOW ENGINE INNODB STATUS; command reveals:

---TRANSACTION 507961, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 5666332672, query id 153 localhost root update
insert into `RUNTIME_ATTRIBUTES` (`EXECUTION_ID`,`ATTRIBUTE_NAME`,`ATTRIBUTE_VALUE`,`RUNTIME_ATTRIBUTE_ID`)  values (2,'e','1',null) on duplicate key update `EXECUTION_ID`=VALUES(`EXECUTION_ID`), `ATTRIBUTE_NAME`=VALUES(`ATTRIBUTE_NAME`), `ATTRIBUTE_VALUE`=VALUES(`ATTRIBUTE_VALUE`)
------- TRX HAS BEEN WAITING 18 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 752 page no 5 n bits 72 index UK_RUNTIME_ATTRIBUTE of table `cromwell_test`.`runtime_attributes` trx id 507961 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Session 1

!!!!! THIS WILL CAUSE PRIOR COMMAND ON Session 2 TO ABORT WITH AN ERROR !!!!!

insert into `RUNTIME_ATTRIBUTES` (`EXECUTION_ID`,`ATTRIBUTE_NAME`,`ATTRIBUTE_VALUE`,`RUNTIME_ATTRIBUTE_ID`)  values (1,'f','2.2',null) on duplicate key update `EXECUTION_ID`=VALUES(`EXECUTION_ID`), `ATTRIBUTE_NAME`=VALUES(`ATTRIBUTE_NAME`), `ATTRIBUTE_VALUE`=VALUES(`ATTRIBUTE_VALUE`);

The SHOW ENGINE INNODB STATUS; command reveals:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-04-01 14:07:16 0x15123e000
*** (1) TRANSACTION:
TRANSACTION 507961, ACTIVE 107 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 5666332672, query id 157 localhost root update
insert into `RUNTIME_ATTRIBUTES` (`EXECUTION_ID`,`ATTRIBUTE_NAME`,`ATTRIBUTE_VALUE`,`RUNTIME_ATTRIBUTE_ID`)  values (2,'e','1',null) on duplicate key update `EXECUTION_ID`=VALUES(`EXECUTION_ID`), `ATTRIBUTE_NAME`=VALUES(`ATTRIBUTE_NAME`), `ATTRIBUTE_VALUE`=VALUES(`ATTRIBUTE_VALUE`)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 752 page no 5 n bits 72 index UK_RUNTIME_ATTRIBUTE of table `cromwell_test`.`runtime_attributes` trx id 507961 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 507960, ACTIVE 120 sec inserting
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 5656272896, query id 158 localhost root update
insert into `RUNTIME_ATTRIBUTES` (`EXECUTION_ID`,`ATTRIBUTE_NAME`,`ATTRIBUTE_VALUE`,`RUNTIME_ATTRIBUTE_ID`)  values (1,'f','1',null) on duplicate key update `EXECUTION_ID`=VALUES(`EXECUTION_ID`), `ATTRIBUTE_NAME`=VALUES(`ATTRIBUTE_NAME`), `ATTRIBUTE_VALUE`=VALUES(`ATTRIBUTE_VALUE`)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 752 page no 5 n bits 72 index UK_RUNTIME_ATTRIBUTE of table `cromwell_test`.`runtime_attributes` trx id 507960 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 752 page no 5 n bits 72 index UK_RUNTIME_ATTRIBUTE of table `cromwell_test`.`runtime_attributes` trx id 507960 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (1)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment