April 14, 2016 at 11:02 pm
Hi,
We have a table, which can altered from UI, when user adds new field or remove a field.
When user adds a field, we need to alter a table to add new column.
When user deletes a field, we need to alter a table to remove the column.
But the table may in use in other session , performing DML operations on it.
It may cause locking / dead lock issue.
How can avoid such locking / dead lock issue.
eg. session 1 is updating data as below:-
BEGIN TRANSACTION
UPDATE CustomFields SET Location = 'Mumbai' WHERE Id = 100
session 2 is adding column to the table
ALTER TABLE CustomFields ADD Address VARCHAR(1028)
Session 2 will get locked until session 1 either commits or rollback.
How can I avoid such locking or check whether table CustomFields is used in any other sessions?
PS. I am using SQL Server 2008 Enterprise edition.
Thanks in advance
April 15, 2016 at 8:10 am
Stop allowing users to modify the schema of a table via a GUI. This MUST be a CONTROLLED evolution for numerous reasons, including the one that is giving you fits.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 15, 2016 at 7:52 pm
Bhushan Kulkarni (4/14/2016)
How can avoid such locking / dead lock issue.
You can't. You can't modify the DDL of a table and expect other users to have access to it.
And , I generally agree with what Kevin posted. Having users be able to add and delete columns isn't real high on the best practices list.
There are work arounds although they're nearly as painful. With that thought in mind, what does this table contain, how many rows does it usually contain, and what is the reason why users are allowed to modify the structure, and what the heck to you want to do about accidental deletes of columns?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2016 at 1:56 pm
If you absolutely must have users dynamically adding 'fields' in a UI form, perhaps you would consider storing them in an attribute table rather than dynamically changing your table's structure?
Instead of altering your base table for any user defined fields, have a separate table with columns such as: user defined field ID, user defined field name. Adding a new 'field' would then only require adding a new row to this table.
You'd then have a third table that would link your user defined fields to their values: Value ID, user defined field ID, field value
Remember when users remove a user defined field to also remove any associated values in the value table. You could do this with foreign keys and cascading deletes.
April 18, 2016 at 2:13 pm
How can avoid such locking / dead lock issue.
...
...
eg. session 1 is updating data as below:-
BEGIN TRANSACTION
UPDATE CustomFields SET Location = 'Mumbai' WHERE Id = 100
session 2 is adding column to the table
ALTER TABLE CustomFields ADD Address VARCHAR(1028)
Session 2 will get locked until session 1 either commits or rollback.
...
...
He heeee. Yeah, we know. Ask Microsoft or Dr. Codd, and they'll tell you this "blocking" behaviour is "by design". :rolleyes:
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 19, 2016 at 7:13 am
An alternative would be be have an XML column in the base table where user defined fields could be placed. This would give you the option to use indexes if it proved necessary.
April 19, 2016 at 9:22 am
Ken McKelvey (4/19/2016)
An alternative would be be have an XML column in the base table where user defined fields could be placed. This would give you the option to use indexes if it proved necessary.
The bad part about this is... ummm.... XML 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
April 19, 2016 at 10:01 am
Jeff Moden (4/19/2016)
Ken McKelvey (4/19/2016)
An alternative would be be have an XML column in the base table where user defined fields could be placed. This would give you the option to use indexes if it proved necessary.The bad part about this is... ummm.... XML 😀
I agree. This might help in the short term as a workable solution. But in the longer term, I can imagine that XML column becoming a world of pain as more and more 'stuff' gets shovelled into it.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 19, 2016 at 10:11 am
Rather than containing your user defined attributes within a straight XML typed or VarChar column, instead consider defining a Column Set, which is essentially a relational column-ish abstraction layer for containing your XML attributes.
https://msdn.microsoft.com/en-us/library/cc280521(v=sql.110).aspx
The following example is excerpted from MSDN:
CREATE TABLE t (i int SPARSE, cs xml column_set FOR ALL_SPARSE_COLUMNS);
GO
INSERT t(cs) VALUES ('<i/>');
GO
SELECT i FROM t;
GO
i
---
0
However, if a requirement for this application is that users have flexibility to routinely add new logical columns to tables, then consider Cassandra. It now supports tabular resultsets and a SQL-ish language called CQL.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 19, 2016 at 6:13 pm
Simple EAV structure will be way more effective than dynamic schema or XML.
With appropriate indexing searching for a value in EAV structure will be much faster than in XML.
And more flexible than XML, as it would allow storing non-varchar values in its native form, without converting to a string format.
CREATE TABLE dbo.CustomField (
ID SMALLINT PRIMARY KEY,
FieldName NVARCHAR(100) COLLATE DATABASE_DEFAULT NOT NULL
)
CREATE INDEX IX_Fieldname ON dbo.CustomField (Fieldname)
-- Populating lookup-data
INSERT INTO dbo.CustomField ( ID, FieldName )
SELECT 1, N'Location'
UNION
SELECT 2, N'Address'
CREATE TABLE dbo.YourEntity_CustomField (
EntityID INT NOT NULL,
CustomFieldID SMALLINT NOT null,
FieldValue SQL_VARIANT NOT NULL,
PRIMARY KEY (EntityID, CustomFieldID, FieldValue),
--FOREIGN KEY (EntityID) REFERENCES dbo.YourEntity(ID)
)
CREATE INDEX IX_FieldValue ON dbo.YourEntity_CustomField (FieldValue)
-- Adding a "before update" record
INSERT INTO dbo.YourEntity_CustomField
( EntityID, CustomFieldID, FieldValue )
SELECT 100, -- ID from YourEntity table
CF.ID,
'New Dehli'
FROM dbo.CustomField cf
WHERE cf.FieldName = 'Location'
Now, session 1 is updating data:
BEGIN TRANSACTION
UPDATE ECF
SET FieldValue = 'Mumbai'
FROM dbo.YourEntity_CustomField ECF
INNER JOIN dbo.CustomField cf ON cf.ID = ECF.CustomFieldID
WHERE ecf.EntityID = 100
AND cf.FieldName = N'Location'
session 2 is adding a column to the TABLE
DECLARE @NewAddress NVARCHAR(500)
SET @NewAddress = N'Some New Address'
INSERT INTO dbo.YourEntity_CustomField
( EntityID, CustomFieldID, FieldValue )
SELECT 100, CF.ID, @NewAddress
FROM dbo.CustomField cf
WHERE cf.FieldName = 'Address'
AND NOT EXISTS (SELECT * FROM dbo.YourEntity_CustomField ECF
WHERE ECF.EntityID = 100
AND ECF.CustomFieldID = CF.ID
AND ECF.FieldValue = @NewAddress)
GO
No problem. No blocking.
Completed instantly.
You may wish to add another field in the 2nd session while the 1st session is waiting for the transaction to complete:
DECLARE @DateFrom DATETIME
SET @DateFrom = GETDATE()
INSERT INTO dbo.CustomField ( ID, FieldName )
SELECT 3, N'DateFrom'
INSERT INTO dbo.YourEntity_CustomField
( EntityID, CustomFieldID, FieldValue )
SELECT 100, CF.ID, @DateFrom
FROM dbo.CustomField cf
WHERE cf.FieldName = 'DateFrom'
AND NOT EXISTS (SELECT * FROM dbo.YourEntity_CustomField ECF
WHERE ECF.EntityID = 100
AND ECF.CustomFieldID = CF.ID
AND ECF.FieldValue = @DateFrom)
With a simple PIVOT you may choose which columns to select in each particular query:
SELECT EntityID, [Location]
FROM (
SELECT EntityID, cf.FieldName, ECF.FieldValue
FROM dbo.YourEntity_CustomField ECF
INNER JOIN dbo.CustomField cf ON ECF.CustomFieldID = CF.ID
WHERE ECF.EntityID = 100
) ST
PIVOT (
MAX(FieldValue)
FOR FieldName IN ([Location])
) AS PT
SELECT EntityID, [Location], [Address], [DateFrom]
FROM (
SELECT EntityID, cf.FieldName, ECF.FieldValue
FROM dbo.YourEntity_CustomField ECF
INNER JOIN dbo.CustomField cf ON ECF.CustomFieldID = CF.ID
WHERE ECF.EntityID = 100
) ST
PIVOT (
MAX(FieldValue)
FOR FieldName IN ([Location], [Address], [DateFrom])
) AS PT
But you need to
COMMIT TRANSACTIONin the 1st session to let the SELECTs go.
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply