Questions tagged [transaction]
A mechanism for committing a consistent set of changes into a database atomically.
86
questions
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
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
7
votes
1
answer
6k
views
How to view tuples changed in a PostgreSQL transaction?
BEGIN;
INSERT INTO foo_table(foo_column) VALUES('a'),('b'),('c');
-- SELECT * FROM pg_stat_activity WHERE ...
-- shows records with a flag if they have been added/deleted/updated
END;
Is ...
- 407
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
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
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
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
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 ...
3
votes
1
answer
810
views
select for update gives error on indexed column
here is my code:
mysql_query("start transaction");
$q="select max(id) from test for update";
$r=mysql_query($q);
$row=mysql_fetch_array($r);
for ($k=0;$k<100000;$k++)
{
$q="insert into test values ...
- 205
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
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
4
votes
1
answer
1k
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 ...
- 868
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
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
9
votes
3
answers
7k
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 ...
- 375
7
votes
2
answers
32k
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, ...
- 761
3
votes
1
answer
4k
views
SQL Server sys.dm_tran_active_transactions long running transaction worktable
today I used the system object sys.dm_tran_active_transactions (to be honest for the first time). I had an issue with distributed transactions (Microsoft DTC) and went digging there after I had no ...
- 675
3
votes
1
answer
4k
views
Transaction Lock Timeouts When Updating a Row
I'm trying to determine the cause of an InnoDB transaction lock timeout. This one is very strange in that it appears to be a deadlock error instead - nonetheless, here's the output of my slow log ...
- 131
3
votes
2
answers
5k
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 ...
- 181
1
vote
1
answer
3k
views
COMMIT works in one plpgsql code block, but not in another?
Every once in a while we need to backfill a bunch of data based on some complex calculations. Because we have a lot of data, we need to make sure that we are not locking a table for a long time and do ...
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
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
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
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
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.
...
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
10
votes
3
answers
15k
views
Get transactions that failed or were never committed
Is there anywhere I can look to find failed transactions in SQL server 2012?
- 121
9
votes
3
answers
8k
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 ...
- 295
7
votes
1
answer
425
views
Does RCSI use less or same space than SNAPSHOT? Why?
Trying to better understand row-versioning based transaction isolations - RCSI (Read Committed Snapshot Isolation and SNAPSHOT) - in SQL Server...
MSDN online article "Choosing Row Versioning-based ...
- 1,382
6
votes
2
answers
9k
views
Transaction basics. What is the result of 2 transactions running concurrently?
I have a rather basic question on transactions.
Assume a table Account with a column amount. Now we have the following code:
BEGIN
$amount = SELECT amount FROM ACCOUNT WHERE id = 123;
$amount = ...
- 761
6
votes
2
answers
23k
views
Rollback Committed transaction in SQL Server
Is it possible to rollback a committed transaction?
I have deleted all the data in a table. The delete statement is inside Begin Transaction and I have committed the transaction with a commit ...
- 61
5
votes
2
answers
3k
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 ...
- 1,973
5
votes
3
answers
4k
views
Where can I get SQL Server Transaction Begin Time from?
I am working with temporal tables in SQL Server and the generated time is the transaction begin time in UTC datetime2.
Is there a function or table in SQL Server to get that same transaction begin ...
- 151
5
votes
2
answers
374
views
Transaction with lock on a specific table
NOTE : If there is similar question, please refer me to it.
I have two tables Request & Receipt.
I want table Receipt remain locked for CRUD operations from anywhere until transaction finished. ...
- 157
2
votes
2
answers
2k
views
What is the correct implementation of handling concurrent SQL inserts while ensuring a non-primary key column (i.e. wid) unique and auto-incremental?
What is the correct implementation of handling concurrent SQL inserts while ensuring a non-primary key column (i.e. wid) unique and auto-incremental?
Note: There was a design constraint that led this ...
- 229
2
votes
4
answers
585
views
Why can't a committed transaction be undone
Once a transaction has been committed, we cannot undo its effect by aborting it.
We have the log file that has all information to undo a committed transaction, so why is this not possible? We should ...
- 101
1
vote
1
answer
871
views
How to have uniqueness constraints for structures across multiple tables?
Say I have a schema system something like this:
create table objects {
uuid id;
string type;
}
create table object_properties {
uuid id;
uuid source_id; // the object which has this property
...
- 189
0
votes
1
answer
45
views
Do PostgreSQL triggers execute in the same transaction as the original statement?
If Table A has a BEFORE INSERT trigger to UPDATE Table B, do those two operations run in the same transaction? Specifically, if the UPDATE on Table B fails, does that mean the INSERT on Table A is ...
- 885
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
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
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
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
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
10
votes
2
answers
10k
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 ...
- 223
10
votes
3
answers
18k
views
Why does this query result in deadlock?
I provide below the raw MySQL query and also the code in which I do that programatically. If two requests are being done at the same time results in the following error pattern:
SQLSTATE[40001]: ...
- 293
9
votes
3
answers
1k
views
Does SQL Server allow (make visible) DDL inside a transaction to the transaction prior to commit?
In PostgreSQL I can create a table with some test data, and then in a transaction migrate it to a new column of a different type resulting in one table-rewrite upon COMMIT,
CREATE TABLE foo ( a int );...
- 62.2k
9
votes
2
answers
14k
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,674
8
votes
1
answer
2k
views
Is there a performance difference in committing and rolling back a read-only transaction?
I open a (repeatable read) transaction (BEGIN TRAN) to do some work on certain records. First thing I do is checking if the data I need to change is in the database. In some cases there will be and ...
- 1,305
7
votes
3
answers
29k
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 ...
- 9,637
6
votes
2
answers
10k
views
Basic transactional DDL script in PostgreSQL
PostgreSQL newbie here trying to put together a transactional DDL script to atomically create a database and its schema in Postgres 9.3: in a transaction, create a few tables; if any errors, roll the ...
