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

learn more… | top users | synonyms

1
vote
2answers
17 views

Modeling invoices and orders

I have a problem in modeling a database that contains orders and invoices. Actually it's more of a problem from a business point of view, but it's related to modeling the database nonetheless. Model ...
2
votes
0answers
34 views

Trigger/Event that fires when a Transaction finishes

I have a query that pulls from about 15 different tables. I am looking to materialize this into a table that stores it in xml/json. (To improve performance.) The problem I have is that these tables ...
1
vote
2answers
40 views

Rollback Committed transaction in SQL Server

Is it possible to rollback a committed transaction? I have deleted all the data in a table. The delete statement is inside Begin Transaction and I have committed the transaction with a commit ...
2
votes
2answers
42 views

Locking issues under transaction on Postgres

I recently got a "Out of shared memory - You might need to increase max_locks_per_transaction" error. This is a long running process where I import a bunch of CSV files after proper processing. I ...
1
vote
0answers
38 views

Why are SELECT queries in sleeping status holding open transactions

Using sp_WhoIsActive, I see that I have a lot of processes in a "sleeping" status, several for hours. I am not sure of the cause and am still troubleshooting (timeout? bad code with uncommitted ...
2
votes
1answer
24 views

Persistant inserts in a UDF even if the function aborts

I have a rather complicated UDF (moves around a bunch of tables as well as creates a bunch of new ones) where multiple aborts may occur. Before every operation I would like to record the time when an ...
4
votes
2answers
83 views

Avoid unique violation in atomic transaction

Is possible to create atomic transaction in PostgreSQL? Consider I have table category with these rows: id|name --|--------- 1 |'tablets' 2 |'phones' And column name has unique constraint. If I ...
4
votes
2answers
380 views

What happens if a client connection is killed while holding a lock?

What happens if a client establishes a transaction, locks a row or table, and the client process crashes before it has a chance to rollback or commit the transaction? What, if anything, does SQL ...
2
votes
2answers
25 views

Keep local variable after rollback in mysql

I wish to keep local variable from stored procedure even if rollback occurres. In following procedure, i "register" numbers to users. The procedure takes user_id, value_from and value_to as ...
1
vote
1answer
27 views

parent child records - cascade deletes or transactions?

I'm about to create a parent and child table. In planning how to do CRUD operations on this data, I was wondering what is better design when it comes to deleting data. I'm using SQLite. I know ...
2
votes
1answer
66 views

Exclusively access a table and prevent other queries, even read queries

I'm stuck at setting transaction isolation level. Here's my scenario that happens in the application: Get unprocessed messages (using the IsProcessing flag) Set their IsProcessing to true (in RAM) ...
5
votes
0answers
77 views

How to terminate a suspended SQL Server transaction waiting for IO_COMPLETION?

We have a transaction that has been running for more than 5 hours. We are running out of disk space. The session has been killed but it is still waiting for IO_COMPLETION. Actually the wait_type just ...
0
votes
0answers
82 views

MySQL Transaction with rollback upon @@error

I have done several transactions with MySQL but mainly using php's libraries as the handle. Using php you would have something as: $pdo->beginTransaction(); $pdo->execute(' query1; ...
0
votes
1answer
71 views

Simultaneous READ COMMITTED transactions updates a select for update columns

I have a READ COMMITTED transaction in which I am trying to update a row and increment a column value till it reaches a bucket value contained in another column. Please see the code below CREATE ...
1
vote
1answer
86 views

MySQL: Why is inserting data with transactions faster then without them

I am using stored procedure to generate sequence consisting of prefix and number in given range (etc sequence "AB100", "AB101", ..., "AB199", "AB200") The stored procedure includes REPEAT loop, in ...
0
votes
1answer
60 views

How to execute two transactions concurrently in PostgreSQL?

I want to execute two transactions concurrently in pgAdmin: --The first transaction should modify the age of Paul BEGIN; DECLARE newage INTEGER; --the original age of Paul is 32 newage := Select age ...
1
vote
0answers
89 views

How to find the number of transactions per day or per hour in a SQL 2000 database

I am trying to find a query that I can run in order to find the number of transactions per day or per hour in a SQL 2000 database. The purpose is to get the number of hits on the database. I have ...
0
votes
1answer
18 views

Use a value for a Postgresql setting in a trigger

We're using a trigger to track changes in various database tables. This works fine. Now we want to be able to track the changes back to the web request that caused them. I'd like to be able to use ...
0
votes
0answers
15 views

Transaction block on coordinator

I tried to commad a procedure described here: http://files.postgres-xl.org/documentation/add-node-datanode.html We went as far as the third step, and everything was going perfect, but during the ...
0
votes
0answers
27 views

How to reset @@IDENTITY value inside a Sybase SP

Is there any way to reset the @@IDENTITY value inside a Sybase SP? I'm writing a SP which involves a transaction, so everytime I do an insert, to check data was correctly inserted, I assign ...
3
votes
1answer
39 views

xp_cmdshell in a transaction

I need to run multiple scripts in a certain folder in a transaction and rollback if there are any errors. At this point I'm just trying to get the rollback to work without a try catch. Currently when ...
3
votes
2answers
124 views

How to minimize log operations in SQL Server to avoid “log full” error

I have a database in production which constantly get log file filled, its a data warehouse and have many jobs/queries running for obvious. Below is the error which I am getting Msg 9002, Level 17, ...
2
votes
2answers
81 views

Does MySQL support querying for the current running transaction id

PostgreSQL supports retrieving the current running transaction id using a query like this: select txid_current(); Does MySQL have any such equivalent?
1
vote
1answer
40 views

Write-lock a whole table during transaction

I need to perform a delicate operation to my table in which I will solely insert, delete and select upon all of my rows and no God may interfere with the table during this operation: the table will be ...
2
votes
2answers
241 views

Half of a transaction completes, when the other half fails?

I have done some searching on SO, MSDN, and various other sources, and I found plenty of questions about transactions, but none that seem to be exactly what I'm dealing with. Admittedly I'm still ...
1
vote
2answers
46 views

Can I select data inserted in the same uncommited transaction?

Maybe this is a dumb beginner question, but I cannot find an answer anywhere. Everywhere I read about the Transaction Isolation which solves the visibility of data within the concurrent transactions. ...
1
vote
1answer
21 views

if set global transaction, how can I commit?

I found that MySQL can set a global transaction, but can I commit in another session? refer: http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html
0
votes
1answer
44 views

Limit the number of votes

I have a table of content. Users can vote on a content row. They can vote up to three times. I'm looking for the most efficient way to make sure they don't go over three votes. I can think of two ...
0
votes
0answers
52 views

Why are transactions long running?

My stored procedure has a transaction blog. I pick up a record from a database and process it in that transaction block. I noticed that my transaction log has grown in gigabytes. But my transaction ...
0
votes
0answers
42 views

What happens when a mysql connection gets lost?

I hope this is a question for the dba board. Otherwise blame me and i'll switch to unix. imagine the following situation: you have a Mysql server S and two clients A and B. Both clients connect to S ...
0
votes
1answer
74 views

Rolling update to all rows in innodb table (no rollback if aborted)

I have the following table, which stores the Social Security Death Master File database (~86M rows): CREATE TABLE `death_master_files` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ssn_an` smallint(5) ...
1
vote
0answers
48 views

SQLite insertion with junction table

I need to write a transaction for SQLite, that adds a blog post and links it to content tags, and it seems to be a bit too complicated for me. Here's an example schema: CREATE TABLE blogpost( ...
0
votes
1answer
76 views

Transaction over linked server causing time out

When I execute this query on [RemoteServer] linked server, it works just fine: INSERT INTO [RemoteServer].[DbName].[SchemaName].[TableName] VALUES (1, 'bar'); but when puting it inside a ...
0
votes
0answers
69 views

How can I perform reads and writes in the same transaction?

I'm trying to implement transactions in my application. I'm just trying to implement it like the example shown in the BeginTransaction() documentation. Public Shared Sub Process(wwid As String, ...
0
votes
0answers
14 views

Should I use mysql transaction in SaaS application

Can any one elaborate whether we should use transaction for SaaS based application having simple modules like note taking and task for a team? Also if I make a Quickbook like SaaS app then do I have ...
1
vote
0answers
111 views

PHPMyadmin transactions not commited [closed]

If I create a test table as the one below CREATE TABLE IF NOT EXISTS `TEST` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `NOTE` varchar(50) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB; and ...
1
vote
1answer
85 views

Read transaction log records

I am reading Remus Rusanu article on tlog.In that article he demoed on how to read log payload by using dbcc page option .My question is ,is there a way to read logrecord output of fn_dblog(null,null) ...
0
votes
2answers
57 views

Transaction has set autocommit = 0, but still the update happens before I commit

I have multiple MySQL InnoDb tables, I am trying to alter/insert/modify many of them in a SQL transaction, I set autocommit to false, so that if some SQL statements fail, I do commit the rest of ...
3
votes
1answer
110 views

How to perform server-side transaction timeout if COMMIT TRANSACTION will never arrive?

How can SQL Server terminate one of its transactions if it exceeds preset timeout? Imagine the following transaction performed by traveling salesman's SQL client application, which is submitted ...
0
votes
0answers
54 views

SSIS Transaction Set to Required giving error

I want to use transactions in SSIS insert update. My package is working fine but When i set transaction option to "Required" i am getting an error like [OLE DB Source [23]] Error: SSIS Error Code ...
0
votes
1answer
56 views

Behavior of Oracle During Very Large Transactions

I understand that if a transaction is large enough to overflow all of the redo logs on an Oracle database, and the database is in ARCHIVELOG mode, the transaction will wait until the archive log file ...
3
votes
1answer
171 views

Transaction with multiple SQLCMD calls in a batch file

Is there a good way to do something like the following? SQLCMD -S %SERVER% -E -Q "BEGIN TRANSACTION" FOR %%f in (script1 script2 script3) do ( SET CURRENT_SCRIPT=%%f SQLCMD -S Localhost -E -b ...
1
vote
0answers
38 views

What is the difference between 2 phase locking and strict 2 phase locking?

2 phase locking is used to ensure isolation of database transactions (by ensuring serializability). But there is another term called Strict 2 phase locking. I find it hard to understand the ...
1
vote
1answer
62 views

SSIS Transaction Problem

I am trying to use SQL transaction with my SSIS package so if any error comes changes are rollback. I am using an "Sequence Container" in Control Flow for this and setting its "Transaction Option" to ...
0
votes
0answers
23 views

MySQL Cluster TransactionInactiveTimeout and “operations”

Description: http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbd-transactioninactivetimeout I have couple of questions about this parameter. First of all, what ...
0
votes
0answers
203 views

MYSQL: InnoDB inserts wont rollback transaction inside a stored procedure

I have this stored procedure: START TRANSACTION; code_gen_loop: WHILE @Codes > 0 DO IF MOD( @Codes, 10000 ) = 0 THEN EXECUTE qry_status_stmt2; EXECUTE qry_status_stmt3; ...
-1
votes
1answer
60 views

shrink transaction log

Can I shrink transaction log when database in online using below command ? DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY) FYI my database is in production server & highly critical ...
6
votes
1answer
180 views

Transactional DDL workflow for MySQL

I was a little surprised to discover that DDL statements (alter table, create index etc) implicitly commit the current transaction in MySQL. Coming from MS SQL Server, the ability to do database ...
4
votes
0answers
70 views

When does SQL Server acquire locks?

The list of isolation levels in SQL Server found here states that write locks acquired within a transaction are retained until the end of the transaction. However it doesn't mention anything about ...
0
votes
1answer
20 views

Can a trasaction be rolled back if a table is edited outside a transaction?

I am learning how transactions work using this short script CREATE TABLE t_test (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; START TRANSACTION; INSERT INTO t_test VALUES (1); SELECT * FROM ...