1 Replies - 4936 Views - Last Post: 24 November 2007 - 01:58 PM

#1 banging604   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 04-October 07

Question with MySQL Locking (session serializable)

Posted 22 November 2007 - 06:35 PM

Hey there,

I have a question regarding:

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;

I am not sure the proper way to syntax this mysql query. This is how I implemented the query.

I created a script that creates and populates the database and the tables in mySQL. In one of the scripts called Customer.sql, I implemented the serializable query.

INSERT INTO customer ( FirstName, LastName, HomePhone, WorkPhone, Email, AddressID )
VALUES ( "Kyle", "Sawatsky", "6043459687", "6043459687", "[email protected]", 1);

INSERT INTO customer ( FirstName, LastName, HomePhone, WorkPhone, Email, AddressID )
VALUES ( "Kyla", "Carter", "6045729467", "6040450375", "[email protected]", 12);

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;

I then opened up two mysql clients and tested to see if only can person can update the file. So I input the command on client 1:

update Customer set firstname='blahblah' where CustomerID = 1;

It works, but the thing is.....when the second person (client 2) tries to update the table, they just hang for a while and then they get the message:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Yes I do want client #2 to get locked out so they cannot update and change any values that client #1 is currently working on.

Is that the error message I am suppose to be getting? I don't want client #2 to basically timeout, because I want client #2 to have deadlock right away.

When I type in START TRANSACTION; in mysql client, I'll get the error msg when client #2 tries to update the table:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction


Can somebody tell me the difference between these two? I basically want ERROR 1213 to occur, but instead I'm getting the ERROR 1205.

If anybody has any input, please reply,
thanks.

Warren

Is This A Good Question/Topic? 0
  • +

Replies To: Question with MySQL Locking (session serializable)

#2 kwikone   User is offline

  • New D.I.C Head
  • member icon

Reputation: 1
  • View blog
  • Posts: 10
  • Joined: 25-October 07

Re: Question with MySQL Locking (session serializable)

Posted 24 November 2007 - 01:58 PM

I don't see an END TRANSACTION. Without it the transaction is still in progress hence the lock timeout to the second user. However, I believe you have to get error 1213 before you will get error 1205. This is mostly a timing issue. When using the mysql client check it's options.
Also you did not indicate whether the table engine is MyISAM, or is within an INNODB, because locking works differently for them. If MyISAM the lock is a table level lock and with INNODB it is record level.

This post has been edited by kwikone: 24 November 2007 - 02:09 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1