背景
上篇文章我们介绍了MySQL支持的compound statement SQL的语法。有同学指出这个只是“部分compound”,因为这种多语句语法中不支持if这样的逻辑操作,因此在业务中的使用会受限。
这里我们给出一个常见的事务的逻辑。
1) 更新一行
2)若更新成功一行,则在另外一个表中插入一行,否则事务回滚。
在这样的需求下,就是由于少了逻辑判断,导致无法用compound statement发整个事务,因此需要多次交互。
新增HINT
在这类需求中我们发现,其实我们要的只是一个判断更新是否成功的判断。
因此设计这样的HINT及逻辑:
在UPDATE和DELETE语句中增加HINT /*TARGET_AFFECT_ROWS n*/表示这个语句要求修改n行,若不满足这个条件,则认为事务需要回滚。
使用
有了这个功能,在实现上诉的业务逻辑时,我们就能直接将以下语句发给Server:
delimiter ;;
begin; update /*TARGET_AFFECT_ROWS 1*/ t1 set b=b+1 where a=1 and b>=0; insert into ….; commit;;
这样t1表中a=1这行的b值<0时, 整个事务无效.
小结
其好处就是
1\ 在正常执行时,只需要一次交互
2\ 在需要回滚时,无需再发一个rollback命令。
另外,特别说明,整个方案idea绝大部分来自于 @dbatools
相关推荐
Several fields in computer science and economics are ...the participants act independently and their decisions affect the whole system. Following is a list of research fields that utilize Game Theory
APH010 DW1000 INTER-CHANNEL INTERFERENCE How transmissions on one DW1000 channel can affect other channels and how to minimize that effect
Datasheet for a compact 5" LCD monitor.
DLL: Renesas RX: When connecting to locked RX devices via JTAG (does not affect FINE!), 16-byte IDCODE (OSIS) could be rejected even though the correct code was given. Fixed. DLL: Renesas S7G2: QSPI ...
这是我自己写的一个登录界面,可以判断用户是否在数据库中
C# 百度文字识别Demo 源码,图片文字识别功能。包含exe和源码超值。
-target type specify target file type ("vcd", "svcd", "dvd", "dv", "dv50", "pal-vcd", "ntsc-svcd", ...) -apad audio pad -frames number set the number of frames to output -filter filter_graph set ...
GROUP_AFFECT_EMOTION_RECOGNITION
java语言编写的多目标优化算法源代码,感兴趣的可以研究研究
本设计主要研究设计了电能质量相关指标和影响的各要素,然后叙述国内外相关研究设计成果,介绍了有关电能质量方面的一些概念,给出本课题要研究设计的主要内容,在此基础上,进一步分析了哪些因素能够影响到电能质量...
To support SERIALIZABLE transaction semantics, SQL Server needs to lock sets of rows specified by a predicate, such as WHERE salary BETWEEN 30000 AND 50000 SQL Server needs to lock data that does ...
How will it affect you and your children? And how long will it be before some of the predictions occur? To avoid interruptions, I have put references and additional technical notes in endnotes. You ...
高精手持样机展示素材可用于adobe affect 作品展示样机、样机展示素材、移动应用展示样机
Before we look at how SQL Server uses and manages its memory, we need to ensure a full understanding of the more common memory related terms. The following definitions will help you understand how SQL...
;;;;;;;;... 1.... 2.... 3.... 4.... 5.... 6.... The syntax of the file is extremely simple.... Section headers (e.g.... at runtime.... There is no name validation.... (e.g.... previously set variable or directive (e.g....
The changes from Version 1.3 to Version 1.4 do not affect the test vectors. ---------------------------------------------------------------------------- DESCRIPTION ----------- This package ...
With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that ...
SQL Server 2005 Expre混合模式登录设置
Improve your ability to develop, manage, and troubleshoot SQL Server solutions by learning how different components work “under the hood,” and how they communicate with each other. The detailed ...
28-Sep-2011: EurekaLog 6.1.03 for Delphi and C++Builder is now out! This version contains ...changes will affect you (except the usual bug fixes). New compiler have no effect, until explicty enabled.