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

learn more… | top users | synonyms

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
274 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
1k 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
2k views

Transactions within a Transaction

What behaviour would PostgreSQL display if for example the script below were called BEGIN; SELECT * FROM foo; INSERT INTO foo(name) VALUES ('bar'); BEGIN; <- The point of interest END; Would ...
4
votes
2answers
97 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
701 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
716 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
788 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
700 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
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
49 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
91 views

transaction across multiple databases - what is the overhead?

when I run the following update, one single transaction across 2 databases: please note I have not committed the transaction. --================================================================ --RUN ...
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
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
48 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
147 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
372 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
57 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
217 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
4k 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
1answer
898 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
1answer
272 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
85 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
2answers
1k 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
920 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
367 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
266 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 ...
3
votes
1answer
167 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
146 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
125 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
378 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
373 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 ...
3
votes
2answers
242 views

Is there a way to “SELECT and TRUNCATE” without losing new data?

I have an application that writes raw log files to a table called new in a PostgreSQL database -- lots of rows and lots of duplicate data. Via a cron job running every minute, I normalize the raw data ...
3
votes
2answers
2k views

How do I implement insert-if-not-found for transactions at serializable isolation level?

I'm having a hard time figuring out how to exactly implement a 'insert if not found' function. Consider the following. We have a table called artist with 2 columns, (name, id) where name is the ...
3
votes
1answer
3k views

MySQL Insert into two tables using new IDs

I'm trying to select data already in the database and insert it into two tables using the generated ID from table one. table1 contains configuration options, table2 contains pricing data. Table2 uses ...
3
votes
1answer
594 views

SQL Server : KEY lock prevents FK on nearby row, but not SELECT [closed]

I have the following table structure (simplified, pseudocode): TABLE dbo.Users (Id int NOT NULL IDENTITY PRIMARY KEY) TABLE dbo.Transfers ( Id int NOT NULL IDENTITY PRIMARY KEY, ...
3
votes
1answer
35 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 ...
3
votes
3answers
143 views

Difference between commit and checkpoint

I was confused about the difference between a commit operation and a checkpoint. I referred to some of the articles like This Article But they are advanced for my understanding. I mean both appear to ...
3
votes
2answers
42 views

innodb_flush_log_at_trx_commit=1 and flush-to-disk reality

I use the default settings of MySQL 5.5 on various servers and I have the following table: create table test._perf ( name varchar(255) not null ) engine=InnoDB; I have an .sql script which ...
3
votes
1answer
322 views

How to perform server-side transaction timeout if COMMIT TRANSACTION will never arrive?

How can SQL Server terminate one of its transactions if it exceeds preset timeout? Imagine the following transaction performed by traveling salesman's SQL client application, which is submitted ...
3
votes
1answer
6k views

statistics State in MySQL Processlist

I have a busy MySQL Server having query SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION ...
3
votes
1answer
716 views

Using the auto increment id from one query as the fk of the next insert

I'm not sure if I'm overcomplicating this or whether I can't see the wood for the trees. I have two tables, both have auto incrementing ID's. Table 1: ID - auto field - int other field - varchar ...