transaction-isolation

How is a T-SQL transaction not thread-safe?

The following (sanitized) code sometimes produces these errors: Cannot drop the table 'database.dbo.Table', because it does not exist or you do not have permission. There is already an object named 'Table' in the database. begin transaction if exists (select 1 from database.Sys.Tables where name ='Table') begin d...

Commiting only Specific Changes made inside a TRANSACTION which may ROLLBACK

This is a significant edit from the original question, making it more concise and covering the points raised by existing answers... Is it possible to have mulitple changes made to multiple tables, inside a single transaction, and rollback only some of the changes? In the TSQL below, I would NOT want any of the changes made by "myLogSP"...

How should I go about implementing an "autonumber" field in SQL Server 2005?

I'm aware of IDENTITY fields but I have a feeling that I couldn't use one to solve my problem. Let's say I have multiple clients. Each client has multiple orders. Each client needs to have their orders numbered sequentially, specific to them. Example table structure: Orders: OrderID | ClientID | ClientOrderID | etc... Some example...

Is it okay if from within one stored procedure I call another one that sets a lower transaction isolation level?

I have a bunch of utility procedures that just check for some conditions in the database and return a flag result. These procedures are run with READ UNCOMMITTED isolation level, equivalent to WITH NOLOCK. I also have more complex procedures that are run with SERIALIZABLE isolation level. They also happen to have these same kind of chec...

NOLOCK vs. Transaction Isolation Level

What's the difference between using "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" and NOLOCK? Is one better than the other? ...

Transaction isolation level on an oledb-connection

I have an oledb-connection from clickview to a sql2005-server and I would like this connection to use transaction isolation level of read uncommitted. My seconde choice would be to set it on the user. How can I accomplish this? ...

Set default isolation level for Microsoft.Practices.EnterpriseLibrary

Hi all, I've got a .net 3.5 website that calls thousands of different stored procs using Microsoft.Practices.EnterpriseLibrary. We have been getting alot of timeouts and after some playing and testing the use of (nolock) on the end of the table join in stored procs works real well and reduces the timeouts. I want to now do this to al...

Crystal Reports with SQL Server 2005: Setting Transaction Isolation Level

Is there a way to specify the transaction isolation level when Crystal Reports queries a SQL Server 2005 database without resorting to any of the following: Encapsulating the report's query in a stored procedure that executes SET TRANSACTION ISOLATION LEVEL... before the query itself Hand-writing the SQL query in Crystal Reports to exe...

Locking in PHP to acheive a crtitical section - unexpected results with MySQL

The goal is to have a PHP script with a certain section of the code that can only be executed by one thread/process at a time. Some restrictions: semaphores not available on my system The manual mentions that flock() cannot be relied on in multi-threaded servers, so flock() is out. (confirmed this) So thought it would be possible (w...

Nhibernate : generates insert then update statements, in multithreaded environment leads to deadlock issues

I've experienced the following scenario when using NHibernate with SQL Server 2005. I have a business process which involves the following steps: Start transaction Create nhibernate mapped object Save nhibernate mapped object Perform other business workflow steps Update nhibernate mapped object in step 2 Commit transaction In a sing...