jeudi 7 juillet 2016

TSQL SP Transaction Concurrency

I have a situation where I need to wrap an update sql in a SP (sp_update_queue) inside a transaction. But I'm wondering what would happen if you have two threads using the same connection but executing different queries and one rolls back a transaction it started.

For example ThreadA called sp_update_queue to update table QUEUED_TASKS but before sp_update_queue commits/rollback the transaction ThreadB executes some other update or insert sql on a different table, say CUSTOMERS. Then after ThreadB has finished, sp_update_queue happens to encounter an error and calls rollback.

Because they are both using the same connection would the rollback also rollback changes made by ThreadB?, regardless of whether ThreadB made its changes within a transaction or not.

Aucun commentaire:

Enregistrer un commentaire