Questions tagged [transaction]
A mechanism for committing a consistent set of changes into a database atomically.
562
questions
0
votes
1answer
28 views
What standard SQL isolation levels do the SQLite isolation levels correspond to?
There are four standard SQL isolation levels: Read Uncommitted, Read Committed, Repeated Read, and Serializable. (c.f. Database System Concepts 6ed.)
In SQLite, there are three isolation levels: ...
0
votes
1answer
39 views
Duplicate record inserted - why trigger is not preventing it?
I have two tables: DebitIUVs and RPTs. I have record inside DebitIUVs which is with state 118001. My business logic must insert new line inside RPTs table. Inside RPTs table there is additional ...
0
votes
2answers
59 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
64 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
24 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
51 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
63 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
42 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
153 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
71 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
303 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
103 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
26 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
143 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
35 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
78 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
48 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
64 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
121 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
72 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
62 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
135 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
179 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
100 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
278 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
35 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
51 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
86 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
96 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
171 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
45 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
34 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
76 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
66 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 ...
