Tag Info

Hot answers tagged

6

If you look in the ERRORLOG file you'll probably see that the database is in the process of rolling commands forward or backward. Once that process is done the database will come back online. All you can do at this point is wait. DO NOT restart the SQL Server instance again. All you'll do is cause the SQL Server to start this process over again. However ...


6

The UPDATE query has an X lock on a key on "dbo.ACCOUNTS" blocking the SELECT from getting an S lock. The SELECT query has an S lock on a key of htt_customers_overlay_ultra. The UPDATE query has a U lock on the same key and is blocked trying to convert that to an X lock. The execution plan for the UPDATE doesn't feature Accounts at all so there is no ...


5

There are two stages to the lock of updated data. The first is an update lock (U) and the second, provided there is data that needs to be modified, is an exclusive lock (X). It's a two stage operation in the sense that there is data that needs to be searched in order to determine what/if data needs to be modified. The update lock will exist for that (or ...


4

Many experts share many ways on how to overcome this problem. These are my suggestions to play a safe game. Try to set the below command in a seperate session. SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; use db; SELECT COLA, COLB into outfile '/tmp/data.csv' from TABLE_NAME; COMMIT; exit; Doing by this way the SELECT statements are ...


3

Try adding SET_XACT_ABORT ON to the stored procedure to ensure the transaction is rolled back following termination due to an attention request. This is a best practice for procs with explicit transactions. A scenario that could cause the symptoms you describe is that the proc execution timed out during execution due to blocking on the table. When a ...


3

The simplest solution to this seems like it would be to use a named lock. http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_get-lock IF GET_LOCK('my_arbitrary_lock_name',0) THEN ... DO RELEASE_LOCK('my_arbitrary_lock_name'); ELSE ... END IF; This pool of locks is independent of table locks, metadata locks, row locks, or ...


3

Your index should be on (TrackerId ASC, [Date] DESC) INCLUDE (Position) so that it can easily locate the most recent one for each Tracker. But I really don't like the query from EF. Edit: ...because "most recent" should mean "latest datetime", not "latest identity value"


2

Three unlock table commands are COMMIT ROLLBACK DISCONNECT


2

A few things: Remove the set implicit_transactions off Actually, this is probably for the best as you don't want them on. This setting has nothing to do with a "top level transaction". When implicit_transactions are ON, INSERT and DELETE (and others) will auto-start a transaction. You do not need a BEGIN TRAN / COMMIT around a table variable. Not only does ...


2

@Jacob - I would say we've all had an unpleasant experience at some point in our lives, so don't beat yourself up. It's admirable that you're seeking guidance and learning from it. So hats off to you. Some key things to target might be the impacted rowcounts. If you're inserting into @idsToDelete, ideally you have a recordset around 1000 rows or ...


2

The write is done in-memory first and flushed to disk (asynchronously) later. Any readers accessing a document will get the in-memory copy straight away, not waiting for the flush to disk to happen (otherwise the database would be disk bound in terms of performance). The reference about locks applies to the in-memory portion, and guarantees the atomicity ...


2

A better index would be (assuming ORDER BY IdTrackerPosition DESC is correct, and the query should not specify ORDER BY [Date] DESC instead): CREATE UNIQUE INDEX i -- Choose a better name! ON dbo.TrackerPositions (TrackerId, IdTrackerPosition DESC) INCLUDE (Position, [Date], Speed, NbSatellites, Direction, HDOP); The execution plan should look ...


2

There is no UNLOCK TABLE statement in DB2. Locks are released automatically upon commit or rollback.


2

If the values for the 'id' are adjacent, like '1' and '2' in your example, including the case where they are not adjacent but there are no rows in the index with values in between them, then I would suggest that you are bumping into the index gap lock... which is part of "row" locking. InnoDB performs row-level locking in such a way that when it searches ...


1

As per info linked in comment - it needs some preparing, cannot probably be used automatically: that is output from show engine innodb status You can see it if you do in commandline: begin; update employees set store_id = 0 where store_id = 1; and then in any (even the same) session show engine innodb status\G Where you look for section labeled ...


1

The only immediate harm I can think of is dealing with contention from the UPDATE. Normal INSERT, UPDATE, and DELETE commands execute a full table lock. Doing ON DUPLICATE KEY UPDATE should work the same. In addition, what if the row's size changes to the point that the row needs to be bigger that the row's original allocation because of increasing the ...


1

Since OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE are DDL, full table locks are required. However, if all data is InnoDB, the latest version of MySQL is a little more lenient with DDL locks. Note the current MySQL Documentation on OPTIMIZE TABLE in InnoDB: Prior to Mysql 5.6.17, OPTIMIZE TABLE does not use online DDL (ALGORITHM=INPLACE). ...


1

The update statements works perfectly fine without the select before! Since single statements are safe by definition, even two UPDATE queries performed at the same time only will result in the row incremented twice. If you actually want to select the value for your PHP script, do something with it and later want to update this exact counter value, you can ...


1

Confirming what you already know, for future reference: "The time to acquire the initial locks is not counted as execution time." — http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html It stands to reason, at least to me, that a "slow query" is best defined as a query that is poorly-optimized and takes longer than it should even when ...


1

You can explicitly specify using : SET LOCK_TIMEOUT timeout_period timeout_period Is the number of milliseconds that will pass before Microsoft SQL Server returns a locking error. A value of -1 (default) indicates no time-out period (that is, wait forever). When a wait for a lock exceeds the time-out value, an error is returned. A value of 0 ...


1

MyISAM only locks for INSERT, UPDATE, and DELETE (a.k.a. DML) These issue full table locks each time (See MyISAM Documentation on Locking granularity). SELECTs get blocked by those statement. The exception is an INSERT with concurrent_insert=2 defined. Performing an explicit lock is unnecessary, although you are free to do so. You may need to check you ...


1

The commenter was correct -- your kill query 77 was killing your own thread's query... as was indicated by the fact that the row from the processlist indicated that thread 77 (you) was the thread that was currently running the SHOW FULL PROCESSLIST command. The way you fix your problem is by finding the step you took before running mysqldump, and undoing ...


1

Looking at the Stored Procedure, I see something rather unnatural. DELIMITER $$ DROP PROCEDURE IF EXISTS `adam_matan`.`AddPixel` $$ CREATE PROCEDURE `adam_matan`.`AddPixel` ( GivenType VARCHAR(20), GivenPixelData BLOB ) TheStoredProcedure:BEGIN DECLARE KeepPixels,DeleteLimit,MaxID INT; SET KeepPixels = 5; SET DeleteLimit = 100; INSERT ...



Only top voted, non community-wiki answers of a minimum length are eligible