Questions tagged [transaction]

A mechanism for committing a consistent set of changes into a database atomically.

Filter by
Sorted by
Tagged with
1 vote
1 answer
33 views

Limiting the number of rows that can be inserted for a given WHERE clause

In Postgres, I want to be able to confirm users' RSVPs for an event that has limited capacity. How do I do this in a way that protects against race conditions? I have a table event_attendance with ...
Luke Hutchison's user avatar
0 votes
0 answers
14 views

Handling deadlocks in Laravel during concurrent DELETE and INSERT , ON DUPLICATE KEY UPDATE operations

in Laravel I have a service which is not wrapped in a transaction and involves both INSERT ... ON DUPLICATE KEY UPDATE and DELETE statements targeting the same table, customers. INSERT INTO customers (...
u LoV's user avatar
  • 1
2 votes
0 answers
34 views

'Deadlock found when trying to get lock; try restarting transaction' in AWS RDS MySQL 8.0.36 after upgrading from MySQL 5.7.42. Django v4.1.6

Following an upgrade of our AWS RDS instance from MySQL version 5.7.42 to version 8.0.36, we are encountering persistent deadlock issues across concurrent transactions, affecting even basic read ...
Karan Raj's user avatar
0 votes
0 answers
19 views

I need help pulling info from a .txt file into a table in either access or excel

So.. I've tried both Excel and Access to do what I need but I don't know the programs well enough to get them to do what I want, if it's even possible. I have a hourly updating .txt file with ...
UmßraDivisio's user avatar
1 vote
1 answer
85 views

What happens when RCSI is enabled in one database but not another, and you query both?

Suppose that I have two databases on the same server, RCSI_Yes and RSCI_No. In the first, RCSI is enabled. In the second, it is not. How does SQL Server decide what transaction isolation level should ...
J. Mini's user avatar
  • 139
1 vote
2 answers
35 views

MySQL exclusive lock not appearing to work for new child rows

I have some logic in my application which I think results in the following MySQL calls, however when I get two of these run within a couple of milliseconds, I get two incompatible child rows. Start ...
mjaggard's user avatar
  • 125
-4 votes
2 answers
72 views

What do developers need to know about Read Committed Snapshot Isolation? [closed]

My developers have only ever worked with Read Committed, but my new server has Read Committed Snapshot Isolation (RCSI) enabled. What do they need to do differently when writing application code?
J. Mini's user avatar
  • 139
2 votes
1 answer
169 views

Catching exceptions and rolling back transactions

I am just trying to see if I understand this correctly: By default, any error occurring in a PL/pgSQL function aborts execution of the function and the surrounding transaction I have a procedure ...
Lugoom485's user avatar
1 vote
1 answer
60 views

Is MariaDB Acquiring Exclusive Lock during Delete?

There is a Spring batch job with one delete and many inserts within a transaction with isolation level READ_COMMITTED: delete from TABLE_A; insert into TABLE_A values (...); insert into TABLE_A values ...
bboy's user avatar
  • 15
1 vote
1 answer
69 views

how can I see how many records have been affected by a transaction before it is committed? or how costly would be the rollback if I have to kill it?

I am using sqlwatch to monitor some of my servers and it has a job that deletes in batch from a table called dbo.sqlwatch_logger_snapshot_header I had lots of problems of locking and blocking as this ...
Marcello Miorelli's user avatar
0 votes
1 answer
63 views

Repeatable reads in case protection from duplicated inserts in POSTGRES

I would need to perform this kind of logic in a serializable way: account table has columns: 'accountName', 'location', 'worth', ... Assume no unique key exists. Some accounts are logically (according ...
GionJh's user avatar
  • 113
0 votes
1 answer
115 views

Understanding Lock Behavior in SQL Server Deadlock Scenario with INSERT

Hello SQL Server Experts, I'm currently experimenting with SQL Server deadlocks and have intentionally created one to better understand locking behavior. After setting up a test table, I ran two ...
Just a learner's user avatar
1 vote
2 answers
93 views

Transaction management work under Instance or database

I remember from university that: By Default, doing Insert, Delete and update on 2 or more data sources can't be participant under a transaction Please consider this this code: begin tran Insert2 ...
DooDoo's user avatar
  • 203
0 votes
1 answer
62 views

How to set up locks to prevent stale data being written

I'm mainly a C#/Javascript developer, with only a basic understanding of SQL and databases. I've been doing some reading on transactions and locks, but I'm still struggling to figure out how to set ...
Everix's user avatar
  • 1
0 votes
0 answers
34 views

Why postgresql creates force subtransactions in large statement except sql commands (savepoint...)?

I performed following large transaction without any sub transactions: begin for i in 1..10000000 loop INSERT INTO src.customers (id, first_name, last_name, email) values (i,'First Name', 'Last Name', '...
Эльфия Валиева's user avatar
3 votes
1 answer
362 views

SQL Server do not block table/row when I update row to the same value

create table test ( id int identity, id_int int default 1 ) insert test default values go 1000 begin transaction update test set id_int = id_int where id = 1000 waitfor delay '00:00:10' ...
volodya_neftyannik's user avatar
-1 votes
1 answer
144 views

Single-statement transactional consistency

I ran into this Q&A on StackOverflow the other day. To quote comments from the question author, the situation seems "somewhat horrifying". A similar situation is set up in this Q&A. ...
Steve's user avatar
  • 436
0 votes
1 answer
47 views

When can we say a transaction is committed in mysql?

I know the transaction can ensure that all the queries are committed or rolled back, but what is the point at which we can say ok, the transaction has been committed and all changes are durable? I ...
Kaiyu Yang's user avatar
0 votes
1 answer
72 views

How to atomically update different databases?

I have a lambda function which receives data of newly signed up users from a message queue and insert that same data in two different databases. Those two databases belong to two different ...
Prerak Jain's user avatar
1 vote
1 answer
51 views

Does a commit occur all at same time from a transactional point of view?

Assume a "Bank_Account" table exists and contains a single column "balance", where everyone has balance=100. If a transaction performs "update Bank_Account set balance = 200 ...
user1508072's user avatar
5 votes
1 answer
1k views

how to kill a rogue spid that is already killed?

I have an obstinate spid that I cannot kill and it is preventing the transaction log of my tempdb to get truncated this is how I found this rogue spid: if object_id('tempdb..#OpenTranStatus','U') is ...
Marcello Miorelli's user avatar
1 vote
1 answer
71 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 ...
Chris Marriott's user avatar
0 votes
1 answer
35 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 ...
loris's user avatar
  • 103
0 votes
1 answer
98 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. ...
Patrick's user avatar
  • 678
0 votes
1 answer
81 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 ...
Labs's user avatar
  • 3
0 votes
1 answer
51 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 ...
T M's user avatar
  • 11
0 votes
1 answer
78 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 ...
Kevin Meredith's user avatar
0 votes
0 answers
96 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....
mvorisek's user avatar
  • 418
0 votes
1 answer
223 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 ...
Nathan Long's user avatar
1 vote
1 answer
174 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 ...
Nishant's user avatar
  • 869
1 vote
1 answer
357 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 ...
DontBreakAlex's user avatar
0 votes
0 answers
10 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 "...
Fajela Tajkiya's user avatar
0 votes
0 answers
35 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....
Fajela Tajkiya's user avatar
0 votes
1 answer
37 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 ...
T M's user avatar
  • 11
0 votes
1 answer
158 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 ...
Justin Lowen's user avatar
0 votes
1 answer
68 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 ...
Leon's user avatar
  • 347
1 vote
1 answer
366 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, ...
KaliTheGreat's user avatar
0 votes
1 answer
64 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 ...
adam.g's user avatar
  • 355
0 votes
0 answers
144 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 ...
Kaio Marx's user avatar
0 votes
1 answer
47 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 ...
Jacqueline's user avatar
0 votes
1 answer
160 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 ...
Achraf Cheikh Mohamed's user avatar
0 votes
0 answers
72 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 ...
gnc's user avatar
  • 1
0 votes
2 answers
112 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. ...
Ross Bush's user avatar
  • 505
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....
Ross Bush's user avatar
  • 505
0 votes
1 answer
164 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 ...
Jacqueline's user avatar
0 votes
1 answer
84 views

Running 2 inserts in one transaction is executing unacceptably long time. Running same DML Separately works fine

Decided to see what DBAs have to say about this because developers have no idea. I need to execute 2 DMLs in one transaction. First DML takes records from Table1, changes some values and inserts back ...
T.S.'s user avatar
  • 216
0 votes
0 answers
34 views

InnoDB deadlocks with IX locks

Table schema `id` varchar(255) NOT NULL, `created_time` bigint DEFAULT NULL, `updated_time` bigint DEFAULT NULL, `tdi` varchar(255) NOT NULL, `gid` varchar(255) NOT NULL, `auth_id` varchar(...
uzumas's user avatar
  • 101
0 votes
0 answers
75 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 ...
Kushagr Jaiswal's user avatar
1 vote
1 answer
305 views

Lost connection to the database during COMMIT query: did the transaction succeed or not?

PROBLEM I recently encountered the error 2013 in MySql (Lost connection to MySQL server during query) while executing the COMMIT statement for a transaction (the previous update statements succeeded). ...
bruno-kakele's user avatar
0 votes
0 answers
211 views

multiple mongodb transactions over a single session

I'm using mongodb in an enterprise application and for some use cases, I have to use transactions. It seems like at first I wasn't using mongodb's ClientSessions correctly. Each time I was trying to ...
massivefermion's user avatar

1
2 3 4 5
16