对你的问题的简单回答是：
是的，MySql 支持FOR UPDATE子查询中的子句
但是，这肯定不能解决您的问题。
在这种情况下，子查询中的 FOR UPDATE 不能防止死锁
由于您没有向我们展示整个事务，而只向我们展示了一个片段，因此我猜测事务中一定有其他命令在外键引用的记录上放置了锁。
为了更好地理解 MySql 中的锁定是如何工作的，请看一下这个简单的示例：
CREATE TABLE `a` (
`id` int(11) primary key AUTO_INCREMENT,
`a_field` int(11)
);
CREATE TABLE `b` (
`id` int(11) primary key AUTO_INCREMENT,
`a_id` int(11),
`b_field` int(11),
CONSTRAINT `b_fk_aid` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`)
);
CREATE TABLE `c` (
`id` int(11) primary key AUTO_INCREMENT,
`a_id` int(11),
`c_field` int(11),
CONSTRAINT `c_fk_aid` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`)
);
insert into a( a_field ) values ( 10 ), ( 20 );
insert into b( a_id, b_field ) values ( 1, 20 ), ( 2, 30 );
delimiter $$
create procedure test( p_a_id int, p_count int )
begin
declare i int;
set i = 0;
REPEAT
START TRANSACTION;
INSERT INTO c( a_id, c_field ) values ( p_a_id, round(rand() * 100) );
UPDATE a
SET a_field = (
SELECT sum(b_field)
FROM b WHERE b.a_id = a.id
FOR UPDATE)
WHERE
id = p_a_id;
commit;
set i = i + 1;
until i > p_count
end repeat;
end $$
DELIMITER ;
请注意FOR UPDATE用于子查询中。
如果我们同时在两个会话中执行该过程：
call test( 2, 400 );
我们几乎立刻就得到了一个死锁错误：
------------------------
LATEST DETECTED DEADLOCK
------------------------
2013-09-05 23:08:27 1b8c
*** (1) TRANSACTION:
TRANSACTION 1388056, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 6, OS thread handle 0x1db0, query id 3107246 localhost 127.0.0.1 test updating
UPDATE a
SET a_field = (
SELECT sum(b_field)
FROM b WHERE b.a_id = a.id
FOR UPDATE)
WHERE
id = p_a_id
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 222 page no 3 n bits 72 index `PRIMARY` of table `test`.`a` trx id 1388056 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000000152e16; asc . ;;
2: len 7; hex 2d0000013b285a; asc - ;(Z;;
3: len 4; hex 8000001e; asc ;;
*** (2) TRANSACTION:
TRANSACTION 1388057, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 2, locked 2
5 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 0x1b8c, query id 3107247 localhost 127.0.0.1 test updating
UPDATE a
SET a_field = (
SELECT sum(b_field)
FROM b WHERE b.a_id = a.id
FOR UPDATE)
WHERE
id = p_a_id
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 222 page no 3 n bits 72 index `PRIMARY` of table `test`.`a` trx id 1388057 lock mode S locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000000152e16; asc . ;;
2: len 7; hex 2d0000013b285a; asc - ;(Z;;
3: len 4; hex 8000001e; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 222 page no 3 n bits 72 index `PRIMARY` of table `test`.`a` trx id 1388057 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000000152e16; asc . ;;
2: len 7; hex 2d0000013b285a; asc - ;(Z;;
3: len 4; hex 8000001e; asc ;;
*** WE ROLL BACK TRANSACTION (2)
------------
正如您所看到的，MySql 报告死锁错误是由相同的两个 UPDAT 引起的。
然而，这只是事实的一半。
死锁错误的真正原因是INSERT INTO c语句，该语句在 A 表中的引用记录上放置了共享锁（因为 A 表中的 FOREIGN KEY 约束）C table).
并且 - 令人惊讶的是 - 为了防止死锁，必须在行上放置一个锁A事务开始时的表：
declare dummy int;
......
START TRANSACTION;
SELECT id INTO dummy FROM A
WHERE id = p_a_id FOR UPDATE;
INSERT INTO c( a_id, c_field ) values ( p_a_id, round(rand() * 100) );
UPDATE a
SET a_field = (
SELECT sum(b_field)
FROM b WHERE b.a_id = a.id
)
WHERE
id = p_a_id;
commit;
进行此更改后，过程运行不会出现死锁。
因此，您可以尝试添加SELECT ... FROM A ... FOR UPDATE在您的交易开始时。
但如果这不起作用，要获得进一步帮助来解决此问题，请：
- 显示整个事务（事务中涉及的所有命令）
- 显示事务使用的所有表的结构
- 显示在插入/更新/删除时触发的触发器，这些触发器修改事务涉及的表
