Comments (18)
SELECT
MessageId,
MessageTypeFROM
{_prefix}.queue LIMIT 1 FOR UPDATE;
不确定是否这个sql语句在等待锁时导致的错误,但是按以上的消费者代码及cap配置的确抛出了以上的错误信息,不知道我有没有把问题描述清楚了?
from cap.
PS: 前提条件-消息生产者生产了大量消息
for(var i=0;i<10000;i++)
{
publisher.Publish("xxx.services.account.check", new Person { Name = "Foo", Age = 11 });
}
from cap.
消费者执行中时,查询这个语句会一直等待锁直到超时,是否这个语句导致表锁了,而不是行锁。
查询资料:
由于InnoDB 预设是Row-Level Lock,所以只有「明确」的指定主键,MySQL 才会执行Row lock (只锁住被选取的数据) ,否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。
如果优化语句变为行锁,能否减低这种错误的发生?
from cap.
这个我不是很确定你的问题是这个原因导致的,你可以试试添加一个联合主键到 Queue 表看还会不会出现这个情况。
I'm not sure that your question is caused by this, but you can try create a composite primary keys to see if it's going to happen.
TRUNCATE TABLE `cap.queue`; -- 清空表数据
ALTER TABLE `cap.queue` ADD PRIMARY KEY (MessageId, MessageType); -- 添加联合主键
from cap.
Hi @yuleyule66
我添加了联合主键,刚刚测试过了,还会报同样的错误。
我的理解是:
首先CAP有个Processor会不断轮循cap.queue表,去处理表里的消息,
当触发DotNetCore.CAP.MySql.MySqlStorageConnection.FetchNextMessageCoreAsync方法时,
会开始一个事务,执行了以下的语句
var sql = $@"
SELECT `MessageId`,`MessageType` FROM `{_prefix}.queue` LIMIT 1 FOR UPDATE;
DELETE FROM `{_prefix}.queue` LIMIT 1;";
这时事务并未立即提交,是要等到消费者订阅方法处理成功后,才提交事务,才会释放锁表操作。
现在的疑问是:
当我设置QueueProcessorCount = 5,
第一个消费者线程在执行订阅方法时,由于太耗时,会一直锁住cap.queue表,
当后续线程陆续触发FetchNextMessageCoreAsync方法时,会一直等待表锁,直到db command timeout,
然后就一直不断抛出超时错误,程序没办法正常运行,除非手动关闭程序,再重新启动,然后这样不断循环。
想知道您觉得是否因为这样的原因导致这个错误发生的呢?
from cap.
是这个原因导致的。
所以 MySQL 在消费消息的时候性能差,就是因为 MySql 不支持 Skip Locked
语法来跳过被锁定的行。SQL Server, PostgreSQL , Oracle 均有相关语法支持。
MySQL 直到 8.0+ 才提供 Skip Locked
的支持
from cap.
已经在用MySql8.0了,是否考虑升级下代码,或者做个兼容。
测试过程中同样遇到类似错误,有多个线程在检测cap.queue表数据时,出现超时错误。
还有时报这个https://github.com/dotnetcore/CAP/issues/63,但在MQ中取消绑定仍然会出现,清空cap.received表后不再报错。
from cap.
mysql的for update会造成cap.queue表被锁定,下一句的for update需要等待,我觉得是不是可以有其他方法,绕过这个性能问题
from cap.
for update获取得了排它锁,未释放前下一句for update一直在等待,使用Mysql旧版本(不支持skip locked)性能真的很有问题,不仅是消费时,发布消息同样有这个问题。
@yuleyule66 不知道能否在mysql 实现时,不使用排它锁,cap.queue增加个字段status, 如果处理中就更新为processing, 然后FetchNextMessageAsync时就查询不在状态processing的记录,
from cap.
@ajdwfnhaps 可以考虑你的建议,待我衡量一下,这两天比较忙。
from cap.
@yuleyule66 感谢大大
from cap.
@ajdwfnhaps 只要queue的指令不commit,排他锁这个问题没有办法解决, limit 1也会触发.我在想,不用queue的话会有哪些问题?
from cap.
@suxi 不加for update 的select ... limit 1应该不会获得排他锁的啊,没有X锁,FetchNextMessageAsync时就不用再等待了,问题是不用排他锁,能否有什么好的办法可以确保cap.queue里的消息不会被多线程实例重复消费的问题
from cap.
@ajdwfnhaps 消息里面有messageid,可以一定程度避免多发,outbox模式本身没有这个queue的必要性. 关于锁,你可以试试看.我现在准备跳过这个queue,直接发送message
from cap.
@ajdwfnhaps 这个问题解决了。
2.1.2-preview-30288174
版本取消了mysql消费时候的事务转而采用的是当失败的时候重新将消息写入队列,成功后删除队列中的消息。
from cap.
收到,太好了,我明天再测试测试,感谢
from cap.
@yuleyule66 棒极了,刚测试了一遍,一切正常,没有了整个事务的锁,性能提升了一倍。感谢作者~~
from cap.
Fixed in version 2.1.2
from cap.
Related Issues (20)
- CAP has not been started! HOT 4
- Question about parallel execution HOT 6
- Error in Nats transport HOT 4
- 能不能加个配置,可以单独启用消费功能或发送功能 ? HOT 2
- How to Introduce Multiple Database Providers? HOT 1
- .NET Core 8 使用RabbitMQ 报No ip address could be resolved for【hostName】的问题 HOT 1
- Optimizing RabbitMQ Performance and Managing Multiple Instances in CAP HOT 2
- Feature Request: Per-Queue Configuration for BasicQos and ConsumerThreadCount HOT 3
- 消息持久化支持达梦数据库吗 HOT 1
- Is the message data structure compatible with older versions of 3.0? HOT 1
- Add new option for parallel publish send. HOT 2
- Add option for controlling reponse headers from OnSubscribeExecuted HOT 2
- NATS Connection Error after Restarting nats-server: Unable to Publish Messages HOT 5
- SQL Server replication readpast error! "You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels" HOT 3
- 一个进程内可以创建多个CAP吗? HOT 1
- NATS feature: add the possibility to disable dynamic consumer subject/topic creation through NatsCapOtpions HOT 2
- 一个程序进程会操作多个不同的业务数据库,参考#998创建多个发布者进行对应处理,消费者应该如何处理消息? HOT 1
- Feature Request: Add New Column in table Cap.published for relation to table every single data publish HOT 2
- Callback on IIS Idle state HOT 2
- Cap-dashboard empty using dynamic subscribers with Azure Service Bus HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from cap.