Questions tagged [transaction]
A mechanism for committing a consistent set of changes into a database atomically.
738
questions
4
votes
1
answer
633
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 ...
- 141
7
votes
3
answers
736
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 ...
- 35.1k
0
votes
1
answer
680
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 ...
- 35.1k
0
votes
0
answers
459
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 ...
- 11
1
vote
1
answer
664
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) ...
- 111
1
vote
0
answers
219
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 ...
- 119
1
vote
1
answer
5k
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 ...
- 11
2
votes
2
answers
3k
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 ...
- 21
9
votes
1
answer
1k
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)...
- 193
0
votes
1
answer
331
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: ...
- 127
1
vote
2
answers
1k
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 ...
- 453
0
votes
2
answers
144
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
1
answer
2k
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 ...
- 413
5
votes
2
answers
465
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 ...
- 485
0
votes
1
answer
258
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 ...
46
votes
9
answers
7k
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 ...
- 401
0
votes
2
answers
591
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 MYLIB for ...
- 279
1
vote
1
answer
6k
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 ...
- 203
0
votes
0
answers
31
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 ...
- 101
1
vote
1
answer
275
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 ...
- 11
1
vote
1
answer
5k
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 ...
- 11
4
votes
1
answer
138
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 ...
- 49
0
votes
1
answer
843
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 read ...
- 2,602
1
vote
2
answers
1k
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 (
...
- 515
0
votes
2
answers
1k
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 ...
- 103
0
votes
2
answers
2k
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 ...
- 31
3
votes
1
answer
4k
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 ...
- 675
1
vote
1
answer
121
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 ...
- 334
1
vote
1
answer
1k
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 ...
- 113
2
votes
2
answers
172
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 ...
- 2,120
0
votes
0
answers
889
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,
...
- 101
3
votes
3
answers
3k
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
...
- 223
3
votes
1
answer
4k
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 ...
- 272
-3
votes
1
answer
131
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.
...
- 223
0
votes
0
answers
161
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:
...
- 181
0
votes
1
answer
276
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 ...
- 151
5
votes
1
answer
391
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 ...
- 151
1
vote
1
answer
36
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 ...
- 133
19
votes
1
answer
2k
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 ...
- 457
1
vote
1
answer
422
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;
...
- 177
1
vote
1
answer
544
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="...
- 945
0
votes
0
answers
345
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 ...
- 111
-1
votes
2
answers
2k
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 ...
3
votes
2
answers
5k
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.
...
- 31
2
votes
1
answer
60
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 ...
- 21
3
votes
1
answer
170
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 ...
- 449
9
votes
3
answers
2k
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 ...
user180527
9
votes
3
answers
1k
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 );...
- 62.1k
5
votes
2
answers
3k
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 ...
- 644
1
vote
0
answers
191
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, ...
- 275