0

MS SQL Server 2008 R2

Say we have the following tables:

**MoneyTransactions**
 - Amount (int)
 - CustomerId (int)
 ...

Customers
 - Id (int)
 - balance (int)
 ...

Is the following operation thread safe if execute 100 times simultaniously?:

INSERT INTO Transactions(Amount, CustomerId, ...) Values(@myAmount, @customerId, ...)
UPDATE Customers SET Balance = Balance + @myAmount WHERE Id = @customerId

Is my update executed atomically or can simultanious executions interfer and make the balance corrupt comparred to the sum of Amounts in Transactions?

I would like to avoid a BEGIN TRAN/COMMIT TRAN if possible.

Stephan Møller
  • 1,247
  • 19
  • 39
  • 1
    Why do you want to avoid `BEGIN \ COMMIT` ? – Jonny Jul 04 '14 at 09:20
  • If I use a BEGIN/COMMIT, around the insert and update, wouldnt I lock the two tables in the process? – Stephan Møller Jul 04 '14 at 09:21
  • 2
    Don't see any issue with that, I think SQL Server would cope with row locks while data is being modified, have you tried testing it? – Tanner Jul 04 '14 at 09:22
  • possible duplicate of [What happens when multiple simultaneous update requests received for a SQL table?](http://stackoverflow.com/questions/13087197/what-happens-when-multiple-simultaneous-update-requests-received-for-a-sql-table) – Tanner Jul 04 '14 at 09:23
  • No I havent tested to see the difference in performance - my question is just a general desire to not lock or use transactions if not necessary :) – Stephan Møller Jul 04 '14 at 09:23
  • SQL Server manages inserts/updates with it's own locking mechanisms whether you want them or not, it's the only way to ensure scenarios like this can be managed – Tanner Jul 04 '14 at 09:25
  • You should make it as a transaction. Even thought you prefer to avoid it, it is the safest solution. If one part fails I assume you want the whole thing to fail. In your current senario, part 1 could work and part 2 could fail. A different and even more safe approach would be making a trigger on Transactions to keep Customers updated – t-clausen.dk Jul 04 '14 at 09:31
  • t-clausen: You are totally right, that in my case, I cannot accept the first line succeeding and the next failing. So thanks for pointing this out. :) – Stephan Møller Jul 04 '14 at 14:55

1 Answers1

-2
  1. If it is two separate SQL queries you can guarantee that they are thread safe in general case.
  2. Even you not use explicit transactions SQL Server can use implicit transactions. Every time you execute SQL query MS SQL Server use the blocking.
  3. The locks on the tables depends in transaction isolation level you use.
ceth
  • 44,198
  • 62
  • 180
  • 289
  • Thanks for your comment. Regarding 1: Does this mean, that if I execute millions of the update alone, I should not fear inconsistency as a result? Then I guess it aswers my question. – Stephan Møller Jul 04 '14 at 09:26
  • Yes, as I know "one sql server statement == one implicit transaction". – ceth Jul 04 '14 at 09:31
  • Using a single statement or single tran does not guarantee anything at all regarding concurrency bugs. – usr Jul 04 '14 at 10:09
  • @usr, can you provide an example ? – ceth Jul 04 '14 at 10:23
  • Scan a table under read committed and you can miss rows or see them duplicated in the presence of concurrent writes. This is a simple example for a general problem. – usr Jul 04 '14 at 10:29
  • @usr, Not sure I understand you. If I use READ COMMITED isolation level and execute one long SELECT I cannot see all other UNCOMMITED transactions. Even I don't use the explicit transactions. Yes I still phantom records and so on, but we talk about READ COMMITED isolation level and this isolation level doesn't protect us against them (use we explicit transactions or not). It doesn't depend on use we explicit transactions or not - every SELECT statement creates shared locks on SQL Server. How long do they live - this what depends on implicit transactions. – ceth Jul 04 '14 at 11:22
  • My point is that the OP asks "Is the following operation thread safe" and you say yes which is not true. – usr Jul 04 '14 at 11:25
  • Well, in this point of view it is not thread safe anyway - use he emplicit transactions or not. Are you agree ? My point is "one query without explicit transacions is the same thread safe as it was without eplicit transactions". – ceth Jul 04 '14 at 11:31
  • Yes, even with explicit transaction this is not safe in general. – usr Jul 04 '14 at 12:07