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

learn more… | top users | synonyms

0
votes
0answers
42 views

Avoid transaction rollback when Deadlock is encountered (mysql)

I'm currently trying to handle concurrent inserts correctly using MySQL, I'm basically doing the following: CREATE TABLE foo (id INT NOT NULL PRIMARY KEY) ENGINE=INNODB; CREATE TABLE bar (id INT NOT ...
0
votes
1answer
81 views

Circular foreign key deferrable cascade behavior

I am trying to determine what happens for a particular circular foreign key that will cascade delete. Please feel free to address any other possibilities that my inexperience has not listed. On my ...
2
votes
1answer
22 views

MySQL behaviour on connection failure

My MySQL database is hosted on a different machine from my application system, which issues queries to the MySQL database remotely. If for some reason the network connection between the two machines ...
1
vote
1answer
50 views

How does the transactions interact if you set a different isolation level per connection?

I read that I can have different isolation levels per connection and in the server. The default isolation level is REPEATABLE-READ. So if I have a transaction that issues a SERIALIZED to the client ...
-1
votes
2answers
54 views

When do I need transactions /locks?

I know that transactions are meant to group several operations as one. But if for example in the same table one transaction does DELETE FROM table where state = 'done' and another does either SELECT ...
0
votes
0answers
43 views

When is transaction removed from sys.dm_tran_active_transactions?

We are having some issues with clients getting Transaction Timeout when connecting to our clustered SQL Server 2008 R2. To investigate the problems I have been digging into ...
0
votes
0answers
29 views

Why does the delete/update block each other in this case?

The following drives me crazy. I have: session1>begin; session2>begin; session2>update dummy set salary=1000 where id=11; session1>delete from dummy where id=1; id is the PK ...
0
votes
1answer
333 views

statistics State in MySQL Processlist

I have a busy MySQL Server having query SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION ...
0
votes
1answer
49 views

Rollback parent procedure on failure in MySQL

I have the following two procedures on my server: CREATE DEFINER=`someone`@`localhost` PROCEDURE `NewMagnet`( IN `nick` VARCHAR(32), IN `tth` CHAR(39), IN `name` TINYTEXT, IN ...
0
votes
1answer
33 views

How to set isolation to SERIALIZABLE DEFERRABLE for a whole Postgresql database? [closed]

I think read somewhere that it was possible to configure a Postgresql database to force all transactions to be SERIALIZABLE, but I can't find that document anymore. In fact, I would like to force the ...
0
votes
1answer
41 views

MySQL: Transaction and Deadlock

I have table "tbl_data", where have a lot of rows (3 - 5 million). I have JAVA (*.jar) module from which I INSERT/UPDATE data into this table. If I start *.jar file (for example, "run.jar") and after ...
1
vote
2answers
151 views

Are MySQL transactions under-utilised?

I have a vague understanding of transactions that it means you can help the server understand that a series of queries are related. Scenario : Suppose I have a series of queries which creates a user ...
0
votes
1answer
32 views

How to ensure a trigger is fired after variable number of inserts?

I have a 1:N relationship between two tables - let's call them "foo" (the parent table) and "bar". I have some triggers on both "foo" and "bar" tables. I insert data in a single transaction, first to ...
0
votes
1answer
20 views

Case of irrecoverable error in transaction

According to an exam question,which may lead to an irrecoverable error: 1.A transaction writes a data item after it is read by an uncommitted transaction. 2.A transaction a data item after it is ...
1
vote
2answers
53 views

Transaction basics. What is the result of 2 transactions running concurrently?

I have a rather basic question on transactions. Assume a table Account with a column amount. Now we have the following code: BEGIN $amount = SELECT amount FROM ACCOUNT WHERE id = 123; $amount = ...
12
votes
5answers
2k 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?
1
vote
1answer
58 views

What happens to a transaction if the network connection fails?

What happens to a transaction when the client connection is lost. Example the client is a web application code that starts a transaction to a remote server instance. The client sends the sql code to ...
1
vote
0answers
30 views

Is there a way to detect if there is an uncommitted transaction in H2?

I am using an H2 database with JDBC to create a connection with setAutoCommit(false). I am currently programatically checking whether or not there is an uncommitted transaction, but I was wondering ...
0
votes
0answers
25 views

About transactions on mongodb

To what extent the atomic storage of mongodb can leave your database unsafe of inserts or updates on my data? In the other words. What are the possibilities of my data not been (inserted or updated) ...
0
votes
1answer
83 views

What causes lost updates being observed in my SQL Azure database?

I have a SQL Azure database with the following tables: CREATE TABLE [dbo].[UserBalances] ( [UserId] UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID(), [AvailableMoney] INT NOT NULL ...
0
votes
2answers
50 views

DB2 - How to get value for: Transation Per Minute parameter for DB?

Hi is there some easy way to find out what is transaction per minute value for the database? Should I use db2 get snapshot? We are using DB2 9.7 ESE on Linux Thank you in advance
1
vote
1answer
119 views

DB Locks - How would one handle long transactions and maintain high concurrency?

We're maintaining a POS/WMS Application built in powerbuilder that runs on SQL Server 2008 R2 backend. Recently, we've been having a lot of locking issues and I believe it has to do with how we handle ...
1
vote
1answer
50 views

How, when and why does implicit changing of transaction isolation level happen?

RCSI (Read Committed Snapshot Isolation) is the setting of a database changing the behavior of (the default) Read Committed isolation level in SQL Server. This is, as I understand, the context of ...
1
vote
1answer
31 views

SSIS Transaction Level for Single Task

I have 3 tasks in a SSIS package. If the final task fails I only want that task to rollback. Do I just set that task to REQUIRED? Or leave it as SUPPORTED? Thanks!
4
votes
2answers
3k views

Current isolation level in oracle

How to get the current (default) transaction isolation level in oracle?
11
votes
5answers
169 views

What is the exact relationship between a database transaction and locking?

This is a humble question asked in the spirit of increasing my knowledge; kindly be gentle in your response. As a long-time application developer, I know at some level what a transaction is (I use ...
1
vote
2answers
89 views

Do I need a locking read?

I am inserting rows in a table, based on a value and on the existence of a row, which I get from SELECT statements. This function can be run concurrently, so I need to do some extra obviously. The ...
3
votes
2answers
142 views

T-SQL - Enforce Atomic Operations x Locks

I have a database model that works similar to a banking account (one table for operations, and a trigger to update the balance). I'm currently using SQL Server 2008 R2. Table OPERATIONS: VL_CREDIT ...
0
votes
0answers
164 views

Oracle Transaction recovery: lock conflict caught and ignored

In alert log there are a lot of "Transaction recovery: lock conflict caught and ignored" statements. I've found some information, that this error is caused when there is an active dead transaction. ...
6
votes
1answer
203 views

Specific TempDB insert of UserDB select results in SOS_SCHEDULER_YIELD to ENCRYPTION_SCAN

One of our production systems has exhibited a problem with a single insertion statement into a temp table from our user database. When I comment out the insert/select, the stored proc in question runs ...
-1
votes
1answer
99 views

default value with select case on xml

select case 'NULL' when 'NULL' then 'f' end as out output: f but select case (select (SELECT LEFT(l.list,LEN(l.list)-1) FROM (SELECT o.Reciting_Path + '|' + o.Declaimer +',' AS [text()] ...
1
vote
2answers
126 views

MySQL - INSERT … SELECT .. FROM .. - Single transaction or transaction per row?

Question: I am currently working on a data migration. I need to migrate 2.5 million rows from one InnoDB table to another InnoDB table (with a different setup). I am using the following syntax to do ...
15
votes
7answers
6k views

Oracle - Any way to view uncommited changes to a particular table?

I'm debugging through a batch process currently that does a lot of DML statements, but doesn't do a commit right away. It would be nice to be able to view the "pending" changes from another session ...
7
votes
1answer
126 views

In case of multiple updates of the same record in a single transaction, how many versions are being stored?

According to MSDN article by Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation "... All the earlier versions of a particular record are chained in a linked ...
6
votes
1answer
67 views

Does RCSI use less or same space than SNAPSHOT? Why?

Trying to better understand row-versioning based transaction isolations - RCSI (Read Committed Snapshot Isolation and SNAPSHOT) - in SQL Server... MSDN online article "Choosing Row Versioning-based ...
6
votes
2answers
91 views

“When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained”?

The MSDN online article "Snapshot Isolation in SQL Server" states: "An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement, ...
3
votes
1answer
83 views

Why do row-versioning isolations use a linked list of versions of record? [closed]

Trying to better understand row-versioning based transaction isolations in SQL Server... According to Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation": ...
0
votes
1answer
311 views

Putting a Select statement in a transaction

What is the difference between these 2 queries: start transaction; select * From orders Where id=1; UPDATE orders SET username="John" Where id=1; commit; And without transaction: select * From ...
2
votes
1answer
51 views

Why - read but not update ops fail in case of lacking space in tempdb database?

MSDN online article "Choosing Row Versioning-based Isolation Levels" states: "tempdb must have enough disk space for the version store. If there are very long-running transactions, all the ...
0
votes
1answer
72 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- ...
0
votes
1answer
84 views

Should I use begin tran to do an alter table column that increases the column length?

I want to execute this statement in a production database to alter the length of a varchar column from 50 to 72: ALTER TABLE Users ALTER COLUMN LoginID varchar(72) NOT NULL The column is not null ...
0
votes
0answers
80 views

Two-phase commit in MongoDB

I read the online document here and for hours of researching and trying things out I still have a question need explanation. The documentation says that there are two classes of failures, which for ...
0
votes
2answers
87 views

Oracle - Is it possible to maintain transaction across multiple schemas?

I got this database in oracle which includes 4 schemas. Each schema represents a logical layer. Each transaction (Stored Procedure) in the upper layer breaks into a couple of transactions in the lower ...
0
votes
0answers
71 views

What are the possible reasons for SQL Server errors on reading operations upon switching ALLOW_SNAPSHOT_ISOLATION` and `READ_COMMITTED_SNAPSHOT ON?

I have a client-server app running in Windows Server 2008R2 using/interacting SQL Server 2012. After reading app's documentation as well as feedbacks on internet from those who already accomplished ...
1
vote
1answer
92 views

What is the standard isolation level for a db used for money data?

When using a database server for monetary/financial data, I can only assume that using transactions is mandatory. But what I am not sure is which is usually the isolation level? Is a READ-REPEATABLE ...
0
votes
0answers
56 views

MySQL server vs client isolation levels

What is the difference between setting the transaction isolation level in the client configuration and the server configuration? If I set the client isolation level to be serializable while the ...
0
votes
0answers
24 views

Transactions' order of commitment within Serializable schedules

The following diagram was taken from a book(T1 and T2 are transactions which read and write to databases objects A and B). For convenience, I quoted few lines of text in that book which describe the ...
3
votes
1answer
317 views

Why can't I change the isolation levels of my connection in MySQL?

I am trying to experiment a bit with the isolation level of MySQL in a test environment. I do the following: mysql> set @@session.tx_isolation='READ-UNCOMMITED'; ERROR 1231 (42000): Variable ...
0
votes
1answer
59 views

SQL Server Transaction Log File [duplicate]

I am currently in an live environment and I am trying to clear some disk space. I noticed that a SQL Server database transaction log file is kinda big (12GB). The recovery model of that database is ...
0
votes
2answers
94 views

Does ROLLBACK TRAN honor LOCK_TIMEOUT value?

I have a following stored procedure on my system. BEGIN TRY BEGIN TRAN SET LOCK_TIMEOUT 10000 UPDATE tbl COMMIT TRAN END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN END CATCH ...