mysqlservicestackormlite-servicestack

ServiceStack OrmLite: .Save/SaveAsync generates Duplicate entry on PRIMARY key

I was under the impression that the OrmLite Save method did an "upsert", update if record/row exist, otherwise insert. In the documentation for ServiceStack OrmLite, it says:

Save and SaveAll will Insert if no record with Id exists, otherwise it Updates.

However, sometimes I get the duplicate entry error:

Duplicate entry 'PresentationWorker.Presentations.CoreObjectListPresentation-Pres' for key 'presentationproducer.PRIMARY

The POCO has an Id property that is PrimaryKey

[PrimaryKey]
[StringLength(512)]
public string Id { get; set; }

Also, the database in MySQL has Id as the Primary key:

enter image description here

  1. The first thing that looks strange to me is the error message, where it indicates that the value in the PRIMARY field ends with CoreObjectListPresentation-Pres, but it does not, it is actually longer than that, the entire value in the Id field is PresentationWorker.Presentations.CoreObjectListPresentation-PresentationWorkerModule:TED-RYZEN:37576

The Id prop in the POCO being saved is however the same as already existing in db:

enter image description here

and db:

enter image description here

I would like to understand how I can get the error, if it is supposed to do an Update if there already is a PRIMARY KEY with the same value? But as I enabled the logging feature, I can see that the SQL query is actually an INSERT, not an UPDATE:

SQL: INSERT INTO PresentationProducer (Id,PresentationType,ModuleChannelName,LastUpdatedTimestamp) VALUES (@Id,@PresentationType,@ModuleChannelName,@LastUpdatedTimestamp) PARAMS: @Id=PresentationWorker.Presentations.CoreObjectListPresentation-PresentationWorkerModule:TED-RYZEN:37576, @PresentationType=PresentationWorker.Presentations.CoreObjectListPresentation, @ModuleChannelName=PresentationWorkerModule:TED-RYZEN:37576, @LastUpdatedTimestamp=2021-11-21 09:09:02

I am considering some form of race condition, but I am not sure how exactly this happens. I tried finding SaveAsync in the source code, but have not found the relevant code for it.

Is there perhaps an improvement for OrmLite to use the INSERT ON DUPLICATE KEY UPDATE approach when Save methods are used for MySQL?

(Using ServiceStack 5.13.1, .NET5, VS2022, MySqlConnector and db is MySQL8)

Solution

  • OrmLite has to check if the record exists before it decides whether a db.Save* should either insert or update the record. This leaves room for the possibility that if 2 requests tried to concurrently save the same new record at the same there could be a race condition should the first insert occur just after the second requests check.

    Typically this is the result of a double posting bug firing off 2+ concurrent requests. Changing it to use explicit db.Insert* and db.Update* wont help since you'll get the same response with 2+ inserts.

    The proper way to handle this is to use the native upsert functionality in each RDBMS which OrmLite doesn't support yet, please vote for the feature request to get notified when the feature is implemented.