The UPDATE queries in your previous two questions (Question1,Question2) are hitting the table 'people' by PRIMARY KEY with row level locking. This is what I stated back in Question1 on June 6, 2011 10:03 AM
All the transactions are traversing the PRIMARY key. Since the PRIMARY is a clustered index in InnoDB, the PRIMARY key and the row itself are together. Thus, traversing a row and and the PRIMARY KEY are one and the same. Therefore, any index lock on the PRIMARY KEY is a row level lock as well.
Something else has not been considered yet that can attribute slowness to indexes: The use of NON-UNIQUE indexes in InnoDB. Every indexed lookup in InnoDB using non-unique indexes also have the rowID of each row attached to the non-unique key. The rowID basically eminates from the Clustered Index. Updating non-unique indexes MUST ALWAYS interact with the clustered index EVEN IF THE TABLE DOES NOT HAVE A PRIMARY KEY.
Another thing to think about is the process of managing BTREE nodes in an index. Sometimes, it requires the page splitting of nodes. All entries in the BTREE node of non-unique indexes contain non-unique fields PLUS the rowID within the clustered index. To properly mitigate the splitting of such BTREE pages without disturbing data integrity, the row associated with the rowID must experience a row level lock internally.
If the 'people' table has a lot of non-unique indexes, prepare to have a large number of index pages in the tablespace as well as having tiny little rows locks sneak up on you from time to time.
There ia another factor which is not as obvious: Key Population
Sometimes when an index get populated, the key values making up the indexes could become lopsided over time and cause the MySQL Query Optimizer to switch from keyed lookups, to index scans, and finally to full table scans. That you cannot control unless you redesign the table with new indexes to compensate for the lopsidedness ot keys. Please provide the table structure for the 'people' table, the count of the 'people' table, and the show indexes output for the 'people' table.
Even if queries use only the PRIMARY KEY, lopsidedness of keys in non-unique indexes still needs BTREE balancing and page splitting to occur. Such BTREE management will produce a notable slowdown due to intermittent row level locks you did not intend to happen.
UPDATE 2011-06-14 22:19
Queries From Question 1
UPDATE people SET company_id = 1610, name = '<name>', password = '<hash>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@yahoo.com',
phone = NULL, mobile = '<phone>', iphone_device_id = 'android:<id>-<id>',
iphone_device_time = '2011-06-06 05:35:09', last_checkin = '2011-06-06 05:24:42',
location_lat = <lat>, location_long = -<lng>, gps_strength = 3296,
picture_blob_id = 1190,
authority = 1, active = 1, date_created = '2011-04-13 20:21:20',
last_login = '2011-06-06 05:35:09', panic_mode = 0,
battery_level = NULL, battery_state = NULL WHERE people_id = 3125
UPDATE people SET company_id = 1610, name = '<name>', password = '<hash>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@yahoo.com',
phone = NULL, mobile = '<phone>', iphone_device_id = 'android:<id>-<id>-<id>-<id>',
iphone_device_time = '2011-06-06 05:24:42', last_checkin = '2011-06-06 05:35:07',
location_lat = <lat>, location_long = -<lng>, gps_strength = 3296,
picture_blob_id = 1190,
authority = 1, active = 1, date_created = '2011-04-13 20:21:20',
last_login = '2011-06-06 05:35:09', panic_mode = 0,
battery_level = NULL, battery_state = NULL WHERE people_id = 3125
Picture the sequence in events
- Find the row by PRIMARY KEY
- Lock the row and clustered index
- Create MVCC Data for all columns being update
- Four columns are indexed (email,company_id,iphone_device_id,picture_blob_id)
- Each index requires BTREE management
- Within the same transaction space, steps 1-5 is trying to be repeated on the same row, updating the same columns (email the same in both queries, company_id the same in both queries, picture_blob_id the same in both queries, iphone_device_id different)
Queries From Question 2
UPDATE people SET iphone_device_id=NULL
WHERE iphone_device_id='iphone:<device_id_blah>' AND people_id<>666;
UPDATE people SET company_id = 444, name = 'Dad', password = '<pass>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@gmail.com',
phone = NULL, mobile = NULL, iphone_device_id = 'iphone:<device_id_blah>',
iphone_device_time = '2011-06-06 19:12:29', last_checkin = '2011-06-07 02:49:47',
location_lat = <lat>, location_long = <lng>, gps_strength = 66,
picture_blob_id = 1661,
authority = 1, active = 1, date_created = '2011-03-20 19:18:34',
last_login = '2011-06-07 11:15:01', panic_mode = 0, battery_level = 0.55,
battery_state = 'unplugged' WHERE people_id = 666;
These two queries are even more confusing because the first query is updating everything except people_id 666. Hundreds of rows are being painfully locked with just the first query. The second query is updating people_id 666 running the 5 sequence of events. The first query is running those same 5 sequence of events on every row involved except people_id 666 but the index for iphone_device_id is on an interecept course with two different queries. Somebody's gotta lock in the BTREE pages on a first-come-first-serve basis.
In the face of these two pairs of queries on a collision course to possibly lock the same BTREE pages within one index can be a gut-wrenching experience for InnoDB or any ACID-compliant RDBMS. Thus, an index slowdown is the destiny of these pairs of queries unless you can guarantee that the queries run with AUTOCOMMIT = 1 or by allowing dirty reads (although collisions like these make READ-COMMITTED and READ-UNCOMMITED a nightmare for MVCC).
UPDATE 2011-06-15 10:29
@RedBlueThing : In the queries from question 2, the first query is a range query, so a lot of row locks are being attained. Also notice both queries are trying to lock the same space id 0 page no 4611 n bits 152 is being locked in the PRIMARY KEY, aka clustered index.
In order to make sure you app is, at the very least, running based on the series of events you expect, there are two different options you could try:
Option 1) Convert this table to MyISAM (at least on a development server). Each UPDATE, INSERT, and DELETE will impose a full table lock on a first-come, first-serve basis.
Option 2) Try using the SERIALIZABLE isolation level. That will lock all intended rows in SHARED mode.
The sequence of events you expect will either break or be successful using these two alternative options. If both of these options fail, then you will need to look over your app and prioritize the order of execution of your queries. Once you establish that priority, you can simply undo these options (For option 1, go back to InnoDB, For option 2, go back to the default isolation level [stop using SERIALIZABLE]).
Yes you can do that using Event Notification for deadlocks / blocking / create database / drop database, and many more events as outlined here.
Below is the script that will help you with Deadlock detection and email in real time :
It will create an alert as well as a TSQL Job to fire with all the details emailed to DBA team. look for change Here to replace required stuff.
use dba_db ---- change HERE use find and replace as per your database name !!
go
/********************************************************************
Job Name : Monitoring - Deadlock Detector
Created by : Kin for dba.stackexchange.com
Version : V1.0
Funtionality: 1. Real time Deadlock detection using WMI
2. Send email out when deadlock occurs along with
Deadlock Graph attached.
3. Logs all the deadlocks in a central Table
dba_db.dbo.Deadlock
4. Does not require trace flags 1204 and 1222 enabled
*********************************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Deadlock]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
Print ' *** Table Deadlock Table Already Exists... moving Further *** '
ELSE
create table Deadlock
(
RecordId int identity (1,1) primary key not null,
AlertTime datetime not null,
DeadlockGraph xml,
Notified int not null constraint [DF_deadlock_flag] default (0)
)
go
create index deadlock_idx on Deadlock (AlertTime) with fillfactor = 100
go
USE [msdb]
GO
-- No need to enable deadlock trace flags
--dbcc traceon (1204,1222,-1)
--go
-- enable replace runtime tokens for sql agent to respond to WMI alets
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1
GO
-- create the job
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Monitoring - Deadlock Detector',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Job Name : Monitoring - Deadlock Detector
Created by : Kin for dba.stackexchange.com
Version : V1.0
Funtionality: 1. Real time Deadlock detection using WMI
2. Send email out when deadlock occurs along with
Deadlock Graph attached.
3. Logs all the deadlocks in a central Table
dba_db.dbo.Deadlock
4. Does not require trace flags 1204 and 1222 enabled',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert Deadlock info',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'INSERT INTO Deadlock (
AlertTime,
DeadlockGraph
)
VALUES (
GETDATE(),
''$(ESCAPE_NONE(WMI(TextData)))''
)',
@database_name=N'dba_db',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Send Email with Deadlock Graph] Script Date: 10/01/2010 12:01:45 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send Email with Deadlock Graph',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'if exists (select 1 from dba_db.dbo.Deadlock where notified = 0 )
begin
declare @tableHTML nvarchar(max)
set @tableHTML =N''<H3><FONT SIZE="3" FACE="Tahoma">Deadlock Has occured on ''+@@servername+'' .. Please Investigate Immediately </FONT></H3>''
set @tableHTML = @tableHTML+ N''<table border="1">'' +
N''<FONT SIZE="2" FACE="Calibri">'' +
N''<tr><th align="center">RecordId</th>'' +
N''<th align="center">AlertTime</th>'' +
N''<th align="center">DeadlockGraph</th>'' +
N''</tr>'' +
ISNULL(CAST ( (
select td = '''',
td = RecordId,'''',
td = AlertTime,'''',
td = DeadlockGraph,''''
from dba_db.dbo.Deadlock where notified = 0
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ),'''') +
N''</FONT>'' +
N''</table>'' ;
-- bcp out as .xdl file. This is the deadlock graph that will be emailed. Note that it will be overwritten everytime !!
exec master..xp_cmdshell ''BCP.exe "SELECT [Deadlockgraph].query(''''/TextData/deadlock-list'''') FROM dba_db.dbo.Deadlock where Notified = 0" queryout "d:\logs\deadlock.xdl" -c -q -T -Slocalhost''; ---- change localhost ..with the servername\instance or servername !!
-- send email out with the graph attached
declare @subject1 varchar(50)
set @subject1 = ''Deadlock Has Occured on ''+@@servername
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''DBMAIL PROFILE'', ---- change HERE db mail profile !!
@recipients=''DBAcompanyGroup@companyName.com'', ---- change HERE email group!!
@subject = @subject1,
@body = @tableHTML,
@body_format = ''HTML'',
@file_attachments = ''D:\logs\Deadlock.xdl''; ---- change HERE deadlock graph location!!
end
go
-- update the Deadlock table so that when the job runs it wont send out previous alert
update dba_db.dbo.Deadlock
set Notified = 1 where notified = 0
--SELECT * FROM dba_db.dbo.Deadlock',
@database_name=N'dba_db',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
-- create an WMI alert to respond to deadlocks
IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to Deadlock')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to Deadlock'
GO
DECLARE @server_namespace varchar(255)
IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
ELSE
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'
EXEC msdb.dbo.sp_add_alert @name=N'Respond to Deadlock',
@wmi_namespace=@server_namespace,
@wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
@job_name='Monitoring - Deadlock Detector' ;
GO
Best Answer
SQL Server can handle multiple concurrent requests simultaneously from multiple machines and threads. Unfortunately, replay can only spit them out one at a time, serially, from a single machine - introducing multiple layers of bottlenecks. There is probably also some overhead in replay to read and prepare the statements to send, that doesn't exist in your application.
If you want the replay to be more realistic, you should look into SQL Server 2012's Distributed Replay, which is able to distribute the replay across multiple machines. That said, you're not likely to ever match the original load time perfectly, and there is likely still some inherent overhead in the preparation of each statement during replay, even if you are able to eliminate some of the bottlenecks on concurrency.