Questions tagged [transaction]
A mechanism for committing a consistent set of changes into a database atomically.
542
questions
39
votes
8answers
4k 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 ...
0
votes
2answers
24 views
Pending changes from a finished job
I'm getting the following error:
Uncommitted changes pending for file MYTABLE in library MYLIB.
CPF325E
Cause . . . . . : Uncommitted changes are pending for file MYTABLE in library ...
0
votes
1answer
15 views
Debugging a hanging session/lock
We are having a recurring situation where we have 2 one query hanging, and other queries waiting for that one, and our DB grinds to a standstill. I am trying to debug this and am seeking help. The DB ...
0
votes
0answers
19 views
Is it possible to create an SQL transaction that rollbacks on syntax errors?
In may day-to-day job, I often have to create SQL files containing minor changes (no DDL operation usually) in order to migrate those to production databases easily.
The problem is, even with syntax ...
0
votes
1answer
22 views
How postgres deals with XIDs and wraparounds?
I was reading the docs about the xid and it's wraparounds. But can't understand how it all works.
Normal XIDs are compared using modulo-2^32 arithmetic. This means that for every normal XID, there ...
0
votes
1answer
43 views
SQL server - data successfully inserted in a table but not returned in Select Statement
SQL select query not returning records, even though the data insert operation is successful.
We have a table [dbo].[Login] in our database where data is inserted using the below stored procedure. The ...
0
votes
0answers
28 views
In Oracle, how can I tell if I'm currently in a read only transaction?
How can I tell if I'm currently in a read-only transaction that was started by set transaction read only name 'tran1'?
4
votes
1answer
115 views
Inner procedure is rolled back but outer procedure is still committed
I have an outer procedure which calls 5 different inner procedures something like
Parent procedure(Tran/try/catch)
Inner sp1 (Tran/try/catch)
'
'
Inner sp5 (Tran/try/catch)
But in this case, if one ...
0
votes
1answer
35 views
Understanding of Oracle Statement-Level Read Consistency between Oracle 10g and 10g+
I'm new to Oracle and I just want to confirm my understanding of Statement-Level Read Consistency for Oracle 10g and 10g+.
The Oracle 10g document states:
Oracle always enforces statement-level ...
1
vote
2answers
50 views
How to handle concurrency during an INSERT-SELECT cycle?
The application should limit each user's access to a certain resource to a maximum number of accesses every 24 hours. To keep track of those, we have the following table:
CREATE TABLE accesses (
...
0
votes
2answers
93 views
SQL SERVER: How to get the stored procedure text EXCLUDING comments?
I need to find all the stored procs that use Transactions, as I want to enable transaction abort to those procedures. However, --I didn't do this; it's inherited-- many of the stored procedures ...
0
votes
0answers
34 views
How to avoid MYSQL procedure “transaction characteristics can't be changed while a transaction is in progress” error?
I am trying to call a MySQL procedure which is throwing the error
"transaction characteristics can't be changed while a transaction is
in progress"
I am making the call to this procedure from a ...
-2
votes
2answers
53 views
How do writers not block reads in PostgreSql
Since readers do not block writers and writers do not block readers is what MVCC is all about, how does postgresql prevent writers who are in the process of committing their changes to blocks/pages ...
1
vote
1answer
57 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 ...
1
vote
1answer
31 views
Difference in location of transaction isolation level declaration - inside/ouside a transaction?
I have been looking though some stored procedured on our system today when I saw the following structure:
(pseudocode indicative of actual SP, server is using the default isolation level):
CREATE ...
1
vote
1answer
25 views
What are the performance differences of inserting a bunch of data with one INSERT versus a bunch of INSERTs in a transaction?
I'm trying to understand the performance differences between something like
INSERT INTO Person (id, name) VALUES
(1, "Kevin"),
(2, "John"),
(3, "Jane"),
...
and
BEGIN TRANSACTION;
INSERT INTO ...
2
votes
2answers
47 views
Transactions in SQL Server Emergency Mode
I know that while the database is marked as suspected users cannot start new transactions, and the only access is via emergency mode which gives only read only access. I want to know how are ...
0
votes
0answers
43 views
PostgreSQL Race condition and Serializable
Hello I try to debug a tricky race condition in my app (golang, pq, sqlx, postgresql Debian 10.6-1.pgdg90+1).
I have a table
CREATE TABLE j (
id BIGSERIAL PRIMARY KEY NOT NULL,
...
2
votes
3answers
90 views
when do lock actually happen in a transaction?
Let's say we have a transaction A:
BEGIN TRAN A;
//--------------------< time a
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
//--------------------< time b
...
1
vote
1answer
24 views
Isolation level serializable not working as expected?
I have this table
create table testing(
c1 text,
c2 text);
I open two transactions simultaneously. t0 t1.. denote series of time snapshots in increasing order
Transaction 1
BEGIN; -- t0
SET ...
-3
votes
1answer
40 views
System failure causes inconsistency between two transactions
Let's say the picture above the transaction 4 for user A.
A updated a record from Account table, change the balance from 100 to 0, then a checkpoint occurred, so all dirty pages got written to disk.
...
0
votes
0answers
26 views
MySQL - Investigate deadlock on atomic update
On a high traffic queue managed by Redis there is the following code:
update `ads_publishers` set `impressions` = `impressions` + 1 where `ad_id` = ? and `pub_id` = ?
Here's is the table schema:
...
0
votes
1answer
63 views
How SQL Server transaction works with log records
My textbook says:
Starting when you begin a transaction, your changes are isolated from other users. What you’re doing is visible to you only,and isn’t really done until you COMMIT — although it ...
2
votes
0answers
58 views
When updated records get written to the real physical pages in mdf file?
I'm a beginner in SQL Server, just some questions on transaction. My textbook says:
A checkpoint is a periodic operation
that forces all dirty pages for the database currently in use to be written to ...
1
vote
1answer
24 views
How to update two records with two different values with two 'where' conditions at once?
In T-SQL, how to have this:
DECLARE @amount decimal(18,2) = 1000
UPDATE account
SET balance = balance + @amount WHERE accountID= 1;
UPDATE account
SET balance = balance - @amount WHERE ...
15
votes
1answer
1k 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 ...
1
vote
1answer
18 views
Do the action blocks of an ALTER TABLE statement run atomically?
In Postgres, ALTER TABLE can accept multiple action forms, separated by a comma.
For example:
ALTER TABLE mytable
DROP CONSTRAINT pkey,
ADD CONSTRAINT pkey PRIMARY KEY USING INDEX myindex;
...
1
vote
1answer
51 views
Deadlock on the same resource calling the same procedure
I would like to kindly ask community for help with this deadlock scenario:
<deadlock-list>
<deadlock victim="process17392264e8">
<process-list>
<process id="...
0
votes
0answers
28 views
Memory usage of large transactions
I have an application which persists itself to a PostgreSQL 11 server. It does this by truncating the tables (removing the old state) and then uploading its current state (many statements) as part of ...
-1
votes
2answers
61 views
When is an Insert committed?
If I highlight/select both the statements below, without a GO stmt between them, will the Insert be committed once it shows 'Rows Affected 1234' in results pane?
If so, can I then cancel the Rebuild ...
2
votes
2answers
47 views
SQL Transaction - use values before commit?
So I am creating a stored procedure in SQL2014. Towards the start it has:
Begin Transaction
Then it does an insert into a table, which has an auto-incrementing primary key:
Insert into tNotice.
...
1
vote
1answer
20 views
Which part of a DB bulk insert takes the most time?
One of the most common tip with regards to DB writing is to group your inserts and execute them as a single transaction. My question is, which part of the [begin transaction] - insert - [commit ...
3
votes
1answer
72 views
An uncommitted transaction updates a column; how can a different transaction see the old column value?
Let's assume the following case.
There is a students table:
id | name
-----------------
1 | Mateus
A "User A" executes the following statement without committing the transaction:
update students ...
8
votes
3answers
898 views
transaction log in RAM or physical file?
I'm a beginner in transaction, just a question on transaction log.
We know that when we commit a transaction, the changes are written to the transaction log, but is transaction log in RAM or physical ...
9
votes
3answers
528 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 );...
4
votes
2answers
232 views
Gap Locking in Read Committed isolation level in Mysql
MYSQL VERSION : 5.7.X
STORAGE ENGINE : Innodb
I have a general idea that Read Committed Isolation will mostly use Shared and Exclusive Record Locks. But, as per mysql docs, there are some cases where ...
1
vote
0answers
34 views
Preserving some data on rollback in InnoDB?
I want to keep log of attempted, sucessful, and failed operations (both for troubleshooting, and also for detecting malicious users trying things). I want to keep that log in InnoDB MySQL table.
Now, ...
4
votes
1answer
121 views
postgres notifications from inside stored procedures/triggers not immediately received by node.js listener client
We are using postgres 11's stored procedures to do looped update of rows in batches. After a batch is updated, a 'progress' table is updated with progress percent and status, THEN a notification (...
4
votes
1answer
70 views
How to avoid deadlock for this query?
I am running a MySql 5.7 database and I have a query which results in a deadlock as it is running in multiple threads.
I am trying to figure out how to avoid this deadlock, but so far I haven't been ...
1
vote
2answers
210 views
Very slow simple INSERT in transaction
I have I problematic query on my SQL Server where I could not find the problem. I have two simple tables, one for the "Headers" and one for the "Items" (each with an PK column, total of five fields ...
3
votes
2answers
468 views
Best practices for committing a transaction in SQL Server where TRY CATCH is used
In a SQL Server code block, what is the best place to place the commit transaction? Inside the try catch block or outside it?.
For example, is option A or option B the correct approach or are they ...
1
vote
1answer
53 views
PostgreSQL ensure no concurrent reads
I'm sorry if it's a very basic question, but would really appreciate educated advice on this:
Say I have an operation, which first has to read from a table and then insert a row if the read value ...
0
votes
1answer
117 views
Safe to use the same temporary table name in parallel transactons?
Using postgreSQL 10 or 11, would it be safe creating temporary tables with the same name, each carrying different data? I do not intend to share the data between any sessions/connection/transactions. ...
0
votes
0answers
41 views
Transaction on defriend and multi databases
I have a big issue when i'm working with multi database and diffrent databases as same time.
The problem is that I'm working with mssql, mysql and mongodb at sametime, and what I need is a way to be ...
1
vote
1answer
15 views
Wraparound Transaction ID
Once the txid has passed the the xmin/xmax 32 bit limit, and assuming you've not suffered any issues with wraparound because you've been correctly maintaining your tables (plenty of vacuums, vacuum ...
2
votes
1answer
94 views
Is it possible to step through TSQL that uses variables, in SSMS?
If I enter TSQL into SSMS Query editor, I know I can "step through" the statements (ie. execute them one at a time) by highlighting a statement and hitting F5 on the keyboard.
It seems by default ...
-1
votes
2answers
49 views
Is there any possible solution to insert correct records and prevent incorrect records from being inserted by insert statement in SQL server? [closed]
I know that each statement (i.e: Insert , Update , Delete ) in SQL-Server is treated as one single transaction and I know that one of the main characteristics of a transaction is it's 'Atomicity' so ...
5
votes
3answers
685 views
How to inspect global temp table from outside the transaction it is created in
To help debugging a batch of SQL (that is run inside a transaction), inside the transaction I dump some data into a global temporary table. The global temporary table is created inside the transaction ...
0
votes
0answers
15 views
InnoDB: does `redo log` contain initial value and final value? or just final value?
start transaction;
update users set age = 34 where user_id = 3 // update age from 30 to 34 years old.
....
....
when I am updating the age of this user, InnDB will write redo log.
What's the ...
0
votes
0answers
66 views
MySQL: why undo log is required in the processes of database recovery?
Let's imagine that an incident happens when you updates.
start transaction;
update users set gender = 1 where id = 3;
Undo log space is allocated.
Previous values are copied to undo log
Record of ...
