stored procedure issue

Question

Kindly assist on below error message. we are trying to execute a stored procedure on always on 2nd node and 3rd node

Msg 3949, Level 16, State 1, Procedure abc.def, Line 434 [Batch Start Line 0]

Transaction aborted when accessing versioned row in table 'abc.def' in database 'PROD'. Requested versioned row was not found because the readable secondary access is not allowed for the operation that attempted to create the version. This might be timing related, so try the query again later.

Completion time: 2023-07-04T08:44:39.3071725+04:00

Answer 1

I've never seen or heard about that error before, but it seems to be timing related.

I think you need to wrap the statement where this happens with:

DECLARE @NoOfAttempts int = 0
WHILE @NoOfAttempts < 5
BEGIN TRY
    SET @NoOfAttempts += 1
     -- Your statement here
    BREAK
END TRY
BEGIN CATCH
   IF error_number() = 3449
      CONTINUE
   ; THROW
END CATCH

Answer 2

Your error message indicates a problem accessing a versioned row in the table 'abc.def' in the 'PROD' database. This issue occurs when accessing a versioned row on a readable secondary replica in an Always On Availability Group.

I am assuming the following since you didn't provide enough details :

By default, readable secondary replicas in an Always On Availability Group are set to disallow connections. Ensure that the readable secondary replicas are appropriately configured to allow read access. You can check this configuration in the Availability Group settings and modify it if necessary.

If the readable secondary replicas are set to allow read access, the error message suggests that the problem might betiming-related. It's possible that the secondary replica is experiencing synchronization delay and hasn't yet received the versioned row. In this case, you can try running the stored procedure again later when the synchronization has caught up.

Ensure no conflicts or locking issues prevent the readable secondary replicas from accessing the versioned row. Review any concurrent operations, locks, or conflicts occurring on the primary or secondary replicas.