1

I have a C# process that is inserting records into a table within a transaction. During this time another process (an SQL stored procedure) is updating this table. The C# process is using serializable isolation mode and the SP is using read commit.

The table being updated has a datetime field named DateShipped which the C# process is inserting using the current time. It takes about 1s for the C# process to determine the values to use and execute the insert statement. Looking at the data in the table you can see all the records added within the transaction have about a 1s difference in the DateShipped field. Let's not worry about how to make this process better. I'm currently concerned with understanding how the SP is able to update a record that is within the transaction of another process and has not been committed.

There is another field TransID in the table defined as INT. The C# process is not specifying a value for this field when inserting so it defaults to NULL. The SP runs on a schedule and the first thing it does is update all the records where TransID IS NULL to a specified value. The SP is running within an explicit transaction.

There are times (not sure if it is always as I don't know how often they both run at the exact same time) when some of the records (the first so many) get updated with a TransID when the others within the same transaction do not.

How is it that the SP can see the first so many records, but not all of them?

Is there a way to use Extended Events to capture this?

Insert Results:

Insert Results

DateShipped             TransID
2020-02-12 00:26:47.680 514928
2020-02-12 00:26:49.090 514929
2020-02-12 00:26:50.057 514929
2020-02-12 00:26:51.027 514929
2020-02-12 00:26:51.963 514929
2020-02-12 00:26:52.887 514929
2020-02-12 00:26:53.807 514929
2020-02-12 00:26:54.747 514929
2020-02-12 00:26:55.713 514929
2020-02-12 00:26:56.700 514929
2020-02-12 00:26:57.700 514929
2020-02-12 00:26:58.653 514929
2020-02-12 00:26:59.620 514929
2020-02-12 00:27:00.573 514929

Can someone recommend a way to prevent this (updating an uncommitted insert from a different transaction) or capture relative information to show this is happening?

Additional Information

Process 1 Code (c#)

ADODB.Connection.BeginTrans()

... Several inline SELECTs and INSERTs via ADODB.Connection.Execute()

ADODB.Connection.CommitTrans()

The insert is setting the DateShipped field to the current date/time. So the DateShipped is the date/time of the insert.

Process 2 Code (stored procedure)

BEGIN TRANSACTION

SELECT @ID = (MAX(TransID) + 1) FROM dbo.tHostInterfaceExport
INSERT INTO dbo.tHostInterfaceExport ( TransID ) VALUES (@ID)

EXEC dbo.dloc_UpdateActivityTables @ID

COMMIT TRANSACTION

PROCEDURE dbo.dloc_UpdateActivityTables @TransID INT

UPDATE dbo.tActivityShipments
SET TransID = @TransID
WHERE TransID IS NULL

The issue is this UPDATE statement sometimes (probably only when the 2 processes run at the same time) only updates some of the first records inserted in process 1.

Here's another sample of data where the issue has happened. All of these records were written within the same transaction of process 1.

DateShipped             TransID
2020-02-12 10:54:29.157 244878
2020-02-12 10:54:30.063 244878
2020-02-12 10:54:30.940 244878
...
2020-02-12 10:56:31.290 244878
2020-02-12 10:56:31.867 244878
2020-02-12 10:56:32.493 244878  <-- why was this one and the above ones updated
2020-02-12 10:56:33.087 244879  <-- and not this one and the ones below?
2020-02-12 10:56:33.730 244879
2020-02-12 10:56:34.353 244879
...
2020-02-12 10:57:32.360 244879
2020-02-12 10:57:33.093 244879
2020-02-12 10:57:33.970 244879

Addition information as to when the TransID are created. The TransID is created at the start of process 2. This shows that process 2 was called at 2 separate times and 3 minutes apart.

TransID DateCreated
244878  2020-02-12 10:54:56.817
244879  2020-02-12 10:57:56.880

What's interesting here is that process 2 is scheduled to run every 60s, yet it was held up (waiting for process 1 to finish and commit the transaction) before finishing TransID 244878. I say this because the entry for TransID = 244879 should of happened at 10:55:56 or 10:56:56, but the process was still waiting for the previous update that was being blocked by the insert transaction which finished after the last insert at 10:57:33.970.

user1828271
  • 13
  • 3
  • There's no enough to go on here. There's nothing special about a transaction that is initiated from a C# program, so you should be able to test the interaction between the two processes using two SSMS query windows. – David Browne - Microsoft Feb 13 '20 at 15:22
  • I'm not able to duplicate the issue (using c# and SP or 2 sessions in SSMS). On the surface this should not be possible, yet it is happening many times throughout the day. I have stepped through the code in c# and started the SP once the first record is written to the table. The SP is blocked until the commit is executed. – user1828271 Feb 13 '20 at 17:39
  • 1
    It should not be possible to update not committed row. You should assume that row you inserted is committed, consider adding on update trigger to try gather more data (or add insert and update timestamps to your table). – Piotr Palka Feb 14 '20 at 18:14
  • Please post the code which is inserting and then updating these rows. – Piotr Palka Feb 14 '20 at 18:18
  • `The C# process is using serializable isolation mode and the SP is using read commit.` - that guarantees that the stored procedure can see *no* records of a *currently running* C# procedure. – GSerg Feb 17 '20 at 15:51
  • @GSerg I agree it should not happen, but it appears it it. My question is how can I show this is happening other than the end results? The current results are showing the times when the inserts happen and the inserts are within a transaction. However, when the update executes it is only seeing the first so many inserts. The update is running with isolation mode read commit. So what now? – user1828271 Feb 17 '20 at 16:36
  • Are you sure `dloc_UpdateActivityTables` does not run in several instances in parallel? – GSerg Feb 17 '20 at 16:43
  • @GSerg I no special code to limit parallelism, but would that really matter? The insert is still in a transaction and they would all need to wait. Right? – user1828271 Feb 17 '20 at 18:51
  • They would race against each other about calculating the `@ID`. If you can, use a [sequence](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15) instead of that. – GSerg Feb 17 '20 at 18:58
  • The dloc_UpdateActivityTables is only running once at a time. This can be seen by the 3 minute difference between the table that holds the TransID when it is created. – user1828271 Feb 17 '20 at 19:24

1 Answers1

0

The issue turned out to be a COMMIT within process 1 which was ending the transaction prematurely.

Process 1 was starting a transaction and then doing a bunch of work. Part of the work was calling a stored procedure that had a COMMIT. Once this was executed the transaction was ended and process 2 was able to continue.

I still need to understand why process 1 did not throw an error when it executed the intended commit transaction.

Thanks to Piotr for suggesting using a trigger to get additional information.

user1828271
  • 13
  • 3