Tag Info

New answers tagged

0

I spent a while in a test environment trying to reproduce your issue and have had some success. If I set up Log Shipping in its most basic form, and in such a way as to mimic your situation, everything appears to be working. However, I have found one way to configure the environment that results in messages similar to yours... Message 1: Message 2: ...


2

Your Question is much too broad to provide any specific table design suggestions. Just this, "Which one has Employee", could many any of dozens of things as mentioned in the Answer by Neil McGuigan: Person who first took customer inquiry phone call, the sales person who responded, the sales assistant who filled out Sales Order, the production manager who ...


2

Price shouldn't be in your order table. A sales order hasMany line items. Price belongs to line item. As does delivery date. An invoice is a request for payment. An order can have many (or no) invoices, and an invoice can be for many orders. It's a many to many relationship. You don't really need invoices if you're doing pay-before-delivery (or don't do ...


1

Good answer from Rolando. In addition -- Triggers should not be used for logic, because a couple of inter-relating triggers later, things will get confusing fast. A nice set of instructions in a stored procedure or client side procedure can get across the business logic more clearly than a bunch of hidden logic in the database. There are also limitations ...


3

It sounds like the code might be using SAVEPOINTs to handle errors, and not releasing the savepoints before proceeding. That would explain the large number of virtual xid locks. RELEASE SAVEPOINT after you're done with a step. You might also want to consider batching the work into smaller chunks, as the: SAVEPOINT Try it ROLLBACK TO SAVEPOINT if it ...


1

No. Committed transactions modify the contents of the database tables first in the transaction log, then in the data files. Unless triggers or other techniques are explicitly set up upfront, there is no way to recover the values stored in the database before the transaction was committed. In order to do that, you have to restore a copy of the database ...


1

sorry to hear that. Well if u havent trimmed transaction log you can do it. Nice articles how to achieve it: http://www.mssqltips.com/sqlservertip/3160/recover-deleted-sql-server-data-and-tables-with-the-help-of-transaction-log-and-lsns/ How do I rollback a dropped table using log?


1

It is difficult to explain your precise case without explain plans and engine profiling information. You may use pg_stat_statements module or PostgreSQL perf for tracing, but I am convinced your issue is the transaction size. It is known that the pattern of one transaction per row must be avoided for performance reason as commit overhead will slow down the ...


1

When an exception occurs everything is rolled back. You want to execute selected code "outside" the current transaction context, which is commonly referred to as autonomous transactions. This is not currently implemented (as of pg 9.4). There is an item in the Postgres TODO wiki, but it's a tricky matter, don't hold your breath. For now you can use the ...


10

In addition to what @Craig provided (and correcting some of it): Effective Postgres 9.4, UNIQUE, PRIMARY KEY and EXCLUDE constraints are checked immediately after each row when defined NOT DEFERRABLE. This is different from other kinds of NOT DEFERRABLE constraints (currently only REFERENCES (foreign key)) which are checked after each statement. We worked ...


9

As I understand it, your issue here is that the constraint is checked after each statement, but you want it checked at the end of the transaction, so it compares the before-state to the after-state, ignoring the intermediate states. If so, that is possible with a deferrable constraint. See SET CONSTRAINTS and DEFERRABLE constraints as documented in CREATE ...


1

You could test this, like so: Open SSMS, Connect to the Server/Database, Open New query begin tran , insert into sometable select somevalue Open new SSMS window, and run select * from sometable - it should be blocked. Open Task Manager and Kill first SSMS process Check the results of select in new SSMS window whether the table contains somevalue (it won't, ...


9

The session along with the spid attached will be killed/rolled back. http://stackoverflow.com/questions/3978227/how-to-kill-or-rollback-active-transaction https://technet.microsoft.com/en-us/library/ms173730%28v=sql.110%29.aspx EDIT: I would check your logs to verify, but in essence, the socket would be forcibly closed. It's not a database issue from ...


0

Plan A: Put it in a MEMORY or MyISAM table. Plan B: Have two connections; use the second to store the value you don't want rolled back.


3

It's not because of ROLLBACK. Value is reset because error is thrown later during INSERT. Handling the error inside procedure body (for instance, with DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ) will give you what you want. Alternatively, in your case you can just LEAVE inside IF @uid IS NOT NULL THEN block : CREATE PROCEDURE sp_insert_values(IN uid INT, ...


1

This depends on the needs of the data and is also a design preference. Having CASCADE ON DELETE can be a cleaner implementation and better describe the relationship of the data. The research that this article presents shows that CASCADE ON DELETE actually has a slight performance decrease over a straight delete in SQLite. But unless you have a consistently ...



Top 50 recent answers are included