MS SQL table Locking?

Anon

Hi,

I'm just wondering if there is a command for table locking in MS SQL. Like there is in MySQL ie "LOCK TABLES employee WRITE".

I can't seem to see an answer anywhere.

Thanks,
Euan

[deleted]

What would you want to lock a table for?

Anon

Same reason you lock a MySQL table I guess, to prevent deadlocking and prevent problems when two or more users write to the same table at once, ie grabbing the same ID value to create a new row.

[deleted]

MySQL uses locking to solve all the problems that they are too lazy to solve by adding the required functionality.

You cannot prevent deadlocks by locking, because locking is what causes deadlocks in the first place :-)

I suggest you read some tutorials about transactions and forget all about locking tables.

With transactions you can safely do all the things you want to do without having to lock anything.

PS how can you ever 'grab the same ID to create a new row', and how does locking a table prevent that?

Anon

ok, thanks but doesn't a deadlock occur when two processes are competing for the same resources and by locking tables the processes are prioritised and queued. I've only been using locking for the UPDATE and INSERT statements.

With 'grab the same ID to create a new row', if two insert queries are run at the same time on the same table they may grab the same MAX(ID) + 1 from the table. The reason why I didn't use auto_increment was because I didn't think MS SQL supported it, unlike MySQL. Mind you I did initially develop my current system under MySQL and have just ported to MS SQL, due to the boss :-) Thank god for phpDB and OO.

Also having a quick look at the MySQL website table locking seems give you more speed when updating. <a href='http://www.mysql.com/doc/L/O/LOCK_TABLES.html'>http://www.mysql.com/doc/L/O/LOCK_TABLES.html</a>. And also if your still reading this large message (grin) I just found out that MS SQL takes care of lock for you.

Thanks again for your help.

[deleted]

definition of a deadlock:
- you lock table A
- another person locks table B
- You want to edit table B, but it's locked so you have to wait fo the lock to be released.
- The other person wants to edit table A, but he must wait for your lock to be released.

now you are waiting for him, and he is waiting for you: deadlock.
Proper databases like MS-SQL will refuse to do this and whoever came last get's an error.

"they may grab the same MAX(ID) + 1 from the table"

As you know, this is evil, a definate "you may not do this, never ever, not in your lifetime, not on this planet, never ever" it is punishable by maumau.

Every database has an auto_increment type, but real database call them 'serial' or 'sequence'. Beware that each database has a different way of retreiving this value after it has been generated.

Locking a table can speed up updates because you simply explude the rest of the world, you make the table single-user. You bet that's fast 🙂 But everyone else has to wait. So if you have 200 reads and one update, then all 200 reads have to wait for the one update to finish. Not good.

PS, every database takes care of locking, but real database like MS-SQL have row-level-locking wich means that they only lock what you are updating and nothing more than that.
And that means that you can update 1 record and leave all other records available for other people. Good stuff (only MYSQL 4.x+ support row-level-locking)

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

Anon

Awesome, you've been a great help. Thanks heaps.

-Euan