EXPLAIN SELECT * FROM `common_likes` WHERE user_id = '1' AND sender_id != '1' AND status = '2';
+------+-------------+--------------+------+-----------------------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+------+-----------------------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | common_likes | ref | PRIMARY,common_likes_status | PRIMARY | 8 | const | 1 | Using where |
+------+-------------+--------------+------+-----------------------------+---------+---------+-------+------+-------------+
EXPLAIN SELECT * FROM common_likes WHERE peer_id = '71663';
+------+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | common_likes | ALL | NULL | NULL | NULL | NULL | 7004 | Using where |
+------+-------------+--------------+------+---------------+------+---------+------+------+-------------+ADD UNIQUE KEY `@table_user_peer_sender` (`user_id`, `peer_id`, `sender_id`)------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-05-16 04:41:00 0x7f05cc3f7700
*** (1) TRANSACTION:
TRANSACTION 130522343, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 5 lock struct(s), heap size 1128, 4 row lock(s)
MySQL thread id 96932, OS thread handle 139662906558208, query id 77554557 127.0.0.1 Updating
<b>UPDATE common_likes SET `seen` = '1' WHERE user_id = '79773' AND status = '2' AND sender_id != '79773'</b>
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 60 page no 10 n bits 336 index PRIMARY of table `common_likes` trx id 130522343 lock_mode X locks rec but not gap waiting
Record lock, heap no 78 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 8; hex 0000000000000063; asc c;;
1: len 6; hex 000000000000; asc ;;
2: len 7; hex 80000000000000; asc ;;
3: len 8; hex 0000000000004328; asc C(;;
4: len 8; hex 000000000000a496; asc ;;
5: len 8; hex 0000000000004328; asc C(;;
6: len 5; hex 99aca08e59; asc Y;;
7: len 1; hex 02; asc ;;
8: len 1; hex 00; asc ;;
*** (2) TRANSACTION:
TRANSACTION 130522295, ACTIVE 2 sec fetching rows
mysql tables in use 1, locked 1
5512 lock struct(s), heap size 761976, 1371587 row lock(s)
MySQL thread id 96931, OS thread handle 139662878275328, query id 77554513 127.0.0.1 Updating
DELETE FROM common_likes WHERE peer_id = '71663'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 60 page no 10 n bits 336 index PRIMARY of table `common_likes` trx id 130522295 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;;SELECT COUNT(`id`) AS `counter` FROM table WHERE user_id = '79773' AND sender_id != '79773' AND status = '1' AND seen = '0';
Насколько я понимаю для этого нужно пройтись по всем транзакциям в блоке, посмотреть их vout и если там есть нужный адрес кошелька то зачислить сумму на баланс.
Допустим есть транзакция - https://blockchair.com/bitcoin/transaction/dc0b5e8...
В ней 2 vout: 1) кошелек 1FULLnode6c21o3DEYyLR9xdSiWksnTRjc пополнено на 0.004 BTC; 2) на кошелек bc1q9cnuqrewgzv5z8rtgqw54kfs7w4wq0yd6thdht идет возврат 0.01599856 BTC.
Как отличить что идет возврат, а не пополнение счета? Потому что, возврат зачислит как пополнение счета, а это отправка монет, а не пополнение.