Avoid SQL Deadlocks -- Break Up Large Updates
- 2 minutes - May 8, 2014
- #sql-server#mysql#sql
Deadlocks in SQL occur when one query locks certain rows, frequently for updates, and a second query tries to update those same rows. The second query will then create an error as those rows are unable to be updated since they are in the middle of an update from another query. One of the surefire ways to create a slow running update query like the first query above is to hava a single update statement that will update a large number of rows at once.
Read MoreHow Not to Use SQL Transactions
- 2 minutes - Apr 11, 2014
- #microsoft#sql-server
SQL Transactions allow you to isolate atomic operations so that you can ensure that a third party does not update the data affected during the atomic operation protected by the transaction. An example of an operation that you would want to protect with a SQL Transaction would be transferring funds from one bank account to another. The first step of this operation would be to subtract the funds from bank account A.
Read MoreUnexpected Results with SQL Server and Python pyodbc
- 2 minutes - Apr 10, 2014
- #microsoft#sql-server#python
Using the Microsoft SQL Server Management Studio (SSMS) with SQL Server hides many of the API complications that can sometimes arise when working with SQL Server. One specific example would be when using Python on Windows with the pyodbc driver. If you have an update statement that performs a simple update to a status column and a datetime column, you can have some unexpected results. Lets say that the table you are running the update against has a before update trigger and an after update trigger configured on it.
Read MoreSQL Server Transaction Log Exponential Growth
- One minute - Apr 9, 2014
- #microsoft#sql-server#python
There are few things more frustrating than seemingly random issues that crop up in software when configuration changes occur. One such occurrence is when you migrate your databases from Microsoft SQL Server 2012 Standard Edition to Microsoft SQL Server 2012 Enterprise Edition with High Availability and the transaction log suddenly begins to experience exponential growth without ceasing. It turns out that when using Python and pyodbc on Windows to access SQL Server, there can be some unpredictable results.
Read More