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

learn more… | top users | synonyms

1
vote
0answers
28 views

How to log within a stored procedure that is encapsulated within a transaction

I have a stored procedure, let us call it sp_A which calls other stored procedures, let us call them sp_B and sp_C. For the sake of consistency an XA TRANSATCION is started within sp_A. What I ...
1
vote
0answers
26 views

Is a commit or abort more costly for a prepared select?

I have found that selects will actually generate WAL records upon commit. Are there other costs to committing a select? Are there costs due to an abort? If so, which has more? What would be the ...
0
votes
1answer
29 views

Postgres : Executing SELECT within a transaction does not return most recent rows

Given a table layout that looks like this inv_items ========= * id bigserial sku character varying(22) name character varying(32) ... inv_items_stock =============== item_id bigint (inv_items) qty ...
2
votes
1answer
25 views

MySQL InnoDB transactions with lot of delete instructions

We have a process that runs frequently and does the following steps on a mysql database of type innodb. SET autocommit = 0; DELETE FROM table WHERE category='something'; -- every 300 rows or whatever ...
1
vote
1answer
32 views

SQL Server: affect other transactions?

I'd be surprised if this existed, but will ask just in case. Is there some mechanism which would allow one transaction to affect another (outside) transaction? For example: BEGIN TRY BEGIN TRAN ...
2
votes
0answers
41 views

Postgresql trigger - get all changed rows after transaction

I have invoice_item table and invoice table. I need to check total price of Order after each transaction. Since I still want to be able to change things, I need trigger after each transaction. CREATE ...
3
votes
2answers
130 views

Synonyms and transactions

I'm working with an application that uses SQL synonyms to switch between tables on local and remote databases without duplicating code. BEGIN TRANSACTION if (@isRemote = 1) BEGIN CREATE ...
1
vote
0answers
52 views

Does a serialized transaction affect rows written after begin?

I'm trying to determine exactly how a SERIALIZABLE transaction affects rows written after the transaction has begun. I'm using PostgreSQL 9.2. For example, in one of my serialized transactions, it ...
1
vote
0answers
24 views

Transactions using Isolation Level on databases with FileStream

We have a highly transactional application. We have a requirment to upload huge data into our database that can happen for a duration of 5-10 minutes. During our upload time, the tables are getting ...
0
votes
1answer
44 views

Is UDPLOCK using Row-locks?

I have a UPDATE statement that is using a UPDLOCK inside a DTCTransaction to avoid deadlocks. Now I was wondering. is it locking on a Row-level or on a Table-level?
0
votes
0answers
58 views

Transaction log backups during index maintenance

We have a database with large and complex files and fragmented indexes. The environment is SQL 2008 Full recovery model and Mirrored which limits me to reorganizing. The backup strategy is Full ...
2
votes
1answer
23 views

Reading from subscriber during Transactional Replication

When using Transaction Replication, are there any restrictions around selecting data from the subscriber whilst transactions are being applied to it? i.e. can selects be made against tables to which ...
7
votes
1answer
253 views

Synchronization using triggers

I have a requirement similar to previous discussions at: Writing a simple bank schema: How should I keep my balances in sync with their transaction history? Trigger in combination with transaction ...
0
votes
0answers
63 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 ...
2
votes
1answer
32 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 ...
0
votes
0answers
68 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 ...
-1
votes
2answers
74 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
38 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
140 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 ...
1
vote
1answer
63 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
99 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 ...
0
votes
1answer
49 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 ...
1
vote
1answer
65 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
vote
2answers
68 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 = ...
1
vote
1answer
97 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
42 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
34 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
38 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 ...
0
votes
2answers
103 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
155 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 ...
2
votes
1answer
43 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!
11
votes
5answers
186 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 ...
3
votes
2answers
175 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
1answer
99 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
0answers
365 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. ...
-1
votes
1answer
146 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
1answer
1k 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 ...
1
vote
2answers
188 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 ...
1
vote
1answer
60 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 ...
8
votes
1answer
202 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
2answers
141 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
90 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": ...
6
votes
1answer
72 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
1answer
258 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 ...
0
votes
1answer
104 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
106 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 ...
2
votes
1answer
53 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
0answers
137 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 ...
1
vote
2answers
137 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
82 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 ...