Questions tagged [transaction]

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

Filter by
Sorted by
Tagged with
0
votes
2answers
57 views

Avoiding deadlocks in PostgreSQL with multiple clients

I am developing software which launches multiple, concurrent clients to connect to a PostgreSQL (12) database. When each client starts, the first thing it does upon connecting to PostgreSQL is to run ...
1
vote
2answers
58 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 ...
0
votes
0answers
19 views

Long Running Transaction in MySQL

I have very Long Running Transactions in MySQL (RDS Aurora) select * from information_schema.innodb_trx where trx_id = 45638005; +----------+-----------+---------------------+-----------------------+-...
0
votes
0answers
24 views

Does the order of query operations inside transactions matter?

Does the order of query operations inside transactions influence the performance or not? Does it depend on the low-level DBMS concurrency control protocol, e.g., 2PL, MVTO, OCC?
2
votes
2answers
51 views

What type of locks does BEGIN TRANSACTION cause against a specific table that's being read from?

In the following example, what kinds of locks are generated from the explicit transaction block that wraps the SELECT INTO query? BEGIN TRANSACTION T1 SELECT Field1, Field2, Field3 INTO TableB FROM ...
0
votes
0answers
11 views

Can I rely on the MSDTC to guarantee a distributed transaction was completed?

I've come across a bug using the TransactionScope from the .NET library, where we're getting a TransactionInDoubt exception. After doing a lot of research into something I don't know much about, I've ...
2
votes
1answer
50 views

How the single SQL statement is isolated?

All the books I've seen so far, talking about transactions show scenarios with several SQL statements involved. But what's about single statements? What level of isolation do they have? Is it ...
7
votes
3answers
303 views

What events still occur within a transaction that gets rolled back?

Is it true that all data modifications are undone when the transaction they are a part of gets rolled back? For example if a cursor that just executed a stored procedure 100 times that updated a ...
0
votes
1answer
38 views

Do table locks get released after a query finishes when WAITFOR is used after that query within the same batch and transaction?

If I have a query that reads from a table, if that query is proceeded with the WAITFOR keyword (within the same batch and transaction) will the table lock of the preceding query be held until the ...
0
votes
0answers
21 views

Long Running Transactions with Active Status

I could see long running transactions in my Innodb Status, I'm using RDS Aurora Mysql 5.6 Version. ---TRANSACTION 977075315, ACTIVE 70113 sec 2 lock struct(s), heap size 376, 0 row lock(s), undo log ...
0
votes
1answer
60 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) ...
1
vote
0answers
41 views

Sequence number for COMMITTED concurrent inserts

For example, I have a table create table Queue (OrderNum int, Data varchar(1000)) --int - may be a different type and many concurrent inserts into this table. Is there is a way to fill the OrderNum ...
0
votes
1answer
130 views

“Lock wait timeout exceeded; try restarting transaction” for my delete query

I'm using RDS Aurora with MySQL 5.6. I have a delete query on a table which runs once in a day and it fails with lock wait time outexception, try restarting transaction. This issue is not happening ...
1
vote
2answers
60 views

How does autocommit=off affects bulk inserts performance in mysql using innodb?

I know that turning off autocommit can improve bulk insert performance a lot according to: Is it better to use AUTOCOMMIT = 0 And I have made experiments to confirm that conclusion. But what I want ...
9
votes
1answer
302 views

Key-range RangeI-N lock compatibility in SQL Server

The documentation states that the RangeI-N lock (Insert range, null resource lock; used to test ranges before inserting a new key into an index) is compatible with itself (see the compatibility matrix)...
0
votes
1answer
87 views

Error: Cannot create new transaction because capacity was exceeded

We are getting error messages from our SQL-server when we run a stored procedure to increase a counter. This SP is executed many times for different entities but when executed for a shipment-number ...
0
votes
1answer
71 views

How to optimize stored procedure so it won't end with “Out of memory exception”

I have created a stored procedure that check against 2 and then delete from the 3. When I execute the stored procedure on Management Studio I get the exception as mentioned in this question: ...
0
votes
1answer
24 views

Is it safe to leave/exit the stored procedure without commit or rollback on a transaction?

Like the code below is it safe to do that? create definer=`root`@`%` procedure `test`() label: begin declare exit handler for sqlexception rollback; start transaction; select condition from table ...
0
votes
2answers
37 views

How can I force a minimum isolation level in a procedure?

How can I force a minimum isolation level in a procedure in mssql? I can use "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;" inside my procedure to set the transaction-level. But this command will ...
6
votes
1answer
140 views

Change transaction isolation level inside running transaction

I'm studying transaction isolation levels in SQL Server, and I'm trying to figure out how SQL Server behaves when the isolation level changes during the lifetime of the transaction. It seems that ...
5
votes
2answers
301 views

Lost Update Understanding

https://habr.com/en/company/postgrespro/blog/467437/ gives the following example of a Lost Update: For example, two transactions are going to increase the amount on the same account by ₽100 (₽ is ...
0
votes
1answer
45 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 ...
43
votes
9answers
5k 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
34 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
64 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
20 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
45 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
56 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
29 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
120 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
64 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
61 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
131 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
172 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
94 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
267 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
34 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
50 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
80 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
95 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
165 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
44 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
32 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
74 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
65 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
25 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
20 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
64 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="...