Questions tagged [transaction]
A mechanism for committing a consistent set of changes into a database atomically.
738
questions
3
votes
1
answer
619
views
Commit() takes 50 seconds after inserting about 100000 rows
I have a sqlite database that is, (I think), fairly small.
I has about 1 million rows in a couple of table and the total space on disk is about 150 Mb.
I then open a transaction and insert about ...
CommunityBot
- 1
0
votes
1
answer
308
views
Transaction - Timestamp Ordering. When is aborted transaction restarted
(I don't know if this is the correct place to ask, since this is more like a theoretical question).
I have a question about restarting aborted transactions. I don't know when / at which point should ...
CommunityBot
- 1
1
vote
1
answer
670
views
MySQL Partial Transactions
I am experiencing some strange behavior in MySQL using transactions. We have a PHP site that runs ~100 workstations inserting thousands of items (table 1) daily, with 4 to 7 logged events (table 2) ...
CommunityBot
- 1
1
vote
1
answer
47
views
Why does a SAVE TRAN outside of a CURSOR loop not work, but inside does?
Why, when my SAVE TRAN SavePoint is at location (A) do I get these errors on the third instance of falling into the CATCH and beyond...
"Msg 6401, Level 16, State 1 ... Cannot roll back ...
- 83k
0
votes
1
answer
287
views
Try Confirm Cancel (TCC) Protocol
My Confusion
I have been reading up about the Try Confirm Cancel (TCC) Protocol, and I understand the main idea behind it (for the happy flow). My confusion comes in how do we actually implement or ...
CommunityBot
- 1
0
votes
1
answer
20
views
Is commit of first insert necessary if second insert has constraint depedent on first insert?
Using MariaDB I am running an insert on one table and then trying to run another insert on second table. Due to a constraint the second insert should only succeed if the first one was successful.
If I ...
- 103
0
votes
1
answer
283
views
Can't bring up slave from ec2-consistent-snapshot due to uncommitted prepared transaction
I'm struggling with bringing up a slave instance using a snapshot created by ec2-consistent-snapshot, in my log it's describing the fact that an unprocessed transaction exists, but isn't that what ec2-...
CommunityBot
- 1
0
votes
1
answer
29
views
How would you implement Rollback when you need to make sure that 2 updates and 2 inserts either all happen or are all rolled back?
I have a MySQL stored procedure. Cut down, it looks like this.
START TRANSACTION
SELECT some_columns
SET some_variables
UPDATE row_in_balance_table
UPDATE row_in_entry_table
INSERT ...
- 77k
0
votes
1
answer
59
views
Sql Server Open Transactions
I was notified that the transaction log drive for a sql instance I manage is filling up. I jumped on, found which transaction log was growing, and went to take a look at why the tlog was growing. ...
- 30k
0
votes
1
answer
57
views
Rolling back a transaction if a read would return a different value at commit time
I want a certain behavior for transactions in Postgresql, but I can’t tell if it is possible.
I would describe this behavior as an “Optimistic Read Lock”, but that does not seem to be the right term ...
- 48k
0
votes
1
answer
62
views
Keeping Lock across COMMIT AND CHAIN?
I am writing an application that processes jobs. A job does 2 things that are non-transactional: PartA and PartB. Both are idempotent.
A job has three statuses:
Created
PartA_Done
PartB_Done
The ...
- 495
0
votes
0
answers
84
views
Should/can COMMIT/ROLLBACK emit an error when no transaction is active?
What does ISO/IEC 9075-1:2023 standard say about COMMIT/ROLLBACK queries? Should (or can) they emit an error/exception when executed outside an active transaction?
I am asking because of https://jira....
- 398
0
votes
1
answer
2k
views
Getting ERROR: invalid transaction termination CONTEXT: PL/pgSQL function run(text,text,text,double precision) line 147 at COMMIT
I am trying to execute an INSERT statement and, if there's an error then I rollback and retry the insert; otherwise I commit it to close the transaction. However, I am getting the error: "...
CommunityBot
- 1
3
votes
1
answer
919
views
Help understanding InnoDB Deadlock Detection
I have a deadlock occurring when executing an Update and a Select in seperate threads but I'm not sure what can be done about it. Previously my understanding was that this could happen in parallel but ...
CommunityBot
- 1
1
vote
1
answer
284
views
Serializable isolation fails even for unrelated rows
I have this table
create table "tasks" (id SERIAL PRIMARY KEY, user_id int REFERNCES "user"(id), title TEXT);
I also created index on "tasks"(user_id)
Then I open two ...
CommunityBot
- 1
1
vote
1
answer
129
views
Handle partial failures in a transaction (commit selectively) in MSSQL
I am doing a batch update where I process records sequentially (User 1, User 2 etc). Each record has multiple update queries associated to it. I need to skip if there is a data issue for an individual ...
- 849
0
votes
1
answer
44
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
4
votes
1
answer
535
views
Write-lock a whole table during transaction
I need to perform a delicate operation to my table in which I will solely insert, delete and select upon all of my rows and no God may interfere with the table during this operation: the table will be ...
CommunityBot
- 1
1
vote
1
answer
106
views
Is there a way to have postgresql transactions be commited in the order they were started?
For some business reason I am running the query SELECT * FROM table WHERE updated_at > $1 AND user_id = $2;.
Consider two transactions, A and B, that are updating rows in table: UPDATE table SET ...
- 48k
0
votes
1
answer
337
views
What is the best solution to prevent picking the same value (by multiple requests)?
Situation: there is a table "Tours" which contains a lot of generated records with different properties (Color, Priority, PickedUp). The goal is to allow external application (over REST API) to pick ...
CommunityBot
- 1
0
votes
0
answers
9
views
Understanding SQL Server Extended Events: Clarification on "count" Column in "database_transaction_begin" Event
I'm currently working with SQL Server Extended Events and have come across an area I'm finding a bit unclear. I'm trying to understand the various event properties, particularly focusing on the "...
- 1,159
0
votes
0
answers
25
views
Mismatch Between Transactions and Sessions in DMVs sys.dm_tran_session_transactions and sys.dm_tran_database_transactions
I've been working with several Dynamic Management Views (DMVs) in SQL Server to monitor transactions, specifically sys.dm_tran_session_transactions, sys.dm_tran_database_transactions, and sys....
- 1,159
0
votes
1
answer
259
views
SQL transaction: Delete between two selects possible?
I have two SELECT statements within a transaction (repeatable read)
Select @firstItem =id from myTable Where ....
--Do some more magic, so I can't concat the the two queries!!!!
and
Select * from ...
CommunityBot
- 1
0
votes
1
answer
24
views
MYSQL transactions and guaranteeing inserts/deletes/updates
I have a stored procedure called processWinningBet. It takes a pendingBet and does these things.
update balance table for user
creates a row in historic bets table
deletes the corresponding row in ...
- 77k
3
votes
1
answer
1k
views
MySQL: Skip INSERT / UPDATE statement that triggers error but show them in output
I have a million of rows of customers to be imported via SQL dump. Customer data will be inserted into main table customer and child table customer_address
The dump contains the following SQL ...
CommunityBot
- 1
0
votes
1
answer
61
views
PostgreSQL driven by SQLAlchemy - Release Savepoint Idle in Transaction
While running PostgreSQL 13.12 (occurs in several versions of PG11/PG13) using SQLAlchemy 1.3, we are occasionally hitting issues where increased concurrency leaves certain transactions (and their ...
CommunityBot
- 1
2
votes
2
answers
5k
views
Can we set LOCK_TIMEOUT instance- or database-wide?
It is possible to change the SQL Server's default (which is -1 or indefinite period of time) LOCK_TIMEOUT value session-wide.
Is it possible to set LOCK_TIMEOUT default value server-, instance- and/...
5
votes
3
answers
980
views
advantages of single-leader (transactions) over multi-leader replication
I am reading the excellent book "Designing Data-Intensive Applications" which I wholeheartedly recommend, but I'm confused by a section comparing multi-leader (i.e. multi-writer) replication to single-...
CommunityBot
- 1
1
vote
1
answer
358
views
Reusing the same connection to read uncommited transaction
My question is similar to: how to spy on a transaction that is not yet committed in postgres for debugging
What I tried:
I start a pgbouncer instance, and set the max-db-connection to 1
From my node ...
CommunityBot
- 1
0
votes
1
answer
483
views
Consistency of data between two databases on different server
I have to guarantee consistency of data stored in two databases installed on different servers.
The first one is a MySQL database and the second is a SQL Server database.
Data properties are not the ...
CommunityBot
- 1
0
votes
0
answers
162
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:
...
- 113
0
votes
1
answer
2k
views
MYSQL: InnoDB inserts wont rollback transaction inside a stored procedure
I have this stored procedure:
START TRANSACTION;
code_gen_loop: WHILE @Codes > 0 DO
IF MOD( @Codes, 10000 ) = 0 THEN
EXECUTE qry_status_stmt2;
EXECUTE qry_status_stmt3;
...
CommunityBot
- 1
3
votes
1
answer
6k
views
Many transactions are getting stuck in the "cleaning up" state - what could cause that?
I'm trying to track down a locking issue, and found via SHOW PROCESSLIST that when the queries in question are waiting for locks and timing out, there are no other queries active in the process list. ...
CommunityBot
- 1
1
vote
3
answers
12k
views
How to handle ~1k inserts per second
Assuming one has about 1k requests per second that require an insert.
Now, there are a lot of answers to this on the internet... but they're technically wrong in this specific context. Yes, pretty ...
- 69.7k
0
votes
1
answer
29
views
Create named PK when create table
I'm creating a table in Postgresql-11, and I need create a primary key for it at same time, to prevent from duplicated data being inserted before the PK created, so I want to these two operations be ...
- 48k
1
vote
1
answer
147
views
How to handle deadlocks while multiple batch inserts?
I need suggestions/ideas of handling a issue with deadlocks.
I'm having multiple batch inserts commands to same table:
insert into ip (id, p_id, m_type, m_id, sha256, path) VALUES ($1, $2, $3, $4, $5, ...
- 48k
0
votes
1
answer
41
views
Can I do transaction for dynamic sql which contains DISABLE change tracking / TRUNCATE / PARTITION SWITCH
I would like to create transaction which will :
DISABLE change tracking IF exists.
TRUNCATE partition for table.
SWITCH PARTITION from different table to main table.
ENABLE change tracking IF was ...
- 11.8k
0
votes
0
answers
69
views
UPDATE AND SELECT INSIDE THE SAME TRANSACTION
I have a transaction that updates a table and then does a SELECT check on that table that was changed, but the data does not follow the update made by the previous UPDATE, there is something I should ...
0
votes
1
answer
1k
views
PostgreSQL Foreign Data Wrappers - Simultaneous queries won't finish
We're using foreign data wrappers in a database which points to another server (which is a read-only replica). We run scheduled jobs using python ( more on this here: https://github.com/sqlalchemy/...
CommunityBot
- 1
0
votes
0
answers
71
views
How are blind writes recoverable in a transaction schedule?
Consider the following schedule -
T1 T2
R(A)
W(A)
R(A)
W(A)
Commit
Commit
I understand that this schedule is non-recoverable, because if a failure occurs between the two ...
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?
- 83k
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 ...
- 172k
0
votes
1
answer
32
views
Locking order in a nested query
I'm trying to prevent deadlocks in my code because i may use both in different parts of my project.
Do they lock rows in the same order?
Nested
select * from `carts`
where `carts`.`user_id` = ? and ...
- 57
0
votes
1
answer
102
views
How do two transactions with different levels of isolation interact with each other?
this is specifically for SQLserver:
I want to know if I have a transaction with isolation level SERIALIZABLE and another one with READ UNCOMMITTED how will they interact concurrently?
will the second ...
- 45.9k
0
votes
0
answers
59
views
Is it possible deleted partially?
I executed delete query
delete from [table_name]
After execution, I found that 'where clause' is wrong so I cancelled the query using cancel buttion in SSMS.
and then I counted the table record.
I ...
- 1
0
votes
0
answers
43
views
Trigger with EXEC and Only Debug Statements After First Exception Saved [duplicate]
I apologize for the verbosity of this post.
To catch DDL changes after a snapshot or full load (using aws/dms here) has been performed, there is sourceDB.tblAWSObjects which replicates to targetDB....
- 505
0
votes
2
answers
79
views
Variable Value Set in TRY CATCH is Discarded When Trigger is Coded a Certain Way
I know this goes against all things logical, however, I am attempting to push this through anyway :(
There is a scenario in which records are written to a table with the trigger below attached. ...
- 505
7
votes
1
answer
4k
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.
...
- 131
0
votes
1
answer
128
views
Transaction/For update lock for an eCommerce project
When users want to purchase their orders, The quantity of the products should be decreased. Now I'm planning to use a Transaction and a FOR UPDATE lock on the products and decrease the quantity of ...
- 7,649
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 ...
- 172k