精华内容
下载资源
问答
  • join有关的一种死锁

    2021-02-25 11:27:24
    join有关的一种死锁 public class ThreadDemo { static ReentrantLock lock = new ReentrantLock(); public static void main(String[] args) throws InterruptedException { Thread thread1 = new Thread(() -&...

    join有关的一种死锁

    public class ThreadDemo {
        static ReentrantLock lock = new ReentrantLock();
    
        public static void main(String[] args) throws InterruptedException {
            Thread thread1 = new Thread(() -> test());
    
            //此时如果thread1先抢占到资源,就不会卡死,但是main程序先抢占的话就会死锁
            thread1.start();
    
            //这里睡眠100millis,thread1抢占的几率变大
            System.out.println("i am gonna sleep");
            Thread.sleep(100);
    
            long start = System.currentTimeMillis();
            //不同线程是阻塞的,不可重入
            lock.lock();
            try {
                //此时join 会让thread1先执行,而thread1执行需要锁,获取不到锁,造成死锁
                //如果thread1已经执行完了,再join就不会生效了
                thread1.join();
                Thread.sleep(5000);
            } finally {
                lock.unlock();
            }
            System.out.println("i will print after thread1");
            long end = System.currentTimeMillis();
            System.out.println(end - start);
        }
    
        public static void test() {
    //        Thread.yield();
            System.out.println("i am in thread1");
            lock.lock();
            try {
                for (int i = 0; i < 20; i++) {
                    System.out.println(Thread.currentThread().getName() + ":" + i);
                }
                Thread.sleep(2000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            } finally {
                lock.unlock();
            }
        }
    
    }
    
    展开全文
  • 如下一个两更新语句UPDATE hzxm201610 a,xmhzylb1201610 b SET a.gk07_1_6=b.gk04_11,a.gk07_2_6=b.f06_1,a.gk07_3_6=b.f07_1,a.gk07_4_6=b.f08_1 where substring(a.gk01,1,4) in ('5323') and a.gk01=b.gk01 and...

    如下一个两表更新语句

    UPDATE hzxm201610 a,xmhzylb1201610 b SET a.gk07_1_6=b.gk04_11,a.gk07_2_6=b.f06_1,a.gk07_3_6=b.f07_1,a.gk07_4_6=b.f08_1  where   substring(a.gk01,1,4) in ('5323') and a.gk01=b.gk01 and dqdm='532300'

    如果漏掉了where会怎么样:

    UPDATE hzxm201610 a,xmhzylb1201610 b SET a.gk07_1_6=b.gk04_11,a.gk07_2_6=b.f06_1,a.gk07_3_6=b.f07_1,a.gk07_4_6=b.f08_1  substring(a.gk01,1,4) in ('5323') and a.gk01=b.gk01 and dqdm='532300'

    一般以为是会报错。其实不然,这个语句正常执行!而且对两个表全扫描,最后什么也不做!但是会锁定两个表很长时间,导致应用系统无法工作!

    查询mysql死锁的语句:

    #Show PROCESSLIST;

    SELECT * FROM information_schema.INNODB_TRX;

    SELECT * FROM information_schema.INNODB_LOCKS;

    SELECT * FROM information_schema.INNODB_LOCK_WAITS;

    SHOW VARIABLES LIKE '%lock%';

    展开全文
  • (The child process can call the Queue.cancel_join_thread method of the queue to avoid this behaviour.) This means that whenever you use a queue you need to make sure that all items which have been ...

    I have a question understanding the queue in the multiprocessing module in python 3

    This is what they say in the programming guidelines:

    Bear in mind that a process that has put items in a queue will wait before

    terminating until all the buffered items are fed by the “feeder” thread to

    the underlying pipe. (The child process can call the

    Queue.cancel_join_thread

    method of the queue to avoid this behaviour.)

    This means that whenever you use a queue you need to make sure that all

    items which have been put on the queue will eventually be removed before the

    process is joined. Otherwise you cannot be sure that processes which have

    put items on the queue will terminate. Remember also that non-daemonic

    processes will be joined automatically.

    An example which will deadlock is the following:

    from multiprocessing import Process, Queue

    def f(q):

    q.put('X' * 1000000)

    if __name__ == '__main__':

    queue = Queue()

    p = Process(target=f, args=(queue,))

    p.start()

    p.join() # this deadlocks

    obj = queue.get()

    A fix here would be to swap the last two lines (or simply remove the

    p.join() line).

    So apparently, queue.get() should not be called after a join().

    However there are examples of using queues where get is called after a join like:

    import multiprocessing as mp

    import random

    import string

    # define a example function

    def rand_string(length, output):

    """ Generates a random string of numbers, lower- and uppercase chars. """

    rand_str = ''.join(random.choice(

    string.ascii_lowercase

    + string.ascii_uppercase

    + string.digits)

    for i in range(length))

    output.put(rand_str)

    if __name__ == "__main__":

    # Define an output queue

    output = mp.Queue()

    # Setup a list of processes that we want to run

    processes = [mp.Process(target=rand_string, args=(5, output))

    for x in range(2)]

    # Run processes

    for p in processes:

    p.start()

    # Exit the completed processes

    for p in processes:

    p.join()

    # Get process results from the output queue

    results = [output.get() for p in processes]

    print(results)

    I've run this program and it works (also posted as a solution to the StackOverFlow question Python 3 - Multiprocessing - Queue.get() does not respond).

    Could someone help me understand what the rule for the deadlock is here?

    解决方案

    The queue implementation in multiprocessing that allows data to be transferred between processes relies on standard OS pipes.

    OS pipes are not infinitely long, so the process which queues data could be blocked in the OS during the put() operation until some other process uses get() to retrieve data from the queue.

    For small amounts of data, such as the one in your example, the main process can join() all the spawned subprocesses and then pick up the data. This often works well, but does not scale, and it is not clear when it will break.

    But it will certainly break with large amounts of data. The subprocess will be blocked in put() waiting for the main process to remove some data from the queue with get(), but the main process is blocked in join() waiting for the subprocess to finish. This results in a deadlock.

    Here is an example where a user had this exact issue. I posted some code in an answer there that helped him solve his problem.

    展开全文
  • 1、查询进程show processlist2、 查询到相对应的进程,然后 kill id验证(kill后再看是否还有锁)2、查询是否锁show OPEN TABLES where In_use > 0;示例:新建一个会话执行如下的显示锁示例LOCK TABLES account_...

    1、查询进程

    show processlist

    2、 查询到相对应的进程,然后 kill id

    验证(kill后再看是否还有锁)

    2、查询是否锁表

    show OPEN TABLES where In_use > 0;

    示例:

    新建一个会话执行如下的显示锁示例

    LOCK TABLES account_data.account READ;

    SELECT SLEEP(160);

    UNLOCK TABLES account_data.account;

    另开启一个会话检查锁表情况:

    mysql> show OPEN TABLES where In_use > 0;

    +--------------+---------+--------+-------------+

    | Database | Table | In_use | Name_locked |

    +--------------+---------+--------+-------------+

    | account_data | account | 1 | 0 |

    +--------------+---------+--------+-------------+

    1 row in set (0.00 sec)

    mysql> select * from information_schema.innodb_locks\G;

    Empty set, 1 warning (0.00 sec)

    ERROR:

    No query specified

    mysql> show processlist\G;

    *************************** 1. row ***************************

    Id: 5

    User: root

    Host: 192.168.0.206:64294

    db: NULL

    Command: Sleep

    Time: 4051

    State:

    Info: NULL

    *************************** 2. row ***************************

    Id: 8

    User: root

    Host: 192.168.0.206:64297

    db: NULL

    Command: Sleep

    Time: 4042

    State:

    Info: NULL

    *************************** 3. row ***************************

    Id: 10

    User: root

    Host: localhost

    db: NULL

    Command: Query

    Time: 0

    State: starting

    Info: show processlist

    *************************** 4. row ***************************

    Id: 19

    User: root

    Host: 192.168.0.206:54603

    db: account_data

    Command: Sleep

    Time: 245

    State:

    Info: NULL

    *************************** 5. row ***************************

    Id: 20

    User: root

    Host: 192.168.0.206:54604

    db: information_schema

    Command: Query

    Time: 20

    State: User sleep

    Info: select sleep(160)

    5 rows in set (0.00 sec)

    ERROR:

    No query specified

    mysql>

    3、在5.5中,information_schema 库中增加了三个关于锁的表(innoDB引擎):

    innodb_trx ## 当前运行的所有事务

    innodb_locks ## 当前出现的锁

    innodb_lock_waits ## 锁等待的对应关系

    先来看一下这三张表结构:

    root@127.0.0.1 : information_schema 13:28:38> desc innodb_locks;

    +————-+———————+——+—–+———+——-+

    | Field | Type | Null | Key | Default | Extra |

    +————-+———————+——+—–+———+——-+

    | lock_id | varchar(81) | NO | | | |#锁ID

    | lock_trx_id | varchar(18) | NO | | | |#拥有锁的事务ID

    | lock_mode | varchar(32) | NO | | | |#锁模式

    | lock_type | varchar(32) | NO | | | |#锁类型

    | lock_table | varchar(1024) | NO | | | |#被锁的表

    | lock_index | varchar(1024) | YES | | NULL | |#被锁的索引

    | lock_space | bigint(21) unsigned | YES | | NULL | |#被锁的表空间号

    | lock_page | bigint(21) unsigned | YES | | NULL | |#被锁的页号

    | lock_rec | bigint(21) unsigned | YES | | NULL | |#被锁的记录号

    | lock_data | varchar(8192) | YES | | NULL | |#被锁的数据

    +————-+———————+——+—–+———+——-+

    10 rows in set (0.00 sec)

    root@127.0.0.1 : information_schema 13:28:56> desc innodb_lock_waits;

    +——————-+————-+——+—–+———+——-+

    | Field | Type | Null | Key | Default | Extra |

    +——————-+————-+——+—–+———+——-+

    | requesting_trx_id | varchar(18) | NO | | | |#请求锁的事务ID

    | requested_lock_id | varchar(81) | NO | | | |#请求锁的锁ID

    | blocking_trx_id | varchar(18) | NO | | | |#当前拥有锁的事务ID

    | blocking_lock_id | varchar(81) | NO | | | |#当前拥有锁的锁ID

    +——————-+————-+——+—–+———+——-+

    4 rows in set (0.00 sec)

    root@127.0.0.1 : information_schema 13:29:05> desc innodb_trx ;

    +—————————-+———————+——+—–+———————+——-+

    | Field | Type | Null | Key | Default | Extra |

    +—————————-+———————+——+—–+———————+——-+

    | trx_id | varchar(18) | NO | | | |#事务ID

    | trx_state | varchar(13) | NO | | | |#事务状态:

    | trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#事务开始时间;

    | trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodb_locks.lock_id

    | trx_wait_started | datetime | YES | | NULL | |#事务开始等待的时间

    | trx_weight | bigint(21) unsigned | NO | | 0 | |#

    | trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |#事务线程ID

    | trx_query | varchar(1024) | YES | | NULL | |#具体SQL语句

    | trx_operation_state | varchar(64) | YES | | NULL | |#事务当前操作状态

    | trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#事务中有多少个表被使用

    | trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#事务拥有多少个锁

    | trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#

    | trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#事务锁住的内存大小(B)

    | trx_rows_locked | bigint(21) unsigned | NO | | 0 | |#事务锁住的行数

    | trx_rows_modified | bigint(21) unsigned | NO | | 0 | |#事务更改的行数

    | trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |#事务并发票数

    | trx_isolation_level | varchar(16) | NO | | | |#事务隔离级别

    | trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性检查

    | trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外键检查

    | trx_last_foreign_key_error | varchar(256) | YES | | NULL | |#最后的外键错误

    | trx_adaptive_hash_latched | int(1) | NO | | 0 | |#

    | trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |#

    +—————————-+———————+——+—–+———————+——-+

    22 rows in set (0.01 sec)

    查看正在锁的事务

    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

    查看等待锁的事务

    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

    查看锁阻塞线程信息

    3.1 使用show processlist查看

    3.2 直接使用show engine innodb status查看

    ------------

    TRANSACTIONS

    ------------

    Trx id counter 4131

    Purge done for trx's n:o < 4119 undo n:o < 0 state: running but idle

    History list length 126

    LIST OF TRANSACTIONS FOR EACH SESSION:

    ---TRANSACTION 0, not started

    MySQL thread id 2, OS thread handle 0x7f953ffff700, query id 115 localhost root init

    show engine innodb status

    ---TRANSACTION 4130, ACTIVE 41 sec starting index read

    mysql tables in use 1, locked 1

    LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)

    MySQL thread id 4, OS thread handle 0x7f953ff9d700, query id 112 localhost root updating

    delete from emp where empno=7788

    ------- TRX HAS BEEN WAITING 41 SEC FOR THIS LOCK TO BE GRANTED: ## 等待了41s

    RECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4130 lock_mode X locks rec but not gap waiting

    Record lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 ## 线程4在等待往test.emp中的主键上加X锁,page num=3

    0: len 4; hex 80001e6c; asc l;;

    1: len 6; hex 000000001018; asc ;;

    2: len 7; hex 91000001420084; asc B ;;

    3: len 5; hex 53434f5454; asc SCOTT;;

    4: len 7; hex 414e414c595354; asc ANALYST;;

    5: len 4; hex 80001d8e; asc ;;

    6: len 4; hex 208794f0; asc ;;

    7: len 4; hex 80000bb8; asc ;;

    8: SQL NULL;

    9: len 4; hex 80000014; asc ;;

    ------------------

    ---TRANSACTION 4129, ACTIVE 45 sec starting index read

    mysql tables in use 1, locked 1

    LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)

    MySQL thread id 7, OS thread handle 0x7f953ff6c700, query id 111 localhost root updating

    update emp set sal=3500 where empno=7788

    ------- TRX HAS BEEN WAITING 45 SEC FOR THIS LOCK TO BE GRANTED: ## 等待了45s

    RECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4129 lock_mode X locks rec but not gap waiting

    Record lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 ## 线程7在等待往test.emp中的主键上加X锁,page num=3

    0: len 4; hex 80001e6c; asc l;;

    1: len 6; hex 000000001018; asc ;;

    2: len 7; hex 91000001420084; asc B ;;

    3: len 5; hex 53434f5454; asc SCOTT;;

    4: len 7; hex 414e414c595354; asc ANALYST;;

    5: len 4; hex 80001d8e; asc ;;

    6: len 4; hex 208794f0; asc ;;

    7: len 4; hex 80000bb8; asc ;;

    8: SQL NULL;

    9: len 4; hex 80000014; asc ;;

    ------------------

    ---TRANSACTION 4128, ACTIVE 51 sec

    2 lock struct(s), heap size 360, 1 row lock(s)

    MySQL thread id 3, OS thread handle 0x7f953ffce700, query id 110 localhost root cleaning up

    我们知道,主要根因还是thread=3引起的,但从innodb status中却无法分析得到这个结果。

    从上面来看,线程4和线程7都在等待往test.emp中的主键上加X锁,page num=3,但是线程7等待的时间为45s,而线程4等待的时间为41s,是较线程7之后申请的锁,所以可以判断是线程7阻塞了线程4。至于线程7为什么出现等待,这里分析不到根因。

    3.3 使用mysqladmin debug查看

    # mysqladmin -S /tmp/mysql3306.sock debug

    然后在error日志中,会看到:

    Thread database.table_name Locked/Waiting Lock_type

    3 test.t3 Locked - read Low priority read lock

    7 test.emp Locked - write High priority write lock

    这种方法中,能找到线程ID=3和7是阻塞者,但还是不太准确,判断不出来线程7也是被线程ID=3阻塞的。

    3.4 使用innodb_lock_monitor来获取阻塞锁线程

    MySQL [test]> CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB; ## 随便在一个数据库中创建这个表,就会打开lock monitor

    Query OK, 0 rows affected, 1 warning (0.07 sec)

    MySQL [test]> show warnings\G

    *************************** 1. row ***************************

    Level: Warning

    Code: 131

    Message: Using the table name innodb_lock_monitor to enable diagnostic output is deprecated and may be removed in future releases. Use INFORMATION_SCHEMA or PERFORMANCE_SCHEMA tables or SET GLOBAL innodb_status_output=ON.

    1 row in set (0.00 sec)

    说明:这个在5.6中有一个warning,但不影响使用。

    然后再使用show engine innodb status查看:

    ------------

    TRANSACTIONS

    ------------

    Trx id counter 4667

    Purge done for trx's n:o < 4659 undo n:o < 0 state: running but idle

    History list length 138

    LIST OF TRANSACTIONS FOR EACH SESSION:

    ---TRANSACTION 0, not started

    MySQL thread id 9, OS thread handle 0x7f813c5f7700, query id 152 localhost root init

    show engine innodb status

    ---TRANSACTION 4663, ACTIVE 78 sec starting index read

    mysql tables in use 1, locked 1

    LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)

    MySQL thread id 4, OS thread handle 0x7f813c628700, query id 149 localhost root updating

    delete from emp where empno=7788

    ------- TRX HAS BEEN WAITING 78 SEC FOR THIS LOCK TO BE GRANTED: ## 等待了78s

    RECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4663 lock_mode X locks rec but not gap waiting

    Record lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 ## 线程4在等待往test.emp中的主键上加X锁,page num=3

    0: len 4; hex 80001e6c; asc l;;

    1: len 6; hex 000000001018; asc ;;

    2: len 7; hex 91000001420084; asc B ;;

    3: len 5; hex 53434f5454; asc SCOTT;;

    4: len 7; hex 414e414c595354; asc ANALYST;;

    5: len 4; hex 80001d8e; asc ;;

    6: len 4; hex 208794f0; asc ;;

    7: len 4; hex 80000bb8; asc ;;

    8: SQL NULL;

    9: len 4; hex 80000014; asc ;;

    ------------------

    TABLE LOCK table `test`.`emp` trx id 4663 lock mode IX ## 在给主键行上加X锁之前,先要在表上加意向锁IX

    RECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4663 lock_mode X locks rec but not gap waiting

    Record lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0

    0: len 4; hex 80001e6c; asc l;;

    1: len 6; hex 000000001018; asc ;;

    2: len 7; hex 91000001420084; asc B ;;

    3: len 5; hex 53434f5454; asc SCOTT;;

    4: len 7; hex 414e414c595354; asc ANALYST;;

    5: len 4; hex 80001d8e; asc ;;

    6: len 4; hex 208794f0; asc ;;

    7: len 4; hex 80000bb8; asc ;;

    8: SQL NULL;

    9: len 4; hex 80000014; asc ;;

    ---TRANSACTION 4662, ACTIVE 81 sec starting index read

    mysql tables in use 1, locked 1

    LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)

    MySQL thread id 7, OS thread handle 0x7f813c5c6700, query id 148 localhost root updating

    update emp set sal=3500 where empno=7788

    ------- TRX HAS BEEN WAITING 81 SEC FOR THIS LOCK TO BE GRANTED: ## 等待了81s

    RECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4662 lock_mode X locks rec but not gap waiting

    Record lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 ## 线程7在等待往test.emp中的主键上加X锁,page num=3

    0: len 4; hex 80001e6c; asc l;;

    1: len 6; hex 000000001018; asc ;;

    2: len 7; hex 91000001420084; asc B ;;

    3: len 5; hex 53434f5454; asc SCOTT;;

    4: len 7; hex 414e414c595354; asc ANALYST;;

    5: len 4; hex 80001d8e; asc ;;

    6: len 4; hex 208794f0; asc ;;

    7: len 4; hex 80000bb8; asc ;;

    8: SQL NULL;

    9: len 4; hex 80000014; asc ;;

    ------------------

    TABLE LOCK table `test`.`emp` trx id 4662 lock mode IX ## 在给主键行上加X锁之前,先要在表上加意向锁IX

    RECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4662 lock_mode X locks rec but not gap waiting

    Record lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0

    0: len 4; hex 80001e6c; asc l;;

    1: len 6; hex 000000001018; asc ;;

    2: len 7; hex 91000001420084; asc B ;;

    3: len 5; hex 53434f5454; asc SCOTT;;

    4: len 7; hex 414e414c595354; asc ANALYST;;

    5: len 4; hex 80001d8e; asc ;;

    6: len 4; hex 208794f0; asc ;;

    7: len 4; hex 80000bb8; asc ;;

    8: SQL NULL;

    9: len 4; hex 80000014; asc ;;

    ---TRANSACTION 4615, ACTIVE 1579 sec, thread declared inside InnoDB 1222

    mysql tables in use 2, locked 0

    2 lock struct(s), heap size 360, 1 row lock(s)

    MySQL thread id 3, OS thread handle 0x7f813c659700, query id 147 localhost root Sending data

    select count(*) from t3 a,t3 b ## 这是线程3当前正在执行的SQL

    Trx read view will not see trx with id >= 4662, sees < 4659

    TABLE LOCK table `test`.`emp` trx id 4615 lock mode IX ## 线程3中正在拥有表上的意向IX锁,并且有test.emp表上主键的行级X锁,page num=3

    RECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4615 lock_mode X locks rec but not gap

    Record lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0

    0: len 4; hex 80001e6c; asc l;;

    1: len 6; hex 000000001018; asc ;;

    2: len 7; hex 91000001420084; asc B ;;

    3: len 5; hex 53434f5454; asc SCOTT;;

    4: len 7; hex 414e414c595354; asc ANALYST;;

    5: len 4; hex 80001d8e; asc ;;

    6: len 4; hex 208794f0; asc ;;

    7: len 4; hex 80000bb8; asc ;;

    8: SQL NULL;

    9: len 4; hex 80000014; asc ;;

    为什么线程3当前执行的是一个select t3表操作,但却锁住了test.emp表上page num=3?

    有可能是线程3之前对test.emp表的操作事务没有及时提交导致。

    所以得出:线程3阻塞了线程7,而线程7又阻塞了线程4,所以根因就是线程3,让线程3尽快提交或是kill掉即可。

    3.5、 查看表锁的情况:

    mysql> show status like 'table%';

    +----------------------------+---------+

    | Variable_name | Value |

    +----------------------------+---------+

    | Table_locks_immediate | 100 |

    | Table_locks_waited | 11 |

    +----------------------------+---------+

    3.6、查看InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况:

    mysql> show status like 'InnoDB_row_lock%';

    +-------------------------------+--------+

    | Variable_name | Value |

    +-------------------------------+--------+

    | Innodb_row_lock_current_waits | 0 |

    | Innodb_row_lock_time | 159372 |

    | Innodb_row_lock_time_avg | 39843 |

    | Innodb_row_lock_time_max | 51154 |

    | Innodb_row_lock_waits | 4 |

    +-------------------------------+--------+

    5 rows in set (0.01 sec)

    mysql>

    4. 结论

    在分析innodb中锁阻塞时,几种方法的对比情况:

    (1)使用show processlist查看不靠谱;

    (2)直接使用show engine innodb status查看,无法判断到问题的根因;

    (3)使用mysqladmin debug查看,能看到所有产生锁的线程,但无法判断哪个才是根因;

    (4)开启innodb_lock_monitor后,再使用show engine innodb status查看,能够找到锁阻塞的根因。

    到此这篇关于mysql查看死锁与去除死锁的文章就介绍到这了,更多相关mysql查看死锁与去除死锁内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家!

    展开全文
  • MySQL在遇到锁死锁的时候可以通过以下命令进行查找问题原因和相关解决办法:查看锁情况及等待锁SQL语句SELECT r.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query,b....
  • Java---线程之死锁

    2021-06-03 06:16:02
    死锁的两种情况:简单的说下单块cpu运行线程的情况:大家可能平时玩电脑,可以同时挂QQ啊,玩游戏啊,打开文本啊,等等。这里,我们假设是单块cpu。也就是俗称的单核cpu。大家可能会觉得这些软件,这些线程是同时...
  • MySQL联查询基本操作之left-join常见的坑2020-06-16 16:35:15概述对于中小体量的项目而言,联查询是再常见不过的操作了,尤其是在做报表的时候。然而校对数据的时候,您发现坑了吗?本篇文章就 mysql 常用联...
  • 在面试过程中,死锁也是高频的考点,因为如果线上环境真发生了死锁,那真的出大事了。 这次,我们就来系统地聊聊死锁的问题。 死锁的概念; 模拟死锁问题的产生; 利用工具排查死锁问题; 避免死锁问题的发生; ...
  • 我遇到了从以下脚本收集日志...in range(10): lt = LoggingThread() lt.setDaemon(True) lt.start() # # multiprocess part # do_parallel_job() logger.debug('FINISH') 如何在进程和线程脚本中使用日志记录模块?
  • Java死锁分析

    2021-05-13 17:58:14
    死锁进程环境下的一种假死现象,即产生死锁的进程都处于等待状态。只要满足了下面四个必要条件,就有可能产生死锁:互斥条件:进程对所分配到的资源不允许其他进程进行访问,若其他进程访问该资源,只能等待,...
  • C++ 线程实现死锁

    2021-04-02 15:08:38
    什么是死锁——个线程循环等待对方释放所需的资源 一.死锁的发生 1. 忘记释放锁 void data_process() { EnterCriticalSection(); if(/* error happens */) return; LeaveCriticalSection(); } 2. 单线程...
  • Java codepublic class DeadLock { private static Integer accountA = 1000;... // 账户B,有2000元 public static void main(String... // join 只是负责等待这两笔转账业务的线程结束而已 a2b.join(); b2a.join(); } }
  • flink sql 并发写入 pg 表死锁 1、环境描述 1、flink 1.13.2 2、3个并发度[3个taskmanager],即任务会在三个节点[A、B、C节点]上跑 3、事实表join维度 2、死锁排查步骤 1、收到数据库运维监控表死锁告警 2、点...
  • 一、什么情况下会出现死锁假设有两个线程T1,T2。两个账户A,B。刚好某一个时刻在T1线程内A给B转账,在T2线程内B给A转账。代码如下:public static void main(String[] args) throws InterruptedException {Account a...
  • 个线程互相等待对方而导致永远暂停称为死锁。常见的死锁q情况是线程A持有锁L1的情况下申请锁L2,线程B持有锁L2申请锁L1。线程A等待锁L2释放,线程B等待锁L1释放,从而互相等待永远暂停。死锁的必要条件1. 资源互斥,...
  • 第十五章、Python线程同步锁,死锁和递归锁1. 引子:1.创建线程对象t1 = threading.Thread(target=say,args=('tony',))2.启动线程t1.start()后面又说了两个点就是join和守护线程的概念​以上就是python线程的...
  • 达梦死锁的解决

    2021-11-09 18:09:11
    本文用终止一个或者个进程的方式释放资源解决死锁,对此进行了一个简单测试。 2、死锁测试 2.1 建表插数据 create table testlock (id int,name char(30)); insert into testlock values(1,'aaa'); insert into
  • 1.mysql都有什么锁MySQL有三种锁的级别:页级、级、行级。级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突...
  • 1. 死锁概念 2. 死锁程序模拟 3. 死锁堆栈信息
  • SQL Server表死锁

    2021-02-28 09:16:08
    然后企图访问A 这时用户A由于用户B已经锁住B,它必须等待用户B释放B,才能继续,好了他老人家就只好老老实实在这等了 同样用户B要等用户A释放A才能继续这就死锁了 解决方法: 这种死锁是由于你的程序的BUG...
  • 线程开发过程中很多人应该都会遇到死锁问题,死锁问题也是面试过程中经常被问到的问题,这里介绍在c++中如何使用gdb+python脚本调试死锁问题,以及如何在程序运行过程中检测死锁。首先介绍什么是死锁,看下维基...
  • java线程join()方法的作用和实现原理

    多人点赞 热门讨论 2021-07-04 18:16:45
    join() 方法的作用 这个方法的作用是先将当前线程挂起,待其他线程结束后在执行当前线程的代码; 应用场景 比如有三个人小红、小李、小王, 三个人相约一起去酒店吃饭,菜已经点好了,三个人从不同的地方出发,...
  • 1. 线程编程与线程安全相关重要概念 开始之前,我们熟悉了几个特别重要的概念:GIL,线程,进程, 线程安全,原子操作。 GIL: Global Interpreter Lock,全局解释器锁。 Cpython解释器上的一把互斥锁,不能利用...
  • = 'STOP': result = _entropy_split3(multi_list, feature_index) done_queue.put(result) feature_index = task_queue.get() 当我运行我的程序,它通过_entropy_split_parallel工作正常次运行,但最终死锁。...
  • Python是一种通用语言,可用于编写任何类型的程序。同时,Python是一种简单易学且功能强大的编程语言,也是现阶段世界上增长最快的...如何使用Python解决线程死锁问题等。下面我们就来详细了解一下。python如何...
  • 高并发和死锁

    2021-06-23 12:27:20
    并发的另一个途径是单个进程中运行个线程,线程在很书中都被称为“轻量级的进程”,每个线程可以相互独立运行,但是进程中的所有线程都享有共同的地址空间,并且线程间拥有不少共享数据(那么线程间同步和死锁之...
  • mysql死锁示例

    2021-01-19 04:31:55
    MySQL有三种锁的级别:页级、级、行级。MyISAM和MEMORY存储引擎采用的是级锁(table-level locking);BDB存储引擎采用的是页面锁(page-levellocking),但也支持级锁;InnoDB存储引擎既支持行级锁(row-level ...
  • 线程编程中,要执行synchronized块,必须首先获得指定对象的锁。1.Java的线程锁是可重入的锁public void add(int m){synchronized (lock){this.value += m;}}什么叫可重入的锁? 对同一个对象,同一个线程,可以...
  • 哲学家就餐问题(如何避免死锁)(线程版)

    千次阅读 热门讨论 2021-02-22 21:42:18
    线程编程中,常常会遇到线程间访问共享资源的问题,如果处理不当则会发生死锁,某一个线程可能永远访问不到共享资源。 为了避免死锁的发生,提出哲学家就餐问题。 下面展示一些代码片段 #include <stdio.h> ...
  • 互斥量的基本概念临界资源:每次只允许一个线程进行访问(读/写)的资源线程间的互斥(竞争):个线程在同一时刻都需要访问临界资源mutex 类(互斥量)是一把线程锁,保证线程间的互斥利用线程锁能够保证临界资源的安全...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 63,221
精华内容 25,288
热门标签
关键字:

多表join死锁

友情链接: Edit68CMU_pack.zip