Elegant solution to write to a database as well as a rfid card in a somewhat atomic way

Question

Assuming you want to write some data to a RFID card and some data to a database. Theoretically the connection to the database might break at any time and the connection between the card and the card reader might break at any time too. Your goal is to design it in a kinda atomic way that you either end up with both or none being written.

Is there any elegant approach to solve this? Any hints are appreciated :)

(I'm using MS SQL Server and the Windows Smart Card Module (Winscard.dll) in case it makes any difference)

Answer 1

Insert a record before you start your update of the RFID card, then update this record when complete. In this way you'll also be able to spot half-completed updates, and act accordingly.

1. Insert a record into the database with a status of 'incomplete'. Give it a timestamp, a unique id, and any other pertinent information you require to uniquely describe the RFID write attempt
2. Write to RFID card
3. Update the record inserted in step 1. with a 'completed' or 'failure' status

Then have a process which resolves incomplete records after a timeout period has elapsed (either by trying again, or whatever else you want to do).

Do not try to hold open a database transaction while you communicate with your RFID card, as this will:

1. Give you unwanted (and potentially lengthy) locks on your database
2. Rollback upon failure, which will remove any evidence you tried to do something to your RFID card