Slow Database with concurrent queries, Table cache hitrate 0% [Newbie! Need help :)]

I’m a bit of a newbie when it comes to dba work so please take it easy!

I have gone through countless threads trying to improve the performance of my database, looking for someone who could give me some pointers on changes I may need to do for my variables that may squeeze more performance. Or if I have misconfigured anything.

Here are the details Server specs: E3 1270 V6 w/32gb Ram OS: Windows Server 2016 Mysql Version: 8.0.19 – MySQL Community Server – GPL

MYSQL Config https://ybin.me/p/b99f994ad62c27ad#zvwS1+XGP6ZIZtKYdiMOySg+aYR85Qp3ciTvFr6q4mE=

Show Global Status (during high usage):

https://ybin.me/p/b74fae252e807749#F9+88tWsVo/hqHjKlnlnG1gEUwj7vlonLlYrUxXnThg=

SHOW ENGINE INNODB STATUS (During High Usage)

InnoDB       ===================================== 2020-05-17 16:57:16 0xa6c INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 7 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 103908 srv_active, 0 srv_shutdown, 300397 srv_idle srv_master_thread log flush and writes: 0 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 42855 OS WAIT ARRAY INFO: signal count 37923 RW-shared spins 3053, rounds 3116, OS waits 59 RW-excl spins 6909, rounds 59455, OS waits 1320 RW-sx spins 99, rounds 1306, OS waits 25 Spin rounds per wait: 1.02 RW-shared, 8.61 RW-excl, 13.19 RW-sx ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-05-17 13:52:00 0x2904 *** (1) TRANSACTION: TRANSACTION 12175899, ACTIVE 0 sec starting index read mysql tables in use 3, locked 3 LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 3340, OS thread handle 3660, query id 2398670 localhost 127.0.0.1 root updating UPDATE user_inventory SET count = 22 WHERE identifier = 'steam:11000013f8eef2a' AND item = 'bandage'  *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 584 page no 84 n bits 336 index PRIMARY of table `essentialmode`.`user_inventory` trx id 12175899 lock_mode X locks rec but not gap Record lock, heap no 69 PHYSICAL RECORD: n_fields 6; compact format; info bits 0  0: len 4; hex 8039a49f; asc  9  ;;  1: len 6; hex 000000b9849a; asc       ;;  2: len 7; hex 02000001ad0151; asc       Q;;  3: len 21; hex 737465616d3a313130303030313366386565663261; asc steam:11000013f8eef2a;;  4: len 7; hex 62616e64616765; asc bandage;;  5: len 4; hex 8000002b; asc    +;;   *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 584 page no 2445 n bits 792 index item of table `essentialmode`.`user_inventory` trx id 12175899 lock_mode X waiting Record lock, heap no 561 PHYSICAL RECORD: n_fields 2; compact format; info bits 0  0: len 7; hex 62616e64616765; asc bandage;;  1: len 4; hex 800002e8; asc     ;;   *** (2) TRANSACTION: TRANSACTION 12175886, ACTIVE 0 sec fetching rows mysql tables in use 3, locked 3 LOCK WAIT 60 lock struct(s), heap size 8400, 2334 row lock(s) MySQL thread id 3336, OS thread handle 10296, query id 2398648 localhost 127.0.0.1 root updating UPDATE user_inventory SET count = 4 WHERE identifier = 'steam:11000010e1c050e' AND item = 'bandage'  *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 584 page no 2445 n bits 792 index item of table `essentialmode`.`user_inventory` trx id 12175886 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;;  Record lock, heap no 561 PHYSICAL RECORD: n_fields 2; compact format; info bits 0  0: len 7; hex 62616e64616765; asc bandage;;  1: len 4; hex 800002e8; asc     ;;   *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 584 page no 84 n bits 336 index PRIMARY of table `essentialmode`.`user_inventory` trx id 12175886 lock_mode X locks rec but not gap waiting Record lock, heap no 69 PHYSICAL RECORD: n_fields 6; compact format; info bits 0  0: len 4; hex 8039a49f; asc  9  ;;  1: len 6; hex 000000b9849a; asc       ;;  2: len 7; hex 02000001ad0151; asc       Q;;  3: len 21; hex 737465616d3a313130303030313366386565663261; asc steam:11000013f8eef2a;;  4: len 7; hex 62616e64616765; asc bandage;;  5: len 4; hex 8000002b; asc    +;;  *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 12207888 Purge done for trx's n:o < 12207888 undo n:o < 0 state: running but idle History list length 19 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 283609840283040, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283609840282192, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283609840281344, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (read thread) I/O thread 4 state: wait Windows aio (read thread) I/O thread 5 state: wait Windows aio (read thread) I/O thread 6 state: wait Windows aio (read thread) I/O thread 7 state: wait Windows aio (read thread) I/O thread 8 state: wait Windows aio (read thread) I/O thread 9 state: wait Windows aio (read thread) I/O thread 10 state: wait Windows aio (read thread) I/O thread 11 state: wait Windows aio (read thread) I/O thread 12 state: wait Windows aio (read thread) I/O thread 13 state: wait Windows aio (read thread) I/O thread 14 state: wait Windows aio (read thread) I/O thread 15 state: wait Windows aio (read thread) I/O thread 16 state: wait Windows aio (read thread) I/O thread 17 state: wait Windows aio (read thread) I/O thread 18 state: wait Windows aio (read thread) I/O thread 19 state: wait Windows aio (read thread) I/O thread 20 state: wait Windows aio (read thread) I/O thread 21 state: wait Windows aio (read thread) I/O thread 22 state: wait Windows aio (read thread) I/O thread 23 state: wait Windows aio (read thread) I/O thread 24 state: wait Windows aio (read thread) I/O thread 25 state: wait Windows aio (read thread) I/O thread 26 state: wait Windows aio (read thread) I/O thread 27 state: wait Windows aio (read thread) I/O thread 28 state: wait Windows aio (read thread) I/O thread 29 state: wait Windows aio (read thread) I/O thread 30 state: wait Windows aio (read thread) I/O thread 31 state: wait Windows aio (read thread) I/O thread 32 state: wait Windows aio (read thread) I/O thread 33 state: wait Windows aio (read thread) I/O thread 34 state: wait Windows aio (read thread) I/O thread 35 state: wait Windows aio (read thread) I/O thread 36 state: wait Windows aio (read thread) I/O thread 37 state: wait Windows aio (read thread) I/O thread 38 state: wait Windows aio (read thread) I/O thread 39 state: wait Windows aio (read thread) I/O thread 40 state: wait Windows aio (read thread) I/O thread 41 state: wait Windows aio (read thread) I/O thread 42 state: wait Windows aio (read thread) I/O thread 43 state: wait Windows aio (read thread) I/O thread 44 state: wait Windows aio (read thread) I/O thread 45 state: wait Windows aio (read thread) I/O thread 46 state: wait Windows aio (read thread) I/O thread 47 state: wait Windows aio (read thread) I/O thread 48 state: wait Windows aio (read thread) I/O thread 49 state: wait Windows aio (read thread) I/O thread 50 state: wait Windows aio (read thread) I/O thread 51 state: wait Windows aio (read thread) I/O thread 52 state: wait Windows aio (read thread) I/O thread 53 state: wait Windows aio (read thread) I/O thread 54 state: wait Windows aio (read thread) I/O thread 55 state: wait Windows aio (read thread) I/O thread 56 state: wait Windows aio (read thread) I/O thread 57 state: wait Windows aio (read thread) I/O thread 58 state: wait Windows aio (read thread) I/O thread 59 state: wait Windows aio (read thread) I/O thread 60 state: wait Windows aio (read thread) I/O thread 61 state: wait Windows aio (read thread) I/O thread 62 state: wait Windows aio (read thread) I/O thread 63 state: wait Windows aio (read thread) I/O thread 64 state: wait Windows aio (read thread) I/O thread 65 state: wait Windows aio (read thread) I/O thread 66 state: wait Windows aio (write thread) I/O thread 67 state: wait Windows aio (write thread) I/O thread 68 state: wait Windows aio (write thread) I/O thread 69 state: wait Windows aio (write thread) I/O thread 70 state: wait Windows aio (write thread) I/O thread 71 state: wait Windows aio (write thread) I/O thread 72 state: wait Windows aio (write thread) I/O thread 73 state: wait Windows aio (write thread) I/O thread 74 state: wait Windows aio (write thread) I/O thread 75 state: wait Windows aio (write thread) I/O thread 76 state: wait Windows aio (write thread) I/O thread 77 state: wait Windows aio (write thread) I/O thread 78 state: wait Windows aio (write thread) I/O thread 79 state: wait Windows aio (write thread) I/O thread 80 state: wait Windows aio (write thread) I/O thread 81 state: wait Windows aio (write thread) I/O thread 82 state: wait Windows aio (write thread) I/O thread 83 state: wait Windows aio (write thread) I/O thread 84 state: wait Windows aio (write thread) I/O thread 85 state: wait Windows aio (write thread) I/O thread 86 state: wait Windows aio (write thread) I/O thread 87 state: wait Windows aio (write thread) I/O thread 88 state: wait Windows aio (write thread) I/O thread 89 state: wait Windows aio (write thread) I/O thread 90 state: wait Windows aio (write thread) I/O thread 91 state: wait Windows aio (write thread) I/O thread 92 state: wait Windows aio (write thread) I/O thread 93 state: wait Windows aio (write thread) I/O thread 94 state: wait Windows aio (write thread) I/O thread 95 state: wait Windows aio (write thread) I/O thread 96 state: wait Windows aio (write thread) I/O thread 97 state: wait Windows aio (write thread) I/O thread 98 state: wait Windows aio (write thread) I/O thread 99 state: wait Windows aio (write thread) I/O thread 100 state: wait Windows aio (write thread) I/O thread 101 state: wait Windows aio (write thread) I/O thread 102 state: wait Windows aio (write thread) I/O thread 103 state: wait Windows aio (write thread) I/O thread 104 state: wait Windows aio (write thread) I/O thread 105 state: wait Windows aio (write thread) I/O thread 106 state: wait Windows aio (write thread) I/O thread 107 state: wait Windows aio (write thread) I/O thread 108 state: wait Windows aio (write thread) I/O thread 109 state: wait Windows aio (write thread) I/O thread 110 state: wait Windows aio (write thread) I/O thread 111 state: wait Windows aio (write thread) I/O thread 112 state: wait Windows aio (write thread) I/O thread 113 state: wait Windows aio (write thread) I/O thread 114 state: wait Windows aio (write thread) I/O thread 115 state: wait Windows aio (write thread) I/O thread 116 state: wait Windows aio (write thread) I/O thread 117 state: wait Windows aio (write thread) I/O thread 118 state: wait Windows aio (write thread) I/O thread 119 state: wait Windows aio (write thread) I/O thread 120 state: wait Windows aio (write thread) I/O thread 121 state: wait Windows aio (write thread) I/O thread 122 state: wait Windows aio (write thread) I/O thread 123 state: wait Windows aio (write thread) I/O thread 124 state: wait Windows aio (write thread) I/O thread 125 state: wait Windows aio (write thread) I/O thread 126 state: wait Windows aio (write thread) I/O thread 127 state: wait Windows aio (write thread) I/O thread 128 state: wait Windows aio (write thread) I/O thread 129 state: wait Windows aio (write thread) Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] ,  ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 25639 OS file reads, 5252794 OS file writes, 2142145 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 30, seg size 32, 78 merges merged operations:  insert 89, delete mark 3, delete 0 discarded operations:  insert 0, delete mark 0, delete 0 Hash table size 4425293, node heap has 577 buffer(s) Hash table size 4425293, node heap has 7 buffer(s) Hash table size 4425293, node heap has 3 buffer(s) Hash table size 4425293, node heap has 42 buffer(s) Hash table size 4425293, node heap has 25 buffer(s) Hash table size 4425293, node heap has 8 buffer(s) Hash table size 4425293, node heap has 117 buffer(s) Hash table size 4425293, node heap has 268 buffer(s) 63.71 hash searches/s, 4.29 non-hash searches/s --- LOG --- Log sequence number          8204611911 Log buffer assigned up to    8204611911 Log buffer completed up to   8204611911 Log written up to            8204611911 Log flushed up to            8204611911 Added dirty pages up to      8204611911 Pages flushed up to          8204611911 Last checkpoint at           8204611911 2113740 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 17582522368 Dictionary memory allocated 1618261 Buffer pool size   1048576 Free buffers       1010227 Database pages     37302 Old database pages 13779 Modified db pages  0 Pending reads      0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 1961, not young 12509 0.00 youngs/s, 0.00 non-youngs/s Pages read 25245, created 12063, written 2341874 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 37302, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size   131072 Free buffers       126195 Database pages     4738 Old database pages 1766 Modified db pages  0 Pending reads      0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 47, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 3177, created 1561, written 390400 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4738, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size   131072 Free buffers       126202 Database pages     4747 Old database pages 1732 Modified db pages  0 Pending reads      0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 57, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 3144, created 1603, written 377393 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4747, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size   131072 Free buffers       126359 Database pages     4584 Old database pages 1682 Modified db pages  0 Pending reads      0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 1596, not young 12509 0.00 youngs/s, 0.00 non-youngs/s Pages read 3159, created 1431, written 286695 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4584, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size   131072 Free buffers       126376 Database pages     4563 Old database pages 1679 Modified db pages  0 Pending reads      0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 23, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 3060, created 1503, written 217282 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4563, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size   131072 Free buffers       126427 Database pages     4515 Old database pages 1683 Modified db pages  0 Pending reads      0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 39, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 3113, created 1402, written 296757 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4515, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size   131072 Free buffers       126346 Database pages     4604 Old database pages 1681 Modified db pages  0 Pending reads      0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 48, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 3120, created 1484, written 220370 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4604, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size   131072 Free buffers       126053 Database pages     4883 Old database pages 1815 Modified db pages  0 Pending reads      0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 104, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 3276, created 1607, written 259821 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4883, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size   131072 Free buffers       126269 Database pages     4668 Old database pages 1741 Modified db pages  0 Pending reads      0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 47, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 3196, created 1472, written 293156 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4668, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=9652, Main thread ID=00000000000028B4 , state=sleeping Number of rows inserted 425605, updated 1126794, deleted 24758, read 3734292042 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s Number of system rows inserted 1841, updated 1056, deleted 1656, read 1362568 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 69.28 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 

MYSQL Tuner Tips https://i.gyazo.com/107f94f2e69fafe58d6f2125ff3c9ccc.png

I would highly appreciate any input on how we can make the database happier! If I’m missing any information, let me know happy to provide any extra info!

MySQL: how to prevent deadlock on concurrent inserts

Percona Server 5.6.39-83.1-log

Table structure:

CREATE TABLE `myTable` (     `column_1` VARCHAR(50) NOT NULL DEFAULT '',     `column_2` VARCHAR(50) NOT NULL DEFAULT '',     `column_3` VARCHAR(50) NOT NULL DEFAULT '',     `column_4` VARCHAR(50) NOT NULL DEFAULT '',     `column_5` VARCHAR(50) NOT NULL DEFAULT '',     PRIMARY KEY (`column_1`, `column_2`, `column_3`) USING BTREE,     INDEX `column_3` (`column_3`) USING BTREE,     INDEX `column_4_5` (`column_4`, `column_5`) USING BTREE ) COLLATE='utf8_general_ci' ENGINE=InnoDB; 

There are two or more concurrent insert leads to deadlock:

------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-03-23 18:12:45 7f1c08eb8700 *** (1) TRANSACTION: TRANSACTION 55665288601, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s) MySQL thread id 10275055, OS thread handle 0x7f1c08c6c700, query id 56392293629 localhost user update INSERT IGNORE INTO myDB.myTable (`column_1`, `column_2`, `column_3`, `column_4`, `column_5`) VALUES ('a1', 'a2', 'a3', 'a4', 'a5'), ('b1', 'b2', 'b3', 'b4', 'b5'), ('c1', 'c2', 'c3', 'c4', 'c5'), *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 337207190 page no 7370 n bits 200 index `PRIMARY` of table `myDB`.`myTable` trx id 55665288601 lock mode S locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 55665288598, ACTIVE 0 sec inserting, thread declared inside InnoDB 4965 mysql tables in use 1, locked 1 3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 35 MySQL thread id 10275057, OS thread handle 0x7f1c08eb8700, query id 56392293627 localhost user update INSERT IGNORE INTO myDB.myTable (`column_1`, `column_2`, `column_3`, `column_4`, `column_5`) VALUES ('a1', 'a2', 'a3', 'a4', 'a5'), ('b1', 'b2', 'b3', 'b4', 'b5'), ('c1', 'c2', 'c3', 'c4', 'c5') *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 337207190 page no 7370 n bits 200 index `PRIMARY` of table `myDB`.`myTable` trx id 55665288598 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 337207190 page no 83133 n bits 144 index `PRIMARY` of table `myDB`.`myTable` trx id 55665288598 lock_mode X insert intention waiting *** WE ROLL BACK TRANSACTION (1) 

Why is this happening?
Despite the fact that I do not use auto-increment in the primary key and use IGNORE in query.
How should I avoid this problem?

Concurrent and parallel algorithm

A bakery shop has to provide a stream of muffins for customers. The muffins are made by a baker in the kitchen and placed on a conveyor belt. The conveyor belt carries the muffins to where the customers are waiting to buy them. This scenario has been simulated using two processes: the baker and customer, and a shared conveyor belt implemented as a circular array called conveyor, where each space in the array can hold one muffin. There are two shared general semaphores, empty and full, and a mutex buffer_mutex. In this scenario, there is only multiple bakers and a single customer.

What is correct way to ensure that row is not deleted in concurrent transaction

I have 2 tables:

  • Parent (PK id)
  • Child (PK id, parent_id)

Note that parent_id is not defined a foreign key (actually they are not parent/child, I use those names just for example).

I need to handle a case when someone inserts new Child with link to Parent’s row, and someone else deletes Parent’s row. I would like to avoid scenario when DB contains Child row with link to non-existent Parent.

How it works now:

[I1] Insertion Thread: BEGIN [I2] Insertion Thread: SELECT * FROM Parent WHERE id = 3 [D1] Deletion Thread: BEGIN [D2] Deletion Thread: DELETE * FROM Child WHERE parent_id = 3 [D3] Deletion Thread: DELETE * FROM Parent WHERE id = 3 [D4] Deletion Thread: COMMIT [I3] Insertion Thread: INSERT INTO Child VALUES(default, 3) [I4] Insertion Thread: COMMIT 

What I’ve tried to do:

  1. Use SELECT ... FOR UPDATE in insertion thread

    Disadvantage: that will lock Deletion Thread on line D3, but line D2 is already executed at that moment. After Insertion Thread is done, Deletion Thread continue from D3 and will not delete just inserted Child row.

    Solution: swap D2 and D3, so deletion from Parent goes first.

    Disadvantage: a bit fragile. Those SQL statements are called from business logic code, in different places, so it is easy to swap their order back accidentally.

  2. Use REPEATABLE READ isolation level

    Disadvantage: it does not work as I expected. I thought it will abort Insertion Thread’s transaction as selected Parent’s row was deleted in another transaction, but it does not. I suppose it is because I do not perform update of Parent’s row in Insertion Thread, just read.

I’m kinda OK with solution 1 or explicit row locking in the beginning of transaction, but I hope there is more correct way to achieve expected behavior. Maybe I just misused isolation level?

DB is Postgres

Modeling a set of probabilistic concurrent processes

I’m looking into discrete-time Markov chains (DTMCs) for use in analyzing a probabilistic consensus protocol. One basic thing I haven’t been able to figure out is how to model a set of independent processes: consider $ N$ processes. These processes will concurrently execute a series of identical instructions labeled $ 0, 1, 2, 3,$ etc. and all are starting in instruction $ 0$ . When probability is not involved, modeling this is simple: it’s a state machine which branches nondeterministically off the start state to $ N$ different states, where in each of those $ N$ states a different process was the first to execute instruction $ 0$ . What do we do when probability is involved? Do we do the same thing with $ N$ states branching from the start state, where the probability of transitioning to each state is $ \frac{1}{N}$ ? As in, it’s uniformly random which process was the first one to execute instruction $ 0$ ?

Is this like taking the product of the state machines of each process?

I’m using a DTMC here, would I gain anything by moving to a CTMC if I don’t care about anything beyond the global order of execution?

Bonus question: assigning probabilities to whichever action (process executing an instruction) is taken first seems like a generalization of the non-probabilistic notion of fairness; if it is, what is the formal definition of this generalized notion of probabilistic fairness?

How to enable concurrent users, one RDP and one local, in post exploitation in windows without disk modification?

I want to do this by just using memory and not using disk modification or a tool, in post exploitation of a remote system, i already know how to enable RDP but the problem is when a user is logged in i cannot log in

there is a method :

https://www.mysysadmintips.com/windows/clients/545-multiple-rdp-remote-desktop-sessions-in-windows-10

and i thought i can do it in memory and patch that instruction inside memory instead of disk, but found out SVCHOST processes have SYSTEM integrity and so i cannot inject into them and modify them

so how can this be done? any other way that i can do this without using any tool or disk modification?

2013 Workflow Limit Concurrent Workflows

I am looking for a way to keep my Workflow Manager working while there are active 2013 Workflows. I assume I need to lower the number of concurrently running 2013 Workflows, but I’m open to other suggestions. Can anyone help?

On my Farm, there are long running 2013 Workflows. There are periods of no use, followed by ~5-20 being started almost simultaneously. Starting so many Workflows, at once, causes a problem. The Workflows need information from the same list, they block each other, then they go into a suspended state, and they continue ~5 minutes later. This pattern repeats until all the Workflows finish. I can see that the Server, my Workflow Manager is on, is at 100% CPU for a few minutes with ~5 minutes of no activity, while the Workflows are not finished.

I found this command but this appears to be for 2010 Workflows:

Set-SPFarmConfig –WorkflowPostponeThreshold 1 

How to stop continuous concurrent local DNS queries to dnsmasq

I set up a DNS server using dnsmasq, but it seems that it doesn’t work properly. The networking delay is up to hundreds of millisecs.

PING [server] ([server]) 56(84) bytes of data. 64 bytes from [server]: icmp_seq=1 ttl=50 time=583 ms 64 bytes from [server]: icmp_seq=2 ttl=50 time=583 ms 64 bytes from [server]: icmp_seq=3 ttl=50 time=583 ms 64 bytes from [server]: icmp_seq=4 ttl=50 time=583 ms 64 bytes from [server]: icmp_seq=5 ttl=50 time=583 ms 64 bytes from [server]: icmp_seq=6 ttl=50 time=583 ms 64 bytes from [server]: icmp_seq=7 ttl=50 time=583 ms 64 bytes from [server]: icmp_seq=8 ttl=50 time=583 ms 

Then soon I discovered that it’ll turn all right with using the default resolver systemd-resolved. By checking the log, I got the messages here below:

Jul 27 13:32:53 dnsmasq[3780]: query[A] ntp.ubuntu.com from 127.0.0.1 Jul 27 13:32:53 dnsmasq[3780]: forwarded ntp.ubuntu.com to 127.0.0.53 Jul 27 13:32:53 dnsmasq[3780]: query[A] ntp.ubuntu.com from 127.0.0.1 Jul 27 13:32:53 dnsmasq[3780]: forwarded ntp.ubuntu.com to 127.0.0.53 [countless records repeating these above...] Jul 27 13:32:53 dnsmasq[3780]: Maximum number of concurrent DNS queries reached (max: 150) Jul 27 13:32:54 dnsmasq[3780]: query[A] ntp.ubuntu.com from 127.0.0.1 Jul 27 13:32:54 dnsmasq[3780]: forwarded ntp.ubuntu.com to 127.0.0.53 [...] [probably the sigterm was sent here?] Jul 27 13:32:57 dnsmasq[3780]: query[A] ntp.ubuntu.com from 127.0.0.1 Jul 27 13:32:57 dnsmasq[3780]: forwarded ntp.ubuntu.com to 127.0.0.53 Jul 27 13:32:58 dnsmasq[3780]: no servers found in /run/dnsmasq/resolv.conf, will retry Jul 27 13:32:58 dnsmasq[3780]: query[A] ntp.ubuntu.com from 127.0.0.1 Jul 27 13:32:58 dnsmasq[3780]: query[A] ntp.ubuntu.com from 127.0.0.1 Jul 27 13:32:58 dnsmasq[3780]: query[A] ntp.ubuntu.com from 127.0.0.1 Jul 27 13:32:58 dnsmasq[3780]: query[A] ntp.ubuntu.com from 127.0.0.1 Jul 27 13:32:58 dnsmasq[3780]: query[A] ntp.ubuntu.com from 127.0.0.1 Jul 27 13:32:58 dnsmasq[3780]: exiting on receipt of SIGTERM 

So.. How to resolve this problem which makes me crazy, plz 🙁

The content from dnsmasq.conf is here:

strict-order resolv-file=/etc/resolv.conf.dnsmasq listen-address=0.0.0.0 server=/aliyuncs.com/100.100.2.136 server=/aliyuncs.com/100.100.2.138 server=/*.cn/100.100.2.136 server=/*.cn/100.100.2.138  bogus-nxdomain=100.100.2.136 bogus-nxdomain=100.100.2.138  log-queries log-facility=/var/log/dnsmasq/dnsmasq.log log-async=50  #EOF 

And resolv.conf.dnsmasq:

nameserver 100.100.2.136 nameserver 100.100.2.138 nameserver 208.67.222.123 nameserver 208.67.220.123 nameserver 8.8.8.8 nameserver 8.8.4.4 nameserver 1.2.4.8 nameserver 210.2.4.8 nameserver 208.67.222.222 nameserver 208.67.220.220 

resolv.conf:

# Dynamic resolv.conf(5) file for glibc resolver(3) generated by resolvconf(8) #     DO NOT EDIT THIS FILE BY HAND -- YOUR CHANGES WILL BE OVERWRITTEN # 127.0.0.53 is the systemd-resolved stub resolver. # run "systemd-resolve --status" to see details about the actual nameservers.  nameserver 127.0.0.53 options timeout:2 attempts:3 rotate single-request-reopen 

How to set up two concurrent user with euqal rights and permissions?

I would like to have a system with two concurrent users, who are not able to see each others memory nor processes nor data.

So the users shall be able to do whatever they want in their ecosystem, but they should not be able to access any of the other’s activities.

Is the only way to accomplish that, to give root rights to a third party, who is managing the two users?