A mechanism for committing a consistent set of changes into a database atomically.
14
votes
7answers
4k views
Oracle - Any way to view uncommited changes to a particular table?
I'm debugging through a batch proess currently that does a lot of DML statements, but doesn't do a commit right away. It would be nice to be able to view the "pending" changes while the transaction ...
11
votes
4answers
2k views
Is ROLLBACK a fast operation?
Is it true that RDBMS systems are optimized for COMMIT operations? How much slower/faster are ROLLBACK operations and why?
11
votes
3answers
7k views
What risks are there if we enable read committed snapshot in sql-server?
I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there?
eg.
Will this affect recovery of the database?
Is there anything ...
9
votes
1answer
599 views
SQL query for combinations without repetition
I need a query which can be used in (or as) a function and retrieves all combinations of n values. And I need all combinations of length k where k = 1..n.
Extended sample input and result so input ...
9
votes
1answer
9k views
How to find out who deleted some data SQL Server
My boss had a query from a customer yesterday asking how they could find out who deleted some data in their SQL Server database (it is the express edition if that matters).
I thought this could be ...
9
votes
1answer
264 views
Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state
Background: I'm working on http://sqlfiddle.com (my site), and am trying to prevent one avenue of abuse possible there. I'm hoping that by asking about a problem I'm currently addressing, I don't ...
7
votes
1answer
739 views
Generating Invoices and Tracking
Every 2 weeks, the system will generate the invoices for the companies.
Company will receive an invoice on the 1st and 16th every month. (It will run via Cron Job every 2 week. It scan through the ...
7
votes
1answer
1k views
What is the “Chaos” Isolation level and when should it be used?
ADO.NET documentation shows the possibility of setting the transaction level for a SQL transaction to Chaos. It sounds unpleasant, but if the feature is there, presumably it has some legitimate use.
...
6
votes
6answers
2k views
On DB2, who updated a record and what did the record look like before the update?
(SQL Server 2008 R2 Standard, database under full recovery)
I have a table with fields id, firstname, lastname. A statement is executed:
insert into dbo.sometable (id, firstname, lastname) values ...
6
votes
2answers
183 views
Can I use transaction logs to rebuild a database?
We've had some data loss and don't have a reliable backup it seems. We do have HUGE transaction logs and there was some thought that we might be able to use these to get back to a certain point.
Is ...
5
votes
3answers
2k views
Will the transaction log shrink automagically in SQL Server?
When SQL Server database in a SIMPLE mode, you don't have to care about the transaction log bakcups. But in a SIMPLE mode, the transaction log seems to grow as it does in FULL mode. Does is truncate ...
5
votes
3answers
1k views
SQL Server - what isolation level for non-blocking select statements?
I have a long running transaction (called, say, T1) that performs some deletes, updates and inserts on a table in SQL Server 2008 R2. At the same time, another process periodically runs select ...
5
votes
1answer
146 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
3answers
561 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 ID ...
5
votes
1answer
159 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
214 views
How do I query only for committed data while avoid a deadlock
I have a system writing batches of queries as transactions (to maintain coherency), these can be sets of 30-100 queries per transaction. These are done on multiple tables
At the same time there are ...
4
votes
3answers
227 views
Can I change table structure in a transaction and then roll it back if there is an error?
I have some ALTER TABLE statements that I am running. Not all of them work (they are the result of running SQL Data Compare) and I want to group them in some transactions and roll back the statements ...
4
votes
1answer
87 views
Transactions, references and how to enforce double entry bookkeeping? (PG)
Double entry bookkeeping is
a set of rules for recording financial information in a financial
accounting system in which every transaction or event changes at least
two different nominal ...
4
votes
2answers
944 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
713 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
2k views
Current isolation level in oracle
How to get the current (default) transaction isolation level in oracle?
4
votes
2answers
165 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 ...
3
votes
2answers
1k 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
1answer
179 views
Trigger in combination with transaction
Suppose we have the following situation:
We have a table (let's say Table_A), wich has a trigger on INSERT. The trigger job is to update some rows in table_B based on the inserted values in table_A.
...
3
votes
3answers
306 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
3answers
94 views
sleeping SPID blocking other transactions
I'm really having trouble tracking down some blocking we are experiencing.
The root blocking SPID's status is 'sleeping', the cmd is 'AWAITING COMMAND', and the sqltext is 'SET TRANSACTION ISOLATION ...
3
votes
1answer
187 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
1k views
How to use transactions with SQL Server DDL?
I have a login table into which all inserts are done by a single stored procedure.
CREATE TABLE dbo.LogTable(
LogRefnr int IDENTITY(1, 1) NOT NULL,
LogQuery varchar(255) NOT NULL,
LogTime ...
3
votes
1answer
259 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 ...
3
votes
1answer
1k views
MySQL transaction size - how big is too big?
I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction.
There are many aspects, and the imports may yield anywhere between ...
3
votes
2answers
488 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 ...
3
votes
1answer
176 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
106 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
369 views
Deleting MySQL table with pending transactions
Is there a way to delete an InnoDB table or database with pending transactions in MySQL (preferably on file system level)?
What happened:
I use MySQL 5.5.28 and ran LOAD DATA INFILE… to import a ...
3
votes
2answers
367 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
135 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
811 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 ...
3
votes
2answers
308 views
Transaction and Try-catch in SQL Server Job
We have DML operations in each step of a SQL Server job. To ensure the update/insert will be rolled back in case something goes wrong, I have wrapped the data modifications of each step in TRY CATCH ...
3
votes
1answer
580 views
Duplicate UNIQUE KEY error after ROLLBACK in MySQL
I have a table like this
mysql> describe seudonimos;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra ...
2
votes
2answers
4k views
SQL Server Transaction Timeout
Is there a way in SQL Server 2008 R2 to cause a timeout for a database modification involving a transaction? We have a scenario where our application code hangs or throws an exception and fails to ...
2
votes
2answers
2k views
Using MySQL triggers or transactions?
I want to ask your opinion in using MySQL triggers or transactions in a website.
Actually I have a history payment table with - UserId | OperationId | Comment | Credits | Sign (debit or credit). So ...
2
votes
1answer
279 views
Rollback generate scripts
During the development of applications users often want to add new modules, business rules etc. So I've often used SQL Server 2008 option to create scripts from the development DB to generate new ...
2
votes
1answer
303 views
Transaction and data consistency during a failure
When a database begins a transaction, all statements executed in that transaction are isolated and atomic (and consistent and durable). These are pretty much the definition of a transaction.
...
2
votes
1answer
93 views
Transaction level difference between using a large IN filter VS. “BEGIN TRAN/COMMIT”
I would like to delete about 100,000 records with minimal server overhead. I've had a few nagging questions I haven't been able to test properly so I figured I'd ask some experts here. Which would ...
2
votes
2answers
1k 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 ...
2
votes
2answers
64 views
How to rollback the identity seed after deadlock
Now that's an approximate sequence of operations Im performing:
SET IDENTITY_INSERT <table-name> ON;
INSERT SOMETHING to <table-name> with explicitly specifyed id
DECLARE @oldID bigint
...
2
votes
1answer
275 views
In InnoDB, does a Transaction imply any implicit locking of a table?
The Isolation level is REPEATABLE_READ.
The logic is as under:
Transaction begins
Read data from Table A
If (Table A has Any Data) End Transaction and exit
If Table A has No Data, Proceed further
...
2
votes
1answer
394 views
Save Transaction: Is the name local to the stored procedure or not?
Some foot shooting just occured on a production server. I fixed the problem, but I'm now rather confused.
There is a subset of stored procedures that preserve state on failure. That is, if the ...
2
votes
2answers
277 views
read before write transaction
Talking about transactions (db transactions), does a read operation have always precede a write operation?
That's obvious, isn't it?
Thanks in advance
*Of course my question doesn't take into ...
2
votes
1answer
127 views
How to scale MySQL transaction wrapped queries?
So I'm working on an app that wraps many of it's SQL queries in a transaction for it's SQL usage and I'm trying to think of some ways to scale the DB layer up....
Up until now we've been throwing ...
