MySQL InnoDB 解锁一行
更新数据库中的某一行时出现锁定超时。其他行更新正常。
#1205 - Lock wait timeout exceeded; try restarting transaction
我怎样才能解锁这个特定的行？
这是两个相关的表。我正在尝试更新用户的电子邮件。但我认为租客不应该造成任何问题。
CREATE TABLE IF NOT EXISTS `mydb`.`user` (
`username` VARCHAR(45) NOT NULL ,
`email` VARCHAR(60) NOT NULL ,
`password` VARCHAR(45) NOT NULL ,
`created` TIMESTAMP NULL DEFAULT NULL ,
`last_login` TIMESTAMP NULL ,
PRIMARY KEY (`username`) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `mydb`.`tenant` (
`id` INT NOT NULL AUTO_INCREMENT ,
`username` VARCHAR(45) NOT NULL ,
`address` VARCHAR(90) NULL ,
`company` VARCHAR(45) NULL ,
`phone` VARCHAR(25) NOT NULL ,
`fax` VARCHAR(25) NULL ,
`notes` TEXT NULL ,
`contacts` TEXT NULL ,
PRIMARY KEY (`id`) ,
INDEX `fk_tenant_user1` (`username` ASC) ,
CONSTRAINT `fk_tenant_user1`
FOREIGN KEY (`username` )
REFERENCES `mydb`.`user` (`username` )
ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB;
I get a lock timeout when updating a certain row in the database. Other rows update okay.
#1205 - Lock wait timeout exceeded; try restarting transaction
How can I unlock this particular row?
Here are the two related tables. I'm trying to update the email on user. I don't think tenant should be causing any problems though.
CREATE TABLE IF NOT EXISTS `mydb`.`user` (
`username` VARCHAR(45) NOT NULL ,
`email` VARCHAR(60) NOT NULL ,
`password` VARCHAR(45) NOT NULL ,
`created` TIMESTAMP NULL DEFAULT NULL ,
`last_login` TIMESTAMP NULL ,
PRIMARY KEY (`username`) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `mydb`.`tenant` (
`id` INT NOT NULL AUTO_INCREMENT ,
`username` VARCHAR(45) NOT NULL ,
`address` VARCHAR(90) NULL ,
`company` VARCHAR(45) NULL ,
`phone` VARCHAR(25) NOT NULL ,
`fax` VARCHAR(25) NULL ,
`notes` TEXT NULL ,
`contacts` TEXT NULL ,
PRIMARY KEY (`id`) ,
INDEX `fk_tenant_user1` (`username` ASC) ,
CONSTRAINT `fk_tenant_user1`
FOREIGN KEY (`username` )
REFERENCES `mydb`.`user` (`username` )
ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我最终只运行了
FLUSH TABLE user，现在看起来很好。I ended up just running
FLUSH TABLE userand it seems fine now.尝试在交互式 mysql 客户端中运行以下命令：
并查看当前打开的事务。查看其中之一是否导致该行被锁定。
Try running the following in an interactive mysql client:
And look at the currently open transactions. See if one of them is causing the row to be locked.
就我而言，MySql 在 6-7 小时后自行修复。 （当我醒来解决这个问题时，它就消失了）
In my case MySql fixed itself after 6-7 hours. (When I wake up to solve this problem. It was gone)