Tag Info

New answers tagged

3

You can set the database to SINGLE_USER so that no other session can come in while you are performing your changes. Also, sp_addrolemember has been deprecated; you should be using ALTER ROLE instead. Finally, please get in the habit of using statement terminators. USE master; GO ALTER DATABASE SQL SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE SQL ...


1

In almost every use case, InnoDB is preferred over MyISAM. So, yes. To make sure the indexes, etc are converted correctly, see if anything in MySQL to InnoDB checklist needs to be addressed. Note that key_buffer_size should be decreased and innodb_buffer_pool_size increased. In MyISAM, an UPDATE blocks all other operations on the table. In InnoDB, it ...


4

The Rick James answer is good (I vote it up). You can try the following test. Create table and insert some values: create table table_x ( id int unsigned not NULL, status varchar(100) not NULL, index(id) ) engine=INNODB; insert into table_x values (123, 'PENDING'), (321, 'PENDING'); Then start two mysql sessions into two separated terminal. I ...


4

No problem. Use InnoDB. One of those queries will take out an "exclusive" lock on the row in question. The other query will try to take out such a lock, but be blocked until the first finishes (and COMMITs). Then it will see that there is no longer any row matching the WHERE clause, so it will say "0 rows matched, 0 rows updated".


3

I'll repost my response on twitter here: Based on the deadlock XML you posted, which lists a trancount of 3 in the session that invokes spUpdateUserAccount, a prior statement or batch in that transaction likely put the lock on IX_Person_TaxPreparer_rowCreated. I'm presuming this lock is held because of the foreign key constraint in place. The locks are ...


0

Using the below query you can find out locks on the table. column oracle_username format a15; column os_user_name format a15; column object_name format a37; column object_type format a37; select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from (select object_id, SESSION_ID, ...


1

The underlying OS is capable of detecting all the above mentioned possible disconnects and break the communication channel (TCP, net pipe etc). This will result in the rollback. Don't try to outsmart this. Of course, one could ask why not use a reliable queueing communication channel (eg. MSMQ) submit the entire order in one call rather than 100 calls ...



Top 50 recent answers are included