[gelöst] epg2vdr: SQL-Error in 'execute(stmt_execute)' - Deadlock found when trying to get lock ...

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
170226 12:05:57 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 58 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 52103 1_second, 52011 sleeps, 4835 10_second, 4556 background, 4555 flush
srv_master_thread log flush and writes: 54615
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 20182, signal count 29081
Mutex spin waits 114904, rounds 435782, OS waits 4775
RW-shared spins 18173, rounds 362276, OS waits 8346
RW-excl spins 10080, rounds 287021, OS waits 6288
Spin rounds per wait: 3.79 mutex, 19.93 RW-shared, 28.47 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
170226 11:50:22
*** (1) TRANSACTION:
TRANSACTION 3B619EB, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), heap size 1248, 9 row lock(s), undo log entries 6
MySQL thread id 227, OS thread handle 0x7fbae4066700, query id 19009523 localhost epg2vdr Updating
update events set actor = ?, audio = ?, camera = ?, category = ?, commentator = ?, complongdescription = ?, compshorttext = ?, comptitle = ?, contents = ?, country = ?, delflg = ?, director = ?, duration = ?, episodecompname = ?, episodecomppartname = ?, episodecompshortname = ?, episodelang = ?, extepnum = ?, fileref = ?, flags = ?, genre = ?, guest = ?, imagecount = ?, longdescription = ?, moderator = ?, movieid = ?, music = ?, numrating = ?, other = ?, parentalrating = ?, producer = ?, rating = ?, screenplay = ?, scrmovieid = ?, scrseriesepisode = ?, scrseriesid = ?, scrsp = ?, shortdescription = ?, shortreview = ?, shorttext = ?, source = ?, starttime = ?, tableid = ?, tipp = ?, title = ?, topic = ?, txtrating = ?, updflg = ?, updsp = ?, useid = ?, version = ?, vps = ?, year = ? where channelid = ? and eventid = ?
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 79254 page no 9021 n bits 328 index `idxupdflgupdsp` of table `epg2vdr`.`events` trx id 3B619EB lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 1; hex 4c; asc L;;
 1: len 4; hex d8b15c74; asc   \t;;
 2: len 8; hex 000000000000aeae; asc         ;;
 3: len 19; hex 5331392e32452d312d313039312d3331323230; asc S19.2E-1-1091-31220;;


*** (2) TRANSACTION:
TRANSACTION 3B619EA, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
4188 lock struct(s), heap size 391608, 66994 row lock(s), undo log entries 10
MySQL thread id 41, OS thread handle 0x7fbae4035700, query id 19009524 localhost epg2vdr Sending data
update
 events t
 inner join
(
  select
   e.masterid,
   v.updflg
  from
   events e
   left join
   events v
   on e.masterid = v.useid and v.masterid != e.masterid and v.updflg = 'L'
  where
   e.updflg in('C','T') and
   e.starttime + e.duration >= unix_timestamp()
) s
on t.masterid=s.masterid
set
 t.useid = t.masterid,
 t.updflg = case when t.delflg = 'Y' then 'D' else 'R' end,
 t.updsp = unix_timestamp()
where
 s.updflg is Null
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 79254 page no 9021 n bits 328 index `idxupdflgupdsp` of table `epg2vdr`.`events` trx id 3B619EA lock mode S
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 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 1; hex 4c; asc L;;
 1: len 4; hex d8b15c74; asc   \t;;
 2: len 8; hex 000000000000aeae; asc         ;;
 3: len 19; hex 5331392e32452d312d313039312d3331323230; asc S19.2E-1-1091-31220;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 79254 page no 8548 n bits 88 index `PRIMARY` of table `epg2vdr`.`events` trx id 3B619EA lock mode S waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 59; compact format; info bits 0
 0: len 8; hex 0000000000000b4f; asc        O;;
 1: len 19; hex 5331392e32452d312d313034312d3131393331; asc S19.2E-1-1041-11931;;
 2: len 6; hex 000003b619eb; asc       ;;
 3: len 7; hex 20000003510110; asc     Q  ;;
 4: len 4; hex 00015650; asc   VP;;
 5: len 4; hex 00015650; asc   VP;;
 6: len 3; hex 766472; asc vdr;;
 7: SQL NULL;
 8: len 4; hex d8b284fc; asc     ;;
 9: len 4; hex d8b2b2ee; asc     ;;
 10: len 1; hex 49; asc I;;
 11: SQL NULL;
 12: len 4; hex 80000051; asc    Q;;
 13: len 4; hex 80000017; asc     ;;
 14: len 5; hex 576f6c6669; asc Wolfi;;
 15: len 5; hex 574f4c4649; asc WOLFI;;
 16: len 30; hex 274d65696e20436f7573696e2057696c6c6965202f204d75747465727461; asc 'Mein Cousin Willie / Mutterta; (total 32 bytes);
 17: len 25; hex 4d45494e434f5553494e57494c4c49454d5554544552544147; asc MEINCOUSINWILLIEMUTTERTAG;;
 18: len 30; hex 312e20476573636869636874653a20576f6c666920667265757420736963; asc 1. Geschichte: Wolfi freut sic; (total 499 bytes);
 19: len 30; hex 3147455343484943485445574f4c46494652455554534943484552574952; asc 1GESCHICHTEWOLFIFREUTSICHERWIR; (total 403 bytes);
 20: len 4; hex d8b7a34c; asc    L;;
 21: len 4; hex 80000258; asc    X;;
 22: len 4; hex 80000000; asc     ;;
 23: len 4; hex 80000000; asc     ;;
 24: SQL NULL;
 25: SQL NULL;
 26: SQL NULL;
 27: SQL NULL;
 28: SQL NULL;
 29: SQL NULL;
 30: SQL NULL;
 31: SQL NULL;
 32: SQL NULL;
 33: SQL NULL;
 34: SQL NULL;
 35: SQL NULL;
 36: SQL NULL;
 37: SQL NULL;
 38: SQL NULL;
 39: SQL NULL;
 40: SQL NULL;
 41: SQL NULL;
 42: SQL NULL;
 43: SQL NULL;
 44: SQL NULL;
 45: SQL NULL;
 46: SQL NULL;
 47: SQL NULL;
 48: SQL NULL;
 49: SQL NULL;
 50: SQL NULL;
 51: SQL NULL;
 52: SQL NULL;
 53: SQL NULL;
 54: SQL NULL;
 55: SQL NULL;
 56: SQL NULL;
 57: SQL NULL;
 58: SQL NULL;


*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 3B62F5D
Purge done for trx's n:o < 3B62F59 undo n:o < 0
History list length 453
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 261, OS thread handle 0x7fbae40f9700, query id 19312737 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 3B62F57, not started
MySQL thread id 227, OS thread handle 0x7fbae4066700, query id 19312736 localhost epg2vdr
---TRANSACTION 3B62EC1, not started
MySQL thread id 226, OS thread handle 0x7fbae40c8700, query id 19312735 localhost epg2vdr
---TRANSACTION 3B62F5B, not started
MySQL thread id 222, OS thread handle 0x7fbacf77c700, query id 19312181 localhost epg2vdr
---TRANSACTION 3B62F3A, not started
MySQL thread id 220, OS thread handle 0x7fbae811b700, query id 19309210 localhost epg2vdr
---TRANSACTION 3B62B2C, not started
MySQL thread id 43, OS thread handle 0x7fbae4097700, query id 19312688 localhost epg2vdr
---TRANSACTION 3B62F5C, not started
MySQL thread id 41, OS thread handle 0x7fbae4035700, query id 19312323 localhost epg2vdr
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
3470747 OS file reads, 1224850 OS file writes, 354454 OS fsyncs
1.40 reads/s, 16384 avg bytes/read, 2.67 writes/s, 0.57 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 6149, seg size 6151, 55885 merges
merged operations:
 insert 154716, delete mark 517341, delete 137508
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 87 buffer(s)
164.95 hash searches/s, 23.93 non-hash searches/s
---
LOG
---
Log sequence number 241252328814
Log flushed up to   241252328814
Last checkpoint at  241252328814
0 pending log writes, 0 pending chkp writes
250009 log i/o's done, 0.36 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 264881
Buffer pool size   8191
Free buffers       0
Database pages     8104
Old database pages 2971
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5827269, not young 0
1.26 youngs/s, 0.00 non-youngs/s
Pages read 3470709, created 185222, written 918268
1.40 reads/s, 0.02 creates/s, 2.22 writes/s
Buffer pool hit rate 998 / 1000, young-making rate 2 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8104, unzip_LRU len: 0
I/O sum[191]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 1280, id 140440359393024, state: waiting for server activity
Number of rows inserted 1804623, updated 593297, deleted 474695, read 278423002
0.05 inserts/s, 0.29 updates/s, 0.12 deletes/s, 274.51 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================


1 row in set (0.02 sec)