Questions tagged [transaction]
A mechanism for committing a consistent set of changes into a database atomically.
738
questions
120
votes
3
answers
87k
views
Is it a bad practice to always create a transaction?
Is it a bad practice to always create a transaction?
For example, it is a good practice to create a transaction for nothing but one simple SELECT?
What is the cost of creating a transaction when it ...
- 1,303
80
votes
5
answers
82k
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 ...
- 1,531
46
votes
9
answers
7k
views
Asked to Not Use Transactions and to Use A Workaround to Simulate One
I've been developing T-SQL for several years and am always digging in further, continuing to learn all I can about all aspects of the language. I recently started working at a new company and have ...
- 401
38
votes
2
answers
36k
views
Can I select data inserted in the same uncommited transaction?
Maybe this is a dumb beginner question, but I cannot find an answer anywhere. Everywhere I read about the Transaction Isolation which solves the visibility of data within the concurrent transactions. ...
- 567
31
votes
1
answer
35k
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.
...
- 815
30
votes
2
answers
51k
views
How to rollback when 3 stored procedures are started from one stored procedure
I have a stored procedure that only executes 3 stored procedures inside them. I am only using 1 parameter to store if the master SP is successful.
If the first stored procedure works fine in the ...
- 441
30
votes
4
answers
127k
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 ...
- 402
29
votes
1
answer
34k
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 100k-...
- 413
28
votes
3
answers
16k
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.
...
- 382
27
votes
2
answers
4k
views
Transactional DDL workflow for MySQL
I was a little surprised to discover that DDL statements (alter table, create index etc) implicitly commit the current transaction in MySQL. Coming from MS SQL Server, the ability to do database ...
- 370
25
votes
7
answers
41k
views
Oracle - Any way to view uncommited changes to a particular table?
I'm debugging through a batch process 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 from another session ...
- 411
25
votes
4
answers
33k
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 ...
- 355
24
votes
4
answers
42k
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 ...
- 949
24
votes
3
answers
23k
views
Concurrent transactions result in race condition with unique constraint on insert
I have a web service (http api) which allows a user to restfully create a resource. After authentication and validation I pass off the data to a Postgres function and allow it to check authorisation ...
- 345
24
votes
1
answer
17k
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 ...
- 12.2k
21
votes
5
answers
8k
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?
- 6,702
21
votes
2
answers
15k
views
Deferrable unique index in postgres
Looking into postgres documentation for alter table, it seems regular constrains can be marked as DEFERRABLE (more concretely, INITIALLY DEFERRED, which is what I'm interested in).
Indexes can also ...
- 333
20
votes
5
answers
3k
views
What is the exact relationship between a database transaction and locking?
This is a humble question asked in the spirit of increasing my knowledge; kindly be gentle in your response.
As a long-time application developer, I know at some level what a transaction is (I use ...
- 303
19
votes
2
answers
11k
views
Avoid unique violation in atomic transaction
Is possible to create atomic transaction in PostgreSQL?
Consider I have table category with these rows:
id|name
--|---------
1 |'tablets'
2 |'phones'
And column name has unique constraint.
If I ...
- 333
19
votes
1
answer
2k
views
What exactly is the 'online' in OLAP and OLTP?
I'm a bit confused because I'm questioning the definition of 'online' in OLTP and OLAP. I used to think that 'online' here means that we want our answer in bounded time and based on the data available ...
- 457
19
votes
1
answer
32k
views
MySQL: will a transaction lock the row?
I haven't tried using MySQL transaction before, I just want to clarify something.
If two users execute a query at the very exact time, how MySQL would handle this?
e.g. the users is trying to update ...
- 453
17
votes
1
answer
13k
views
Are we required to handle Transaction in C# Code as well as in stored procedure
Do we really require transaction handling in c# as well database store process both side
C#:
Using(transaction with transaction scope)
{
Execute stored proc;
Transaction. Complete;
}
SQL ...
- 185
16
votes
1
answer
3k
views
In Postgres, how to get a list of current defined savepoint?
I'm using postgres SAVEPOINT which create a new savepoint within the current transaction, and would like to display the list of currently defined savepoint in a connection.
To be more precise: I'd ...
- 263
15
votes
3
answers
39k
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 ...
- 933
15
votes
3
answers
6k
views
What is the point of TRY CATCH block when XACT_ABORT is turned ON?
Code sample with XACT_ABORT_ON:
SET XACT_ABORT_ON;
BEGIN TRY
BEGIN TRANSACTION
//do multiple lines of sql here
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0) ROLLBACK;
//...
- 3,440
14
votes
2
answers
123k
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 ...
- 151
14
votes
1
answer
2k
views
Is this an error in SQL Server's documentation for ROLLBACK?
This is the SQL Server's document talking about the ROLLBACK statement. On that page it states that the syntax of it is as follows:
ROLLBACK { TRAN | TRANSACTION }
[ transaction_name | @...
- 2,602
14
votes
2
answers
21k
views
Why "SET LOCAL statement_timeout" does not work as expected with PostgreSQL functions?
My understanding is that PostgreSQL functions are executed similar to a transaction. However, when I tried to "SET LOCAL statement_timeout" within a function, it did not work. Here's how it works ...
- 243
14
votes
2
answers
13k
views
At what point does a database update its indexes in a transaction?
I'm trying to understand the sequence of events in inserts where both an index and a transaction are involved.
For example, Oracle documentation states:
If you create [or have] one or more ...
- 243
14
votes
1
answer
29k
views
Putting a Select statement in a transaction
What is the difference between these 2 queries:
START TRANSACTION;
SELECT * FROM orders WHERE id=1;
UPDATE orders SET username='John' WHERE id=1;
COMMIT;
And without transaction:
SELECT * FROM ...
- 293
13
votes
2
answers
49k
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 ...
13
votes
2
answers
18k
views
ROLLBACK doesn't work after INSERT INTO newly created destination table
I am working on PHP-script which imports CSV file (customers.csv) into MySQL table (customers).
Before inserting contents of CSV-file into the mysql table I am first backing up the original customers ...
- 235
13
votes
1
answer
11k
views
Are explicit transactions needed in this while loop?
SQL Server 2014:
We have a very large (100million row) table, and we need to update a couple of fields on it.
For log shipping, etc, we also, obviously, want to keep it to bite-size transactions.
...
13
votes
2
answers
17k
views
Current isolation level in oracle
How to get the current (default) transaction isolation level in oracle?
- 1,089
13
votes
1
answer
8k
views
How to compare xmin and txid_current() after transactions ID wraparound?
Besides its regular columns, Postgres tables also have various system columns available. One of them, xmin, stores the transaction ID used to create a row. Its data type is xid, a four byte integer ...
- 927
13
votes
1
answer
15k
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 ...
- 319
13
votes
1
answer
6k
views
MySQL Commited data not seen to select query
Context: The framework used is Spring and all queries are run with JdbcTemplate. Mysql Server version is 5.6.19. The table is an InnoDB table and defaults like auto commit and isolation level ...
- 131
12
votes
2
answers
16k
views
Why am I getting a deadlock for a single UPDATE query?
I have two processes which execute code like this in parallel:
begin;
update foos set unread=false where owner_id=123 and unread=true;
commit;
This results in deadlocks.
My understanding of what ...
- 783
12
votes
3
answers
8k
views
What is the effect of having an opened transaction in MSSQL for too long?
I'm just wondering what happens if you begin a transaction in a DB and forgot to commit or rollback it. Will the server be down? Lets say you left it for 3 days.
There are also users who are using it ...
- 253
12
votes
2
answers
4k
views
Where does InnoDB store transaction data before committing it?
I've done some tests using READ_COMMITTED and READ_UNCOMMITTED at home, using the JDBC technology.
I see that READ_UNCOMMITTED can actually read uncommitted data, e.g. data from some transaction not ...
- 221
12
votes
1
answer
6k
views
In case of multiple updates of the same record in a single transaction, how many versions are being stored?
According to MSDN article by Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation
"... All the earlier versions of a particular record are chained in a linked ...
- 1,382
12
votes
3
answers
3k
views
Why do temporal tables log the begin time of the transaction?
When updating a row in a temporal table the old values for the row are stored in the history table with the transaction begin time as the SysEndTime. The new values in the current table will have the ...
- 5,715
12
votes
1
answer
2k
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 ...
- 607
11
votes
3
answers
9k
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,333
11
votes
2
answers
43k
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 ...
- 213
11
votes
1
answer
8k
views
PostgreSQL: How does COPY FROM behave inside a transaction?
I have an 8-million-line CSV file I need to import into a table. This import will currently take place within a transaction.
Does COPY FROM create a subtransaction? Will a failure roll back the ...
- 213
11
votes
2
answers
23k
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 ...
- 257
11
votes
1
answer
6k
views
Synchronization using triggers
I have a requirement similar to previous discussions at:
Writing a simple bank schema: How should I keep my balances in sync with their transaction history?
Trigger in combination with transaction
I ...
- 227
11
votes
2
answers
3k
views
list of batch aborting errors in SQL server
In SQL Server, if XACT_ABORT is off then some errors will terminate the current statement (for example supplying the incorrect number of parameters to a stored procedure that takes some parameters) ...
- 169
11
votes
1
answer
12k
views
"truncate log on checkpoint" option in SQL Server
Long story but our long term consultants (former employees) wrote a custom script years back (2006 or so) to interface with Tivoli Storage Manager and it appears to be checking for a SQL Server DB ...
- 113