查看mysql所有事务，以及死锁解除方法

最新推荐文章于 2024-03-20 18:08:09 发布

布依前端

最新推荐文章于 2024-03-20 18:08:09 发布

阅读量2k

点赞数 1

文章标签：备份sql

本文链接：https://blog.csdn.net/qq_41444226/article/details/101569580

版权

– 用来查看当前运行的所以事务

SELECT * FROM information_schema.INNODB_TRX;

– 在执行结果中可以看到是否有表锁等待或者死锁，如果有死锁发生，可以通过下面的命令来杀掉当前运行的事务：

KILL 165667 ;

– KILL 后面的数字指的是 trx_mysql_thread_id 值。

– 清除表格数据，表格本身还存在
TRUNCATE TABLE tb_user_role

备份sql：
– 用户角色关系表
INSERT INTO prsystem.tb_user_role (id, user_id, role_id) VALUES (‘21’, ‘2’, ‘1168865555508215809’);

– 用户表
INSERT INTO prsystem.tb_user (userid, username, password, phone, updatetime, phonecode, status, deleted, type, create_id, create_person, create_time, update_id, update_person, update_time) VALUES (‘2’, ‘admin’, ‘25d55ad283aa400af464c76d713c07ad’, ‘15284636598’, NULL, NULL, ‘0’, ‘1’, ‘1’, NULL, NULL, NULL, NULL, NULL, NULL);

– 角色表
INSERT INTO prsystem.tb_role (id, name, create_id, create_person, create_time, status, update_id, update_person, update_time) VALUES (‘1168865555508215809’, ‘系统管理员’, ‘0’, ‘admin’, ‘2019-09-03 20:37:18’, ‘1’, ‘2’, ‘admin’, ‘2019-09-07 08:26:08’);

– 角色权限关系表
INSERT INTO prsystem.tb_role_permission (id, role_id, permission_id) VALUES (‘45’, ‘1168865555508215809’, ‘1’);
INSERT INTO prsystem.tb_role_permission (id, role_id, permission_id) VALUES (‘46’, ‘1168865555508215809’, ‘2’);
INSERT INTO prsystem.tb_role_permission (id, role_id, permission_id) VALUES (‘47’, ‘1168865555508215809’, ‘3’);
INSERT INTO prsystem.tb_role_permission (id, role_id, permission_id) VALUES (‘48’, ‘1168865555508215809’, ‘4’);
INSERT INTO prsystem.tb_role_permission (id, role_id, permission_id) VALUES (‘49’, ‘1168865555508215809’, ‘5’);
INSERT INTO prsystem.tb_role_permission (id, role_id, permission_id) VALUES (‘50’, ‘1168865555508215809’, ‘6’);
INSERT INTO prsystem.tb_role_permission (id, role_id, permission_id) VALUES (‘51’, ‘1168865555508215809’, ‘7’);
INSERT INTO prsystem.tb_role_permission (id, role_id, permission_id) VALUES (‘52’, ‘1168865555508215809’, ‘8’);
INSERT INTO prsystem.tb_role_permission (id, role_id, permission_id) VALUES (‘53’, ‘1168865555508215809’, ‘9’);

– 权限表
INSERT INTO prsystem.tb_permission (id, title, pid, url, icon, status, create_id, create_person, create_time, update_id, update_person, update_time) VALUES (‘1’, ‘系统菜单’, ‘0’, ‘/system’, ‘layui-icon layui-icon-cellphone’, ‘1’, NULL, NULL, NULL, ‘2’, ‘admin’, ‘2019-09-23 22:35:59’);
INSERT INTO prsystem.tb_permission (id, title, pid, url, icon, status, create_id, create_person, create_time, update_id, update_person, update_time) VALUES (‘2’, ‘系统管理’, ‘1’, ‘/sysmana’, ‘layui-icon layui-icon-set’, ‘1’, NULL, NULL, ‘2019-09-14 16:04:58’, ‘2’, ‘admin’, ‘2019-09-23 22:35:59’);
INSERT INTO prsystem.tb_permission (id, title, pid, url, icon, status, create_id, create_person, create_time, update_id, update_person, update_time) VALUES (‘3’, ‘用户管理’, ‘2’, ‘/sys_user/tousermana’, ‘layui-icon layui-icon-user’, ‘1’, NULL, NULL, ‘2019-09-14 16:06:33’, ‘2’, ‘admin’, ‘2019-09-23 22:35:59’);
INSERT INTO prsystem.tb_permission (id, title, pid, url, icon, status, create_id, create_person, create_time, update_id, update_person, update_time) VALUES (‘4’, ‘角色管理’, ‘2’, ‘/sys_user/toRoleMana’, ‘layui-icon layui-icon-cellphone’, ‘1’, NULL, NULL, ‘2019-09-14 16:06:35’, ‘2’, ‘admin’, ‘2019-09-23 22:35:59’);
INSERT INTO prsystem.tb_permission (id, title, pid, url, icon, status, create_id, create_person, create_time, update_id, update_person, update_time) VALUES (‘5’, ‘权限管理’, ‘2’, ‘/sys_user/toPermissionMana’, ‘layui-icon layui-icon-vercode’, ‘1’, NULL, NULL, ‘2019-09-17 16:06:37’, ‘2’, ‘admin’, ‘2019-09-23 22:35:59’);
INSERT INTO prsystem.tb_permission (id, title, pid, url, icon, status, create_id, create_person, create_time, update_id, update_person, update_time) VALUES (‘6’, ‘密码重置’, ‘1’, ‘/test’, ‘layui-icon layui-icon-password’, ‘1’, NULL, NULL, ‘2019-09-02 16:09:33’, ‘2’, ‘admin’, ‘2019-09-23 22:35:59’);
INSERT INTO prsystem.tb_permission (id, title, pid, url, icon, status, create_id, create_person, create_time, update_id, update_person, update_time) VALUES (‘7’, ‘修改密码’, ‘6’, ‘/sys_user/toUpdatePasswordUI’, ‘layui-icon layui-icon-edit’, ‘1’, NULL, NULL, ‘2019-09-26 16:09:36’, ‘2’, ‘admin’, ‘2019-09-23 22:35:59’);
INSERT INTO prsystem.tb_permission (id, title, pid, url, icon, status, create_id, create_person, create_time, update_id, update_person, update_time) VALUES (‘8’, ‘关于作者’, ‘1’, ‘/test’, ‘layui-icon layui-icon-star’, ‘1’, NULL, NULL, ‘2019-09-17 16:09:39’, ‘2’, ‘admin’, ‘2019-09-23 22:35:59’);
INSERT INTO prsystem.tb_permission (id, title, pid, url, icon, status, create_id, create_person, create_time, update_id, update_person, update_time) VALUES (‘9’, ‘联系我们’, ‘8’, ‘/sys_user/conectionToUs’, ‘layui-icon layui-icon-link’, ‘1’, NULL, NULL, ‘2019-09-16 16:09:42’, ‘2’, ‘admin’, ‘2019-09-23 22:35:59’);