A mechanism for committing a consistent set of changes into a database atomically.

learn more… | top users | synonyms

5
votes
1answer
237 views

Does the transaction isolation level affect indices as well?

Here's one for the SQL Server gurus: if I set my SQL Server 2008 transaction isolation level to READ UNCOMMITTED, does that also affect the index pages? E.g. using ISOLATION LEVEL READ UNCOMMITTED, ...
5
votes
2answers
5k views

What server property makes TransactionScope escalating to MSDTC?

In our application we're using TransactionScope's. We're aiming to not use the MSDTC service because it's a lot slower than lightweight transactions. using (var transactionScope = new ...
5
votes
3answers
846 views

Deadlock Error from SQL Server 2000 to SQL Server 2008

After migrating data from SQL Server 2000 to SQL Server 2008, when I am doing any a transaction in the table which find out more row on the database, that's gives me error. Transaction (Process ...
5
votes
1answer
55 views

About backing up Transaction logs

I have understood the importance of transaction logs but still unclear on a few things: I've taken back-up of a T-log using this statement: BACKUP LOG myDB TO DISK = 'I:\Log_Backup\myDB.TRN' GO ...
5
votes
1answer
287 views

Triggers and the Transaction

We recently made a change to create constraints based on logic previously used in stored procedures, and part of that included the use of INSTEAD OF triggers to centralize logic. The logic is ...
4
votes
2answers
2k views

Is it possible for SQL Server to miss rows on a select * where PK = “value” query

We have just migrated an application to an SQL Server 2008 R2 from SQL Server 2005 and moved from a physical to virtual hosting environment. I have a very strange error in our application and I was ...
4
votes
1answer
2k views

Sql Server - Select while insert on another transaction gives unexpected results

I stumbled at situation that changes my knowledge about transactions and locking fundamentally (I don't know much though), and I need help to understand it. Let's say I have a table like this: ...
4
votes
2answers
125 views

What are all the ways an open transaction will rollback?

When a transaction is opened in SQL Server, what are all of the ways that it can be rolled back? We are currently working with a 3rd party application that is frequently showing open transactions ...
4
votes
2answers
2k views

Oracle 11g see transaction or session start time

I want to know when a session or transaction started. The deadlock file doesn't give me this information. Is there some logfile that keeps these records? I've got a transaction id "TX-1234-abcd", a ...
4
votes
2answers
811 views

Synonyms and transactions

I'm working with an application that uses SQL synonyms to switch between tables on local and remote databases without duplicating code. BEGIN TRANSACTION if (@isRemote = 1) BEGIN CREATE ...
4
votes
2answers
797 views

T-SQL - Enforce Atomic Operations x Locks

I have a database model that works similar to a banking account (one table for operations, and a trigger to update the balance). I'm currently using SQL Server 2008 R2. Table OPERATIONS: VL_CREDIT ...
4
votes
2answers
3k views

mysql transaction vs lock

I have a question regarding MySQL locking and transaction management. My Question is ..does mysql takes lock on the the tuple/table on which I am doing select/update in a transaction ?
4
votes
1answer
1k views

Find out if two rows came from same transaction in Oracle

I am using Oracle 11.1 and would like to figure out if two rows in one table where inserted by the same transaction. Is there an easy sql statement to map a row to an transactions id? The table is an ...
4
votes
1answer
1k views

Transaction with multiple SQLCMD calls in a batch file

Is there a good way to do something like the following? SQLCMD -S %SERVER% -E -Q "BEGIN TRANSACTION" FOR %%f in (script1 script2 script3) do ( SET CURRENT_SCRIPT=%%f SQLCMD -S Localhost -E -b ...
4
votes
1answer
736 views

Locking in “Read Committed Snapshot”

If an update is run on a table with "Read Committed Snapshot" isolation level and a commit is pending eg: update table1 set col1 = col1 + 1 where PKcol < 3 update table1 set col1 = col1 + 1 ...
4
votes
1answer
275 views

Is SQL Server Replication transactionally 'safe' across all published tables?

We have a system that has one of its datasources still on SQL Server 2000. In order to achieve higher availability, we are looking to setup transactional replication from this 2000 server to a 2008 ...
4
votes
2answers
3k views

Concurrent Transactions vs Simultaneous Transactions

Chapter 6 of David Kroenke's Database Concepts: Database Administration Is my understanding correct that in a database, simultaneous transactions don't really happen. Instead, it is a concurrent ...
4
votes
1answer
106 views

Why are transactions running as distributed (DTCXact)?

I am actually trying to analyze and solve some regularly occurring deadlocks that all include at least one transaction named "DTCXact", this makes me ask. I repetitively run the following query ...
4
votes
1answer
119 views

transaction across multiple databases - what is the overhead?

I run the following update, which is one single transaction across 2 databases. Please note I have not committed the transaction. --================================================================ ...
4
votes
1answer
1k views

How to UPDATE just one record in DB2?

In DB2, I need to do a SELECT FROM UPDATE, to put an update + select in a single transaction. But I need to make sure to update only one record per transaction. Familiar with the LIMIT clause from ...
4
votes
1answer
495 views

Locking, deadlocking workaround while process data at the same table

My database has a table: tableX. Task1 makes intensive INSERTs (1000 records per minute) as records to process and very seldom UPDATEs (1-2 records per minute) as records to recalculate. At the ...
4
votes
1answer
1k views

Why is this rollback needed when using sp_addextendedproperty in a stored procedure?

In enter link description here I showed, how I document databases. To insert the extended properties, initially I uses plan sequences of sp_addextendedproperty calls. But lately I wanted to catch ...
4
votes
1answer
138 views

SQL 2012 CLR error: “The context transaction which was active before entering …”?

I am experience strange error with SQL 2012 and CLR: The context transaction which was active before entering user defined routine, trigger or aggregate "My routine" has been ended inside of ...
4
votes
1answer
74 views

Persistent inserts in a UDF even if the function aborts

I have a rather complicated UDF (moves around a bunch of tables as well as creates a bunch of new ones) where multiple aborts may occur. Before every operation I would like to record the time when an ...
4
votes
1answer
162 views

Why do row-versioning isolations use a linked list of versions of record? [closed]

Trying to better understand row-versioning based transaction isolations in SQL Server... According to Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation": ...
4
votes
1answer
383 views

Delete all rows within transaction: what would happen to other transactions?

I currently have a function in PostgreSQL that creates a temp table, fills it, performs some tricky selects and returns results. postgresql's temp tables are local to functions, so this helps me avoid ...
4
votes
1answer
71 views

Mysql transaction READ REPEATABLE and READ COMMITTED read view [closed]

I've come across something strage using Amazon RDS (MySQL 5.6.23). I had issues when concurrent transactions were locking and then calculating a value from multiple tables. The following is a quick ...
4
votes
1answer
249 views

Rollback on \include error in psql

I'm trying to execute an sql script file in a single transation. The file contains includes for some other scripts which in my example create some tables. It looks like this: \include ...
3
votes
2answers
5k views

Will a delete then insert within a transaction cascade?

I had a question about what the expected and actual behaviour for the following scenario is. The scenario is one where a database table ([table1]) is cleared and reloaded every day. The table has an ...
3
votes
3answers
2k views

Why am i getting deadlocks with 1 read and 1 write thread in SQL

if anyone could please help me understand why the following transactions deadlock? I am providing the 2 transactions and the indexes for each table: Transaction 1: SELECT blockId, blockData, ...
3
votes
2answers
75 views

Rules related to Transaction count

I was reading the examples on MSDN for TRY-CATCH blocks. The code listing for example B is as follows: BEGIN TRANSACTION; BEGIN TRY -- Generate a constraint violation error. DELETE FROM ...
3
votes
2answers
5k views

Query data before a commit transaction

My understanding is that, in a MS SQL Server Management Studio window, after doing a "begin transaction" and then making some changes like add data to a table , you can only query that table and those ...
3
votes
1answer
990 views

Distributed - across multiple databases or servers - Transactions

I have some doubts related to transactions in SQL. I perform below steps. Connection 1 open for DB1 database Begin transaction for Connection 1 Run the commands on Connection 1 Connection 2 open for ...
3
votes
2answers
97 views

Can we commit the transaction when the sql server trigger fails?

I am trying to avoid data loss when the insert trigger on a table fails. I am trying this with the following scenario and my code is failing. When an insert happens on the Customers table, I want to ...
3
votes
2answers
100 views

Transaction in a stored procedure

I need to perform an UPDATE and an INSERT in a single transaction. That code works fine on its own, but I'd like to be able to call it easily and pass in the required parameters. When I try to nest ...
3
votes
1answer
274 views

Does blocking always mean open transaction?

This may seem a bit dull question but does blocking always mean that there is open transaction and that may cause transaction log grow to infinity because open transaction prevents log truncation ...
3
votes
1answer
99 views

Why am I getting old data?

Today I discovered something weird on PostgreSQL 9.5. (I have no idea whether this is because of beta or not.) When I want to fetch data, I get old and deleted data from query. I then do VACUUM FULL ...
3
votes
1answer
10k views

Postgres requires commit or rollback after exception

Just trying to understand the thinking here... This bug explains that with other databases, you are allowed to run additional commands after a SQLException occurs. It was written in 2001 but has ...
3
votes
2answers
2k views

How to minimize log operations in SQL Server to avoid “log full” error

I have a database in production which constantly get log file filled, its a data warehouse and have many jobs/queries running for obvious. Below is the error which I am getting Msg 9002, Level 17, ...
3
votes
1answer
2k views

Perform reads and writes in the same transaction

I'm trying to implement transactions in my application. I'm just trying to implement it like the example shown in the BeginTransaction() documentation. Public Shared Sub Process(wwid As String, ...
3
votes
2answers
389 views

Separate table for 'scheduled payments' and 'completed payments'?

In a previous financial payments related database I'd worked on, it just had one large transactions table. We would insert scheduled transactions for the future, and when that time came, we'd execute ...
3
votes
1answer
3k views

Why can't I change the isolation levels of my connection in MySQL?

I am trying to experiment a bit with the isolation level of MySQL in a test environment. I do the following: mysql> set @@session.tx_isolation='READ-UNCOMMITED'; ERROR 1231 (42000): Variable ...
3
votes
1answer
1k views

Complex constraint across all data in a table

We have a table to record processing that occurs on a system and we need to ensure that only a single row has an 'in process' status. I want to ensure that the result of this is always either zero or ...
3
votes
1answer
154 views

Howto find out details and reasons for idle transactions?

I have started to use the "Idera SQL check" tool. It shows floating bubbles for each process active on the Sql Server system: Those bubbles are surrounded by a red border in case the transaction is ...
3
votes
1answer
171 views

Is REPEATABLE READ the right choice for validation before modifying data?

In my database I have a Task table. This table has a number of fields that are foreign keys to other tables. There are also a few link tables, that have Task on one end. When a new task is created ...
3
votes
1answer
195 views

xp_cmdshell in a transaction

I need to run multiple scripts in a certain folder in a transaction and rollback if there are any errors. At this point I'm just trying to get the rollback to work without a try catch. Currently when ...
3
votes
1answer
126 views

Effects of turning the transaction safety OFF

I am considering to turn off the transaction safety to maximize the performance. Before that i am interested in finding the current status of transaction safety. Is there any command that we can get ...
3
votes
1answer
397 views

SQL Server 2008 - Adding a column and a filtered index within a single transaction?

This is more of a general question. Why can you not add a filtered index within the same transaction scope as adding the new column in the first place? The solution is of course simple, just make two ...
3
votes
1answer
2k views

Rotate a table in PostgreSQL

Problem description I need to rotate (with DROP and CREATE) a table which is heavily used by other clients. At present, I have a program which replaces (DROP + CREATE) this table. Sometimes, just ...
3
votes
2answers
401 views

What is the correct term to describe a “persistent transaction”?

I'm kind of a the administrator of a zero-administration OODBMS, but mostly I do programming. I remember reading years ago about some SQL database that had a concept of something like a permanent ...