StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Server transactions in Powershell
text
Body
copied!<p>I am new to Powershell scripting and SQL server. I am trying to write a test case to verify the integrity of SQL server database (running inside a VM) w.r.t backups.</p> <p>The goal is to check that when a backup was taken in the middle of a transaction, database is still consistent after the restore.</p> <p>My test case takes periodic backups of the SQL server VM while another powershell script performs database transactions in parallel (transferring money from one account to another).</p> <p>I frequently find that database is inconsistent: sum of the money from all accounts is lesser than the initial deposit.</p> <p>I am wondering if the SQL server transaction logic is buggy. So, can anybody see what is wrong with the below Powershell and SQL Server code? Does it get the transaction semantics right?</p> <pre><code>Function TransferMoney { $conn = $args[0] $from = $args[1] $to = $args[2] $amount = $args[3] $conn.BeginTransaction() # Keep this transaction intentionally dumb, so that it takes longer to # execute and Uvm has more chance of getting replicated in the middle of the # transaction. # Read the current balances. $reader = $conn.ExecuteReader("SELECT balance FROM $tableName WHERE id=$from") $reader.Read() $from_balance = $reader.GetValue(0) $reader.close() $reader = $conn.ExecuteReader("SELECT balance FROM $tableName WHERE id=$to") $reader.Read() $to_balance = $reader.GetValue(0) $reader.close() $from_balance = $from_balance - $amount $to_balance = $to_balance + $amount $conn.ExecuteNonQuery("UPDATE $tableName SET balance=$from_balance WHERE id=$from") $conn.ExecuteNonQuery("UPDATE $tableName SET balance=$to_balance WHERE id=$to") $conn.CommitTransaction() Write-Output "$amount dollars are transferred from account $from to $to. Current balances are $from_balance and $to_balance dollars respectively." } Function WorkUnit { $from = Get-Random -minimum 0 -maximum $numAccounts $to = ($from + 1) % $numAccounts $conn = CreateConnection $conn.ExecuteNonQuery("SET XACT_ABORT ON") $conn.ExecuteNonQuery("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE") # Transfer money from one account to another. Transactions may fail if # multiple jobs pick conflicting account numbers, in which case ignore that # transfer. Since we use transactions, such failures wouldn't cause any # loss of money, so test should still succeed. TransferMoney $conn $from $to $from # Number of dollars transferred from an account is kept unique (the account # number itself) so that, in the event of data inconsistency, we can deduce # which transfer operation has caused the data inconsistency and it can be # helpful in debugging. } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload